How to call Stored Procedures in Hibernate?

DB Objects:

      1.     Tables

      2.     Views

      3.     Sequences

      4.     Stored Procedures, etc.

All Object/Relational mapping frameworks will have entity relationship between DB Tables/Views with Java POJO Data Transfer Objects. To communicate with these DB objects is very easy and can be managed with the method given by framework like save(), update(), delete(), saveOrUpadate(), etc.

In Hibernate, if user wants to communicate with Stored Procedures from underlying DB, needs to follow the below approach.

Procedures have two kinds of parameters in any Database.

      1.     IN Parameters

      2.     OUT Parameters

Step #1: Open a connection from StatelessSession.

Step #2: Call stored procedure by using prepareCall() method of Statement object.

Step #3: Pass/Provide all IN parameters of stored procedures by using setXXX() of Statement object.

Step #4: Register the type of OUT parameters by using registerOutParameter() of Statement object.

Step #5: Call executeUpdate() method on Statement Object.

Step #6: Get the OUT parameters provided by stored procedures by using getXXX() method of Statement object.

Code Snippet:

String queryString = "{call schema.storedProcExample(?,?,?,?)}"; int sequenceNo = 0; Connection con = getSessionFactory().openStatelessSession().connection(); CallableStatement stmt = null; try {             stmt = con.prepareCall(queryString);             stmt.registerOutParameter(1, java.sql.Types.INTEGER);             stmt.setInt(2, 1);             stmt.setString(3, “2”);             stmt.setDate(4, new java.sql.Date(System.currentTimeMillis()));             stmt.executeUpdate();             sequenceNo = stmt.getInt(1); } catch (SQLException e) {             LOGGER.info("getTransactionSequenceNo : {}", "Exception");             LOGGER.info("getTransactionSequenceNo Exception: {}", e); } finally {             try {                         if (stmt != null)                                     stmt.close();             } catch (Exception e) {                         LOGGER.error("exception while closing statement", e);             }             try {                         if (con != null)                                     con.close();             } catch (Exception e) {                         LOGGER.error("exception while closing connection", e);             } }

Note: The types of OUT parameters can be available from java.sql.Types class file.

Characteristics of StatelessSession in Hibernate:

  Ø  Neither does not implement a first-level cache nor interact with any second-level cache.

  Ø  Collections are ignored.

  Ø  Operations performed via a stateless session bypass Hibernate's event model and interceptors.