Invoke Stored Procedure Using Hibernate

What is STORED PROCEDURE?
A stored procedure is a pre-compiled SQL subroutine used to perform multiple procedural operations.

"A stored procedure is a subroutine available to applications that access a relational database system" - WikiPedia
"A stored procedure is a group of Transact-SQL statements compiled into a single execution plan" - Microsoft

What will we cover in this article?

• Sample MSSQL and MYSQL stored procedure.
• How to call MSSQL and MYSQL stored procedure.
• What should be taken care while executing stored procedure in Hibernate

MSSQL Sample Stored Procedure

IF (OBJECT_ID('SP_MSSQL_HIBERNATE') IS NOT NULL)
  DROP PROCEDURE SP_MSSQL_HIBERNATE
GO
CREATE PROCEDURE SP_MSSQL_HIBERNATE
@PARAM1 INT,
@PARAM2 INT,
@PARAM3 VARCHAR(50)
AS 
BEGIN
 BEGIN TRANSACTION
 BEGIN TRY
    /* 
     * Uncomment below code to get custom row in hibernate.
     * ------------------------------------------------
     * DECLARE @X, @Y INT;
     * DECLARE @RESULT INT;
     * SET @RESULT = @X + @Y;
     * SELECT @RESULT AS RESULT, 'javaQuery' AS STRING_RESULT;
     */
 
    /* Your custom operation */ 
    UPDATE user_master SET Firstname = @PARAM3 WHERE UID = 1;
           
    /* Insert record */
    INSERT INTO user_master (Firstname, Lastname) VALUES('Jennifer ', 'Lawrence');
           
    /* Returns user object (row) */
    SELECT * FROM user_master WHERE UID = 1;

 COMMIT TRANSACTION;
 END TRY
 BEGIN CATCH
          ROLLBACK TRANSACTION;
          PRINT @@ERROR
 END CATCH;
END

MYSQL Sample Stored Procedure

DROP PROCEDURE IF EXISTS SP_MYSQL_HIBERNATE;
DELIMITER $
CREATE PROCEDURE SP_MYSQL_HIBERNATE(IN PARAM1 INT, IN PARAM2 INT, IN PARAM3 VARCHAR(100))
BEGIN
 /*
  * Uncomment below code to get custom row in hibernate.
  * ------------------------------------------------
  * DECLARE X, Y INT DEFAULT 10;
  * DECLARE RESULT INT;
  * SET RESULT = X + Y;
  * SELECT RESULT AS RESULT, 'javaQuery' AS STRING_RESULT; 
  */

  /* Your custom operation */ 
  UPDATE user_master SET Firstname = PARAM3 WHERE UID = 1;
        
  /* Insert record */
  INSERT INTO user_master (Firstname, Lastname) VALUES('Jennifer ', 'Lawrence');

  /* Returns user object (row) */
  SELECT * FROM user_master WHERE UID = 1;
END $
DELIMITER ;

---

How to call MSSQL and MYSQL stored procedure in Hibernate?

/**
 * Create hibernate configuration.
 */
Configuration c = new Configuration();
c.configure("hibernate.cfg.xml");

/**
 * Open session and begin database transaction for database operation.
 */
SessionFactory sf = c.buildSessionFactory();
Session session = sf.openSession();
Transaction t = session.beginTransaction();

/**
 * Create SQL Query for Stored Procedure and pass required parameters.
 * MSSQL : EXEC Name_Of_Stored_Procedure :param1, :param2
 * MYSQL : CALL Name_Of_Stored_Procedure :param1, :param2
 * 
 * `.addEntity(User.class)` will map output result as per User bean.
 */

/**************************************************/
/* Call MSSQL Stored Procedure and MAP it to bean */
/* Un-comment the code                            */
/**************************************************/
/*Query callStoredProcedure_MSSQL = session.createSQLQuery("EXEC SP_MSSQL_HIBERNATE :param1, :param2, :param3").addEntity(User.class);
callStoredProcedure_MSSQL.setInteger("param1", 10);
callStoredProcedure_MSSQL.setInteger("param2", 10);
callStoredProcedure_MSSQL.setString("param3", "javaQuery");*/

/* callStoredProcedure_MSSQL.list() will execute stored procedure and return the value */
/*List userList = callStoredProcedure_MSSQL.list();
if (userList != null && !userList.isEmpty()) {
 for(User user : userList){
  System.out.println("Firstname:"+user.getFirstname());
 }
}*/


/**************************************************/
/* Call MYSQL Stored Procedure and MAP it to bean */
/**************************************************/
Query callStoredProcedure_MYSQL = session.createSQLQuery("CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)").addEntity(User.class);
callStoredProcedure_MYSQL.setInteger("param1", 10);
callStoredProcedure_MYSQL.setInteger("param2", 10);
callStoredProcedure_MYSQL.setString("param3", "javaQuery");

/* callStoredProcedure_MSSQL.list() will execute stored procedure and return the value */
List userList = callStoredProcedure_MYSQL.list();
if (userList != null && !userList.isEmpty()) {
 for(User user : userList){
  System.out.println("Firstname:"+user.getFirstname());
 }
}

/******************************************************************/
/* Process custom result of Stored Procedure                      */
/* Un-comment the code, This will be the same for MSSQL and MYSQL */
/******************************************************************/
/*Query callStoredProcedure_MYSQL = session.createSQLQuery("CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)");
callStoredProcedure_MYSQL.setInteger("param1", 10);
callStoredProcedure_MYSQL.setInteger("param2", 10);
callStoredProcedure_MYSQL.setString("param3", "javaQuery");*/

/* callStoredProcedure_MYSQL.list() will execute stored procedure and return the value */
/*List customResult = callStoredProcedure_MYSQL.list();
if (customResult != null && !customResult.isEmpty()) {
 Object[] obj = customResult.get(0);
 System.out.println(obj[0]);
 System.out.println(obj[1]);            
}*/

/* Commit the transaction and close session. */
t.commit();
session.close();

Above code contains 3 portion for execution. Un-comment your required code and test it.

1. How to Execute MSSQL Stored Procedure in Hibernate (code is commented)
2. How to Call MYSQL Stored Procedure in Hibernate
3. How to Process custom result of Stored Procedure in Hibernate (code is commented)

---

What should be taken care while executing stored procedure in Hibernate?

• If you have single Insert, Update or Delete operation in your stored procedure then you have to beginTransactin() and commit() it in order to take effect.
• Hibernate will only select first result of stored procedure. e.g: If you write two select statement then first result will be mapped for hibernate in case of bean and without bean it'll only return first result as list of object.
• If you are calling MSSQL stored procedure then SQL query must be without Parentheses ( and ).
  e.g EXEC SP_MSSQL_HIBERNATE :param1, :param2, :param3
• If you are calling MYSQL stored procedure then SQL query must be with Parentheses ( and ).
  e.g CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)

Hibernate Versioning Concept

Hibernate Versioning Concept

Once an object is saved in a database, we can modify that object any number of times right, If we want to know how many no of times that an object is modified then we need to apply this versioning concept.
When ever we use versioning then hibernate inserts version number as zero, when ever object is saved for the first time in the database.  Later hibernate increments that version no by one automatically when ever a modification is done on that particular object.
In order to use this versioning concept, we need the following two changes in our application

• Add one property of type int in our pojo class
• In hibernate mapping file, add an element called version soon after id element

Files required to execute this program..

• Product.java (My POJO class)
• Product.hbm.xml  (Xml mapping file )
• hibernate.cfg.xml  (Xml configuration file)
• ClientForSave_1.java (java file to write our hibernate logic)
• ClientForUpdate_2.java

Product.java

package str; public class Product{ private int productId; private String proName; private double price; private int v; public void setProductId(int productId) {     this.productId = productId; } public int getProductId() {     return productId; } public void setProName(String proName) {     this.proName = proName; } public String getProName() {     return proName; } public void setPrice(double price) {     this.price = price; } public double getPrice() {     return price; }        public void setV(int v) {     this.v = v; } public int getV() {     return v; } }

Product.hbm.xml

<?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="str.Product" table="products"> <id name="productId" column="pid"  /> <version name="v" column="ver" /> <property name="proName" column="pname" length="10"/> <property name="price"/> </class> </hibernate-mapping>

Note:

• In this above mapping file, find the <version> element, there i have given column name as version, actually you can write any name its not predefined.

hibernate.cfg.xml

<?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="connection.driver_class">oracle.jdbc.driver.OracleDriver </property> <property name="connection.url">jdbc:oracle:thin:@www.java4s.com:1521:XE</property> <property name="connection.username">system</property> <property name="connection.password">admin</property> <property name="dialect">org.hibernate.dialect.OracleDialect</property> <property name="show_sql">true</property> <property name="hbm2ddl.auto">update</property> <mapping resource="Product.hbm.xml"></mapping> </session-factory> </hibernate-configuration>

ClientForSave_1.java

package str; import org.hibernate.*; import org.hibernate.cfg.*; public class ClientForSave_1 {     public static void main(String[] args)     {         Configuration cfg = new Configuration();         cfg.configure("hibernate.cfg.xml");         SessionFactory factory = cfg.buildSessionFactory();         Session session = factory.openSession();         Product p=new Product();         p.setProductId(104);         p.setProName("AC");         p.setPrice(14000);         Transaction tx = session.beginTransaction();         session.save(p);         System.out.println("Object saved successfully.....!!");         tx.commit();         session.close();         factory.close();     } }

Note:

• Remember friends, first we must run the logic to save the object then hibernate will inset 0 (Zero) by default in the version column of the database, its very important point in the interview point of view also
• First save logic to let the hibernate to insert zero in the version column, then any number of update logic’s (programs) we run, hibernate will increments +1 to the previous value
• But if we run the update logic for the first time, hibernate will not insert zero..! it will try to increment the previous value which is NULL in the database so we will get the exception.

ClientForUpdate_2.java

package str; import org.hibernate.*; import org.hibernate.cfg.*; public class ClientForUpdate_2 { public static void main(String[] args) { Configuration cfg = new Configuration(); cfg.configure("hibernate.cfg.xml"); SessionFactory factory = cfg.buildSessionFactory(); Session session = factory.openSession(); Object o=session.load(Product.class,new Integer(104)); Product s=(Product)o; Transaction tx = session.beginTransaction(); s.setPrice(4000); // implicitly update method will be call tx.commit(); System.out.println("Object Updated successfully.....!!"); session.close(); factory.close(); } }

First run the ClientForSave_1.java, then only ClientForUpdate_2.java
&

Eclipse Output

In the database

Note(Important..!!!)______
Guys your know some thing.., actually we can run any logic (Save or Update) for the first time, but make sure the versioning column is a number (>=0), but save logic has ability to insert zero by default if there is no value, and update logic will directly tries to increments already existing value by 1, it wont insert any value by default if its null, hope you are clear about this point, and mates its the very important concept in the interview point of view, if you have any doubt just Ask a Question on this our team will respond ASAP

Hibernate Update Query

Hibernate Update Query

This is the program to update an object (1 complete row) in the database, which is already persisted in the database, then we have the following two approaches…

Approach 1

Load that object from the database, and modify its values, now hibernate automatically modifies the values on to database also, when ever the transaction is committed.

Approach 2:

If we want to modify object in the database, then create new object with same id and we must call update() given by session interface.
Files required to execute this program..

• Product.java (My POJO class)
• product.hbm.xml (Xml mapping file )
• hibernate.cfg.xml (Xml configuration file)
• ClientProgram.java(java file to write our hibernate logic)

Related to approach 1:

Product.java (POJO)

package str; public class Product{ private int productId; private String proName; private double price; public void setProductId(int productId) { this.productId = productId; } public int getProductId() { return productId; } public void setProName(String proName) { this.proName = proName; } public String getProName() { return proName; } public void setPrice(double price) { this.price = price; } public double getPrice() { return price; } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

package str; public class Product{ private int productId; private String proName; private double price; public void setProductId(int productId) {     this.productId = productId; } public int getProductId() {     return productId; } public void setProName(String proName) {     this.proName = proName; } public String getProName() {     return proName; } public void setPrice(double price) {     this.price = price; } public double getPrice() {     return price; } }

Product.hbm.xml

<?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="str.Product" table="products"> <id name="productId" column="pid" /> <property name="proName" column="pname" length="10"/> <property name="price"/> </class> </hibernate-mapping>

1 2 3 4 5 6 7 8 9 10 11 12 13 14

<?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="str.Product" table="products"> <id name="productId" column="pid"  /> <property name="proName" column="pname" length="10"/> <property name="price"/> </class> </hibernate-mapping>

hibernate.cfg.xml

<?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="connection.driver_class">oracle.jdbc.driver.OracleDriver </property> <property name="connection.url">jdbc:oracle:thin:@www.java4s.com:1521:XE</property> <property name="connection.username">system</property> <property name="connection.password">admin</property> <property name="dialect">org.hibernate.dialect.OracleDialect</property> <property name="show_sql">true</property> <property name="hbm2ddl.auto">update</property> <mapping resource="Product.hbm.xml"></mapping> </session-factory> </hibernate-configuration>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

<?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="connection.driver_class">oracle.jdbc.driver.OracleDriver </property> <property name="connection.url">jdbc:oracle:thin:@www.java4s.com:1521:XE</property> <property name="connection.username">system</property> <property name="connection.password">admin</property> <property name="dialect">org.hibernate.dialect.OracleDialect</property> <property name="show_sql">true</property> <property name="hbm2ddl.auto">update</property> <mapping resource="Product.hbm.xml"></mapping> </session-factory> </hibernate-configuration>

ClientProgram.java (* Recommended way)

package str; import org.hibernate.*; import org.hibernate.cfg.*; public class ForOurLogic { public static void main(String[] args) { Configuration cfg = new Configuration(); cfg.configure("hibernate.cfg.xml"); SessionFactory factory = cfg.buildSessionFactory(); Session session = factory.openSession(); Object o=session.load(Product.class,new Integer(105)); Product s=(Product)o; Transaction tx = session.beginTransaction(); //s.setStno(105); should not update, because we loaded with that number right..? s.setPrice(4000); // implicitly update method will be called.. tx.commit(); System.out.println("Object Updated successfully.....!!"); session.close(); factory.close(); } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

package str; import org.hibernate.*; import org.hibernate.cfg.*; public class ForOurLogic { public static void main(String[] args) { Configuration cfg = new Configuration(); cfg.configure("hibernate.cfg.xml"); SessionFactory factory = cfg.buildSessionFactory(); Session session = factory.openSession(); Object o=session.load(Product.class,new Integer(105)); Product s=(Product)o; Transaction tx = session.beginTransaction(); //s.setStno(105);   should not update, because we loaded with that number right..? s.setPrice(4000);   // implicitly update method will be called.. tx.commit(); System.out.println("Object Updated successfully.....!!"); session.close(); factory.close(); } }

Notes:

• See line number 20, actually there i tried to update Stno(105), we should not do this, because we have loaded the object from the database with his id number only, see line number 16, if we update hibernate will rises the exception
• See line number 24 once we call the commit(), automatically update method will be called by hibernate.
• When ever an object is loaded from the database then hibernate stores the loaded object in cache-memory maintained by session-interface
• Once an object is loaded, if we do any modifications on that object by calling its setter methods, then these modification are stored in the object maintained by cache-memory
• if we modify the loaded object for multiple times then also the modifications will be stored in object maintained by the cache-memory only.
• when ever we issue commit() operation then hibernate verify whether any changes are there between the object stored in the cache and object in the database, if changes exists then hibernate automatically updates the database by generating any update operation.
• What am saying is hibernate automatically maintains synchronization between cache-memory object and database table objects (rows)

Related to approach 2:

<strong> </strong>package str; import org.hibernate.*; import org.hibernate.cfg.*; public class ForOurLogic { public static void main(String[] args) { Configuration cfg = new Configuration(); cfg.configure("hibernate.cfg.xml"); SessionFactory factory = cfg.buildSessionFactory(); Session session = factory.openSession(); Product p=new Product(); p.setProductId(104); // 104 must be in the DB p.setProName("Someting"); Transaction tx = session.beginTransaction(); session.update(p); tx.commit(); System.out.println("Object Updated successfully.....!!"); session.close(); factory.close(); } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

<strong> </strong>package str; import org.hibernate.*; import org.hibernate.cfg.*; public class ForOurLogic { public static void main(String[] args) { Configuration cfg = new Configuration(); cfg.configure("hibernate.cfg.xml"); SessionFactory factory = cfg.buildSessionFactory(); Session session = factory.openSession(); Product p=new Product(); p.setProductId(104);  // 104 must be in the DB p.setProName("Someting"); Transaction tx = session.beginTransaction(); session.update(p); tx.commit(); System.out.println("Object Updated successfully.....!!"); session.close(); factory.close(); } }

Oracle DB, Before Run The Program

Oracle DB, After Run the program

Notes:

• From line number 17 to 19, we created new object and modified, and in line number 22 we are calling update method explicitly
• Here we no need to load an object from the database
• we will create a new object, and we will assign same id no’s to it and we will call update() explicitly in order to make the changes on the object that is stored in the database

That’s it, actually first approach is  recommended always..