Call SQL procedure and function with JDBC and Hibernate


This article is to give some introductions of how to call the SQL procedure and function with JDBC and Hibernate feature.


Call SQL Procedure with JDBC


connection = openSessionInViewInterceptor


CallableStatement callableStatement = connection

.prepareCall("call proc_my_procedure(?)");

callableStatement.setObject("in_user _id", userID);

callableStatement.registerOutParameter("out_username", java.sql.Types.VARCHAR);

ResultSet resultSet = callableStatement.executeQuery();

The CallableStatement interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.

   {?= call <procedure-name>[<arg1>,<arg2>, …]}

   {call <procedure-name>[<arg1>,<arg2>, …]}


IN parameter values are set using the set methods inherited from PreparedStatement. The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the get methods provided here.


In the preceding example in_user _id and out_username are the input and output parameters in the procedure respectively. Please also be aware that you can not use the Hibernate to call a procedure if there is an output parameter other than a result set.


If the resultSet has multiple records, you have to go through the each record.

while ( {

String username = resultSet.getLong("out_ username ");



Otherwise, you can just use callableStatement to fetch the value for the required column.

String username = callableStatement.getLong("out_ username ");


If multiple resultSet, you have to use getMoreResults() to move cusor to the next result set. 

callableStatement.execute();                          // Call the stored procedure       1 

rs = callableStatement.getResultSet();                       // Get the first result set        2 

while ( {                                         // Position the cursor             3 

 i = rs.getInt(1);                                             // Retrieve current result set value

 System.out.println("first result set = " + i);      // Print the value


cstmt.getMoreResults();                                 // Point to the second result set  4a 

                                                                   // and close the first result set

rs = cstmt.getResultSet();                               // Get the second result set       4b 

while ( {                                         // Position the cursor             4c 

 s = rs.getString(1);                                        // Retrieve current result set value

 System.out.println("second result set = " + s); // Print the value


rs.close();                                                     // Close the result set

cstmt.close();                                               // Close the statement


Call SQL Function with JDBC


Long userId=null;

conn =openSessionInViewInterceptor


stmt  = conn.prepareStatement("select func_check_active_subs_trail(?, ?)");

stmt.setString(1, username);

stmt.setString(2, domainname);

ResultSet resultSet = stmt.executeQuery();

while ( {

userId = resultSet.getLong(1);


Calling SQL function is a bit different with calling procedure. Query to call the function should be able to be executed in any SQL vendors. In the preceding example, if you replace the placeholder (“?”) with real value, the query should be able to run in the SQL vender.

Call SQL Procedure/Function with Hibernate

Hibernate 3 introduces support for queries via stored procedures and functions. The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate.


Like tables, you have to provide the hibernate mapping for the procedure.  



public class SubscriptionDetail implements Serializable {   

public static final String SELECT_MY_SUBSCRIPTIONS = "selectMySubscriptions";



public SubscriptionDetail() {



@Column(name = "package_type_id")

public Long getPackageTypeId() {

    return packageTypeId;



public void setPackageTypeId(Long packageTypeId) {

    this.packageTypeId = packageTypeId;




<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC

        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"



    <sql-query name="selectMySubscriptions" callable="true">

        <return-scalar column="package_type_id" type="long"/>

        { call proc_return_subscriptions(:userId) }




As later we use named SQL query, which may return a scalar value. You must declare the column alias and Hibernate type using the <return-scalar> element. To find more about the scalar, please check the reference attached.


To call the procedure, you can use the getNamedQuery, but make sure the query names are identical (as highlighted in red).  

List<SubscriptionDetail> subscriptionDetails =


.setParameter("userId", userMainId)




It is possible to apply a ResultTransformer to native sql queries. Allowing it to e.g. return non-managed entities. The above query will return a list of SubscriptionDetail which has been instantiated and injected the values into its corresponding properties or fields (if there are values for package_type_id to inject). Please also be aware that in your entity class you must provide the constructor.  Otherwise, an initiation exception will be caught, when aliasToBean is called.



This entry was posted in Database. Bookmark the permalink.

10 Responses to Call SQL procedure and function with JDBC and Hibernate

  1. Unknown says:

    Hi,Do you have used LCDs, second hand LCDs, used flat screens and used LCD monitors? Please go Stars).We are constantly buying re-usable LCD panels and working for LCD recycling.The re-usable panels go through strictly designed process of categorizing, checking, testing, repairing and refurbishing before they are re-used to make remanufactured LCD displays and TV sets.Due to our recent breakthrough in testing and repairing technology of LCD, we can improve the value for your LCD panels.[cgifffhbaccjcj]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s