• 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?

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

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 ;

/**
 * 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

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)