This example shows how to use StoredProcedureQuery to call database function.
StoredProcedureQuery
In this example we are going to use HSQLDB.
CREATE FUNCTION getSum(a INT, b INT) RETURNS INT RETURN a + b;
<?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>
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
Dependencies and Technologies Used: