Hibernate provides support for queries via stored procedures and functions. Stored procedure arguments are declared using the IN parameter type, and the result can be either marked with an OUT parameter type, a REF_CURSOR or it could just return the result like a function.
Table of contents
- MySQL stored procedure
- MySQL function
- Using named queries to call stored procedures
1. MySQL stored procedure
For example, consider we have MySQL stored procedure with OUT parameter type:
statement.executeUpdate(
"CREATE PROCEDURE sp_count_phones (" +
" IN personId INT, " +
" OUT phoneCount INT " +
") " +
"BEGIN " +
" SELECT COUNT(*) INTO phoneCount " +
" FROM Phone p " +
" WHERE p.person_id = personId; " +
"END"
);
Calling a MySQL stored procedure with OUT parameter type using Hibernate
Session session = entityManager.unwrap( Session.class );
ProcedureCall call = session.createStoredProcedureCall( "sp_count_phones" );
call.registerParameter( "personId", Long.class, ParameterMode.IN ).bindValue( 1L );
call.registerParameter( "phoneCount", Long.class, ParameterMode.OUT );
Long phoneCount = (Long) call.getOutputs().getOutputParameterValue( "phoneCount" );
assertEquals( Long.valueOf( 2 ), phoneCount );
If the stored procedure outputs the result directly without an OUT parameter type. For example, consider below MySQL stored procedure without an OUT parameter type:
statement.executeUpdate(
"CREATE PROCEDURE sp_phones(IN personId INT) " +
"BEGIN " +
" SELECT * " +
" FROM Phone " +
" WHERE person_id = personId; " +
"END"
);
You can retrieve the results of the aforementioned MySQL stored procedure as follows:
Calling a MySQL stored procedure and fetching the result set without an OUT parameter type using Hibernate
Session session = entityManager.unwrap( Session.class );
ProcedureCall call = session.createStoredProcedureCall( "sp_phones" );
call.registerParameter( 1, Long.class, ParameterMode.IN ).bindValue( 1L );
Output output = call.getOutputs().getCurrent();
List<Object[]> personComments = ( (ResultSetOutput) output ).getResultList();
For a REF_CURSOR result sets, we’ll consider the following Oracle stored procedure:
Oracle REF_CURSOR stored procedure
statement.executeUpdate(
"CREATE OR REPLACE PROCEDURE sp_person_phones ( " +
" personId IN NUMBER, " +
" personPhones OUT SYS_REFCURSOR ) " +
"AS " +
"BEGIN " +
" OPEN personPhones FOR " +
" SELECT *" +
" FROM phone " +
" WHERE person_id = personId; " +
"END;"
);
Calling an Oracle REF_CURSOR stored procedure using Hibernate
Session session = entityManager.unwrap(Session.class);
ProcedureCall call = session.createStoredProcedureCall( "sp_person_phones");
call.registerParameter(1, Long.class, ParameterMode.IN).bindValue(1L);
call.registerParameter(2, Class.class, ParameterMode.REF_CURSOR);
Output output = call.getOutputs().getCurrent();
List<Object[]> postComments = ( (ResultSetOutput) output ).getResultList();
assertEquals(2, postComments.size());
2. MySQL function
Let's discuss how to work with database SQL functions.
For example, consider we have below MySQL function:
statement.executeUpdate(
"CREATE FUNCTION fn_count_phones(personId integer) " +
"RETURNS integer " +
"DETERMINISTIC " +
"READS SQL DATA " +
"BEGIN " +
" DECLARE phoneCount integer; " +
" SELECT COUNT(*) INTO phoneCount " +
" FROM Phone p " +
" WHERE p.person_id = personId; " +
" RETURN phoneCount; " +
"END"
);
Because the current StoredProcedureQuery implementation doesn’t yet support SQL functions, we need to use the JDBC syntax.
Calling a MySQL function
final AtomicReference<Integer> phoneCount = new AtomicReference<>();
Session session = entityManager.unwrap( Session.class );
session.doWork( connection -> {
try (CallableStatement function = connection.prepareCall(
"{ ? = call fn_count_phones(?) }" )) {
function.registerOutParameter( 1, Types.INTEGER );
function.setInt( 2, 1 );
function.execute();
phoneCount.set( function.getInt( 1 ) );
}
} );
3. Using named queries to call stored procedures
Just like with SQL statements, you can also use named queries to call stored procedures. For this purpose, JPA defines the @NamedStoredProcedureQuery annotation.
The following code snippet shows an example of a @NamedStoredProcedureQuery annotation. This query calls the stored procedure to calculate with the input parameters x and y and the output parameter sum.
As you can see, each parameter is defined by a @StoredProcedureParameter annotation which defines the parameter mode and its name. The parameter mode specifies if the parameter is used as an input (ParameterMode.IN), output (ParameterMode.OUT), input and output (ParameterMode.INOUT) or as a result set cursor (ParameterMode.REF_CURSOR).
@NamedStoredProcedureQuery(
name = "calculate",
procedureName = "calculate",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "x"),
@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "y"),
@StoredProcedureParameter(mode = ParameterMode.OUT, type = Double.class, name = "sum")
}
)
That’s all you need to do to define the stored procedure call. You can now use it in your business code. You just have to provide its name to the createNamedStoredProcedureQuery method of the EntityManager to instantiate the query, set the input parameters, execute it and read the output parameter.
StoredProcedureQuery query = this.em.createNamedStoredProcedureQuery("calculate");
query.setParameter("x", 1.23d);
query.setParameter("y", 4.56d);
query.execute();
Double sum = (Double) query.getOutputParameterValue("sum");
Learn Hibernate ORM Framework at Hibernate ORM Tutorial
GitHub Repository
The complete source code of this article is available on my GitHub Repository - https://github.com/RameshMF/Hibernate-ORM-Tutorials
Conclusion
In this article, we have discussed how Hibernate provides support for queries via stored procedures and functions.
You can learn more about Hibernate ORM Framework at Hibernate Tutorial
what if the stored procedure returns a table
ReplyDelete