File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Persistence: Calling Stored Procedures from Hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Persistence: Calling Stored Procedures from Hibernate" Watch "Persistence: Calling Stored Procedures from Hibernate" New topic
Author

Persistence: Calling Stored Procedures from Hibernate

Bhim Upadhyaya
Greenhorn

Joined: Jul 11, 2010
Posts: 12
I did not find much resources for using stored procedures in hibernate, thought of sharing some of my work for feedback! It seems there are two major approaches of integrating persistence sytems with java based business tiers. The first one, architects first design databases (from entity-relationship view point) and design business tiers to adapt with those DB structures. Most of the old/legacy systems are found falling in this category. The architects who have rich experience in OO design, they first model the real world business with objects, i. e. first the object systems, these objects are actually data, then they think about persisting those data. In this approach, DB structure is driven by OO system. I THINK THAT'S WHERE HIBERNATE FITS THE BEST. However, it can adapt with E-R driven system design though it may not be the best. iBATIS might provide more flexibility for legacy systems.

DISCLAIMER: THE CODE MAY CONTAIN UNUSED/REDUNDANT/UNNECESSARY LOCs, PLEASE USE WITH YOUR JUDGEMENT! ANY NAMES USED ARE HYPOTHETICAL AND DO NOT REFLECT ANY ORGANIZATIONAL NAMES. THE CODE ALSO DOES NOT REFLECT ANY ORGANIZATIONAL POLICIES AND IT IS NOT ATTACHED TO ANY ORGANIZATION.

STEP 1: Java Calling Methods:
---------------------------------------------------
Step 1-a (Only Using Hibernate Connection Manager):

// PERMISSION OK : grant exec on CMC_CLASS_SP_7 to ECONUSER
public static void callDBResultSetMSSQL(){
Session session = null;
CallableStatement callable = null;
Connection con = null;
List<CMCClass> classes = null;
Transaction transaction = null;

session = HibernateUtil.getSessionFactoryDBSW9079().openSession();

try {
con = session.connection();
//transaction = session.beginTransaction();
callable = con.prepareCall("{call [MS\\xxxx].CMC_CLASS_SP_7}");
ResultSet rs1 = callable.executeQuery();

while(rs1.next()){
System.out.println("cscsId: "+rs1.getString("CSCS_ID")+" grgrCK: "+rs1.getInt("GRGR_CK")+" cscsDesc: "+rs1.getString("CSCS_DESC"));
}
// transaction.commit();
} catch(HibernateException e){
e.printStackTrace();
} catch(Exception e){
e.printStackTrace();
} finally {
session.close();
}

}
/* ALTER PROCEDURE CMC_CLASS_SP_7
AS
SELECT CSCS_ID, GRGR_CK, CSCS_DESC FROM CMC_CSCS_CLASS WHERE GRGR_CK > 0 AND GRGR_CK < 3
*/


Step 1-b (Calling Unparameterized Stored Procedure):

// WORKS, UNPARAMETERIZED
public static void callDBXMLMSSQL(){
Session session = null;
CallableStatement callable = null;
Connection con = null;
List<CMCClass> classes = null;
Transaction transaction = null;

session = HibernateUtil.getSessionFactoryDBSW9079().openSession();
try {
transaction = session.beginTransaction();
classes = session.getNamedQuery("selectCMCClass_SP_7").list();
for (Iterator iterator = classes.iterator(); iterator.hasNext();) {
CMCClass cmcClass = (CMCClass) iterator.next();
System.out.println(cmcClass.getCscsId()+" :: "+cmcClass.getCscsDesc()+" :: "+cmcClass.getGrgrCK());
}
transaction.commit();
} catch (HibernateException e) {
transaction.rollback();
e.printStackTrace();
} finally {
session.close();
}

}


Step 1-c (Calling Parameterized Stored Procedure):


// PARAMETERIZED
public static void callDBXMLMSSQLP(){
Session session = null;
CallableStatement callable = null;
Connection con = null;
List<CMCClass> classes = null;
Transaction transaction = null;

session = HibernateUtil.getSessionFactoryDBSW9079().openSession();
try {
transaction = session.beginTransaction();
classes = session.getNamedQuery("selectCMCClass_SP_8")
.setParameter("startIndex", 1)
.setParameter("endIndex", 3)
.list();
for (Iterator iterator = classes.iterator(); iterator.hasNext();) {
CMCClass cmcClass = (CMCClass) iterator.next();
System.out.println(cmcClass.getCscsId()+" :: "+cmcClass.getCscsDesc()+" :: "+cmcClass.getGrgrCK());
}
transaction.commit();
} catch (HibernateException e) {
transaction.rollback();
e.printStackTrace();
} finally {
session.close();
}

}


STEP 2: Hibernate configuration file (contains driver info):
----------------------------------------------------------------------------------
[dbsw9079.cfg.xml] (you could name hibernate.cfg.xml too or anything else that makes sense)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
<property name="hibernate.connection.password">YOUR-DB-PASSWORD</property>
<!--
<property name="hibernate.connection.url">YOUR-DB-CONNECTION-URL-HERE</property>
-->
<!--
<property name="hibernate.connection.url">YOUR-DB-CONNECTION-URL-HERE</property>
-->
<property name="hibernate.connection.url">YOUR-DB-CONNECTION-URL-HERE</property>
<property name="hibernate.connection.username">YOUR-DB-USER-NAME</property>
<property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
<property name="connection.pool_size">1</property>
<property name="show_sql">true</property>
<property name="hbm2ddl.auto">create</property>
<!--
<mapping resource="com/rivervalley/policy/Policy.hbm.xml"/>
-->
<mapping resource="ECIMPhysician.hbm.xml"/>
<mapping resource="CMCClass.hbm.xml"/>
<mapping resource="CMCClassSP.hbm.xml"/>
</session-factory>
</hibernate-configuration>





STEP 3: Named Query, Stored Procedures Mapping File:
---------------------------------------------------------------------------------
[CMCClassSP.hbm.xml]

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<sql-query name="selectCMCClass_SP_3" callable="true">
<return alias="cmcClass" class="com.rivervalley.cmc_class.CMCClass">
<return-property name="cscsId" column="CSCS_ID"/>
<return-property name="grgrCK" column="GRGR_CK"/>
<return-property name="cscsDesc" column="CSCS_DESC"/>
</return>
{ call CMC_CLASS_SP_3(?,:startIndex,:endIndex)}
</sql-query>

<sql-query name="selectCMCClass_SP_7" callable="true">
<return alias="cmcClass" class="com.rivervalley.cmc_class.CMCClass">
<return-property name="cscsId" column="CSCS_ID"/>
<return-property name="grgrCK" column="GRGR_CK"/>
<return-property name="cscsDesc" column="CSCS_DESC"/>
</return>
{ call [MS\xxxx].CMC_CLASS_SP_7()}
</sql-query>

<sql-query name="selectCMCClass_SP_8" callable="true">
<return alias="cmcClass" class="com.rivervalley.cmc_class.CMCClass">
<return-property name="cscsId" column="CSCS_ID"/>
<return-property name="grgrCK" column="GRGR_CK"/>
<return-property name="cscsDesc" column="CSCS_DESC"/>
</return>
{ call [MS\xxxx].CMC_CLASS_SP_8(:startIndex,:endIndex)}
</sql-query>

</hibernate-mapping>

===========================================================
If you are curious, how CMCClass.hbm.xml looks like:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.rivervalley.cmc_class.CMCClass" table="CMC_CSCS_CLASS">
<meta attribute="class-description">
This class contains the CMC class description.
</meta>
<id name="cscsId" type="string" column="CSCS_ID">
<generator class="native"/>
</id>
<property name="grgrCK" type="int" column="GRGR_CK" not-null="true" />
<property name="cscsDesc" type="string" column="CSCS_DESC" not-null="true" />
</class>
</hibernate-mapping>

===========================================================
ADDITIONAL STUFFS:

HibernateUtil.java (for multiple database connectivity)

package com.rivervalley.util;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HibernateUtil {
//private static final SessionFactory sessionFactory;
private static final SessionFactory sfFacetsd2;
private static final SessionFactory sf_dbsw9079;
private static final SessionFactory sfFacetst2;
static {
try {
sfFacetsd2 = new Configuration().configure("facetsd2.cfg.xml").buildSessionFactory();
sf_dbsw9079 = new Configuration().configure("dbsw9079.cfg.xml").buildSessionFactory();
sfFacetst2 = new Configuration().configure("facetst2.cfg.xml").buildSessionFactory();
//sessionFactory = new Configuration().configure().buildSessionFactory();
} catch (Throwable ex) {
System.err.println("Initial SessionFactory creation failed." + ex);
throw new ExceptionInInitializerError(ex);
}
}

public static SessionFactory getSessionFactoryFacetsD2() {
return sfFacetsd2;
}

public static SessionFactory getSessionFactoryDBSW9079() {
return sf_dbsw9079;
}

public static SessionFactory getSessionFactoryFacetsT2() {
return sfFacetst2;
}
}



 
 
subject: Persistence: Calling Stored Procedures from Hibernate