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

.getSessionFactory().openStatelessSession().connection();

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 (resultSet.next()) {

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 (rs.next()) {                                         // 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 (rs.next()) {                                         // 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

.getSessionFactory().openStatelessSession().connection();

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

stmt.setString(1, username);

stmt.setString(2, domainname);

ResultSet resultSet = stmt.executeQuery();

while (resultSet.next()) {

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.  

 

@Entity

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"

        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"&gt;

<hibernate-mapping>

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

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

        { call proc_return_subscriptions(:userId) }

    </sql-query>

</hibernate-mapping>

 

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 =

session.getNamedQuery(SubscriptionDetail.SELECT_MY_SUBSCRIPTIONS)

.setParameter("userId", userMainId)

.setResultTransformer(Transformers.aliasToBean(SubscriptionDetail.class))

.setReadOnly(true).list();

 

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.

 

Reference:

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/CallableStatement.html#setObject(java.lang.String,%20java.lang.Object)

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/tjvjdmlt.htm

http://www.hibernate.org/hib_docs/reference/en/html/querysql.html

http://www.geekinterview.com/question_details/22823

Advertisements
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 here:www.sstar-hk.com(Southern 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.
    website:www.sstar-hk.com[cgifffhbaccjcj]

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s