This example shows how to use StoredProcedureQuery to call database function.

Example

In this example we are going to use HSQLDB.

Creating database function

src/main/resources/function.sql

CREATE FUNCTION getSum(a INT, b INT)
RETURNS INT
RETURN a + b;

src/main/resources/META-INF/persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
             xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

    <persistence-unit name="example-unit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <exclude-unlisted-classes>false</exclude-unlisted-classes>
        <properties>
            <property name="javax.persistence.schema-generation.database.action" value="create"/>
            <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbc.JDBCDriver"/>
            <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:mem:dataSource"/>
            <property name="javax.persistence.sql-load-script-source" value="function.sql" />
            <property name="hibernate.hbm2ddl.import_files_sql_extractor"
                      value="org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor" />

        </properties>
    </persistence-unit>
</persistence>

Calling function

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          getSum(3,6);
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void getSum(int a, int b) {
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      StoredProcedureQuery procedureQuery = entityManager
              .createStoredProcedureQuery("getSum");
      procedureQuery.registerStoredProcedureParameter("a", Integer.class, ParameterMode.IN);
      procedureQuery.registerStoredProcedureParameter("b", Integer.class, ParameterMode.IN);
      procedureQuery.setParameter("a", a);
      procedureQuery.setParameter("b", b);
      procedureQuery.execute();
      Object singleResult = procedureQuery.getSingleResult();
      System.out.println("sum: " + singleResult);
  }
}

sum: 9

Example Project

Dependencies and Technologies Used:

• hsqldb 2.4.1: HSQLDB - Lightweight 100% Java SQL Database Engine.
• hibernate-core 5.3.6.Final: Hibernate's core ORM functionality.
  Implements javax.persistence:javax.persistence-api version 2.2
• JDK 1.8
• Maven 3.5.4