hibernate-to-execute-stored-procedures

Using Hibernate to Execute Stored Procedures in MS SQL server

By Sumit Jayaswal

Date 16 April 2009 Confidential

TABLE OF CONTENTS

INTRODUCTION .......................................................................................................... 3

PROBLEM/CASE STATEMENT ................................................................................ 4

PREVIOUS OPTIONS ................................................................................................... 5

PRAXIS SOLUTION ..................................................................................................... 6

IMPLEMENTATION .................................................................................................... 7

CONCLUSION ............................................................................................................. 10


Introduction

Hibernate is an open source object relational mapping tool available for Java and .Net. On LGPL license, hibernate can be used for open source project development and also for commercial projects. Hibernate relieves the developers on data persistence front by allowing java objects to persist in relational database. This is achieved by mapping the database structures to java objects at runtime. With this boon from hibernate; the developers can focus just on developing the business logic than on writing a performant persistence layer (which includes, DAOs, SQL queries, JDBC code, connection management, etc).For more information on hibernate and to download, you can visit www.hibernate.org .


http://www.hibernate.org/

Problem/Case Statement

Executing queries using Hibernate is quite an easy going. Not much of a problem if you know how to create POJO and hibernate mapping file right from an IDE. The most problematic area lies in executing the stored procedures of MS Sql server. Hibernate reference document does not explain in detail about the java side implementation of executing a stored procedure. It becomes more difficult with the fact that you cannot create a POJO or hibernate mapping file (*.hbm.xml) from the IDE. POJOs and hibernate mapping files are created for the known tables and views in the database. This document makes an attempt to explain and resolve this problem for the ease of development. This document will also cover automatic code generation from IDE. For all demonstration purposes, this document will use Netbeans 6.5 as java IDE.


Previous Options

One another way of executing a stored procedure is through making a connection object without using hibernate to handle the connection. You can make a callable statement to use the connection and call the stored procedure.A typical Java code to accomplish the above will look something like below:

String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";String dbURL = "jdbc:odbc:Con2";String usernameDbConn = "sa";String passwordDbConn = "admin1234";Class.forName(jdbcDriver).newInstance();dbConn = DriverManager.getConnection(dbURL, usernameDbConn, passwordDbConn);CallableStatement cstmt = dbConn.prepareCall("{call getEmpDetails(?, ?)}");cstmt.setString(1, “1234”); //this is the setting the employee numbercstmt.setString(2, “HR”); //this is the setting the department numbercstmt.execute();


Praxis Solution

The fact that the above solution does not use hibernate to maintain and establish the connections will jeopardize the very idea of using hibernate in the project on the first place. The solution is quite simple and easy to use in hibernate. Its just that since it is not very well documented anywhere, the developers’ life becomes a nightmare when he is trying to implement a stored procedure call in his hibernate enabled code.

For this, we need to carry out the following steps.1. Create POJO and Hibernate mapping file for that stored

procedure manually with some help from the IDE. Netbeans has capabilities of intuitive adding some of the code and save the tedious coding part from the developer. To create the above files, one should have knowledge of what the stored procedure is doing and what are the columns it is going to return.

2. Create a simple java class to get the connection from hibernate session and use that to call the stored procedure which is already mapped in hibernate mapping file.


Implementation

For demonstration purposes, I will try to place in only the important pieces of the code in this document, which will give you an insight of what to look out for at the time of this particular implementation.

The stored procedure getEmpDetails (empID , deptNo). Execution command in MS SQL server 2005 – exec getEmpDetails 1234, HRThis returns the columns Emp_Name, Emp_Address, Emp_DOB, Emp_Sal

To Create a POJO:• Create a New Java class in NetBeans 6.5.• Make the class implement serializable.

o public class NewClass implements java.io.Serializable

• Make private variables for the columns which are returned from the stored procedure.

o private String Emp_Name;o private String Emp_Address;o private Date Emp_DOB;o private Double Emp_Sal;

• Press ‘Alt+Insert’ with the cursor in the scope of the class. This command is to insert code in current class. This will open a window as shown below

o• Select Getter and Setter from the above. In the next pop up

window select all the private variables.


o

• Click Generate. This will generate all the getter and setters which your code requires

• The class is almost done. But we still need the constructor. For this, again do an ‘Alt+Insert’. Select Constructor in the dialog box. It will open a window which is shown as below.

o• Click Generate. This will generate a constructor for you. You

might as well add a default constructor in the code.• With this your POJO is created.

To Create a Hibernate Mapping File:• Create a new Hibernate Mapping File in the project• Under the <hibernate-mapping> tag, give the class name

which maps to your POJO created earlier. <class name="com.span.NewClass">

• Under class tag, put the property tags for the class and which column it maps to. For e.g.

o <property name="Emp_Name" type="java.lang.String">

o <column name=" Emp_Name" length="10" not-null="true" />

o </property>


• Like wise put property tag for all columns that are getting retrieved from the stored procedure and close the class tag.

• After the class tag and before ending the hibernate-mapping tag, we need to put <sql-query> tag as shown below.

o <sql-query name=" getEmpDetails" callable="true">

o <return alias=" getEmpDetails" class="com.span.NewClass">

o <return-property name="Emp_Name" column="Emp_Name"/>

o ..o ..o </return>o exec getEmpDetails :EmpNo, :Depto </sql-query>

• Close the </hibernate-mapping> tag

Lastly, Create a Java class to get the data• Get the session object from hibernate.

o SessionFactory factory = HibernateUtil.getSessionFactory();

o Session session = factory.getCurrentSession();o session.beginTransaction();

• get the query which you have defined in the Hibernate Mapping file.

o Query query = session.getNamedQuery("getEmpDetails ");

o query.setString("EmpNo", “1234”); //this should be the same variable as defined in exec query of hibernate mapping file. Another way of setting the parameters is by indexing. If the query in hibernate mapping file is exec getEmpDetails ?,? , then it would be query.setString(0, “1234”)

o query.setString("Dept", “HR”);• That’s it. Now you can get these values in a list.

o List<NewClass> result = query.list()• Close session by session.close();


Conclusion

The problem is simple yet tricky. Executing stored procedures through hibernate looks like a simple job, but when it comes to implementation, the development may wander off if the correct way of calling a stored procedure in MS SQL is not known.

This white paper will definitely ease down the effort of the development team to find the correct way of implementing the problem stated above.


hibernate-to-execute-stored-procedures

Documents