How to Call Custom Database Functions with Hibernate

How can you call custom database functions with Hibernate?

If you want to use your custom function in HQL, you'll need to define it in appropriate Dialect

Take a look at PostgreSQLDialect (or any other, really) source, and you'll see a bunch of registerFunction() calls. You'll need to add one more :-) - for your own custom function.

You'll then have to specify your own dialect in Hibernate configuration.

Create and use database function with JPA

You're trying to execute two queries at once. You need to split this into two queries:

int updated = getEntityManager().createNativeQuery("CREATE OR REPLACE FUNCTION pg_temp.get(IN nb BIGINT) RETURNS BIGINT AS 'SELECT $1 + $1' LANGUAGE sql IMMUTABLE").executeUpdate();
Object result = getEntityManager().createNativeQuery("SELECT pg_temp.get(2)").getSingleResult();

Hibernate Criteria with custom database function

I found out that Hibernate.STRING is deprecated: Hibernate 4.1Final alternative for Hibernate.STRING

The following statement works, but it would be nicer to get a solution with a custom Restriction class:

add(Restrictions.sqlRestriction("name % ?", filter, StringType.INSTANCE));

How to use a custom function in a jpa query?

Unfortunately if you want to use the JPA 2.1 feature of the custom function call in your Select statement then you will need to perform some additional actions before you can use it.

When you use it in your where statement then it works without any additional actions, but as i wanted to use it for one of my projects inside the select just as you did then you would need to:

1) Extend the hibernate dialect and register your function(s):

package com.mypkg.dialect;

import org.hibernate.dialect.Oracle10gDialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StringType;

public class CustomOracle10gDialect extends Oracle10gDialect {

    public CustomOracle10gDialect() {
        super();
        registerFunction("my_function"
           , new StandardSQLFunction("my_function", new StringType()));
    }
}

2) Edit your hibernate.dialect property of your session factory to point to that custom implementation:

<property name="hibernate.dialect" value="com.mypkg.dialect.CustomOracle10gDialect"/>

Update

If the function needs to be called from a certain schema then this would be suggested:

registerFunction("my_function"
           , new StandardSQLFunction("schema.my_function", new StringType()));

Further reading -> native function calls

Is there any method that we can use to run our custom SQL queries passed as string in spring hibernate?

Hibernate is just an ORM implementation. ORM = Object-relational-mapping .

Therefore Hibernate only does what it was created to do. Create the link between java objects and database objects

Also Hibernate is just an implementation that is used by default from Spring. You could as well chose another implementation. You normally call spring methods which then get executed by Hibernate.

If you use Spring Jpa you can find many methods to achieve that. One of them are Native Queries in repositories.

Example

@Repository
public interface CarRepository extends CrudRepository<Car, Long>, PagingAndSortingRepository<Car, Long> {
 
    @Query(value="select * from car a where a.model= :model", nativeQuery=true)
    List<Car> getCarsByModel(String model);
}

How to call an Oracle function from Hibernate with a return parameter?

Hibernate Session provides a doWork() method that gives you direct access to java.sql.Connection. You can then create and use java.sql.CallableStatement to execute your function:

session.doWork(new Work() {
  public void execute(Connection connection) throws SQLException {
    CallableStatement call = connection.prepareCall("{ ? = call MYSCHEMA.MYFUNC(?,?) }");
    call.registerOutParameter( 1, Types.INTEGER ); // or whatever it is
    call.setLong(2, id);
    call.setLong(3, transId);
    call.execute();
    int result = call.getInt(1); // propagate this back to enclosing class
  }
});