STORED PROCEDURE steeps in SPRING using HIBERNATE

STORED PROCEDURE steeps in SPRING using HIBERNATE
=====================================================
Spring supports store procedure as efficient manner. For this reason spring provides StoredProcedure class to implement Store Procedure in your application. Spring can supports all Types of Database store procedure like Oracle, MySQL, MSSQL etc.

There are some steps to implement Store Procedure in your application given bellow-
          1) Write store procedure in your DB side.
          2) Crete a store procedure class extending the StoredProcedure Class.
                       a) public class MyStoreProcedure extends StoredProcedure{
                       b) declare Store Procedure name as static globally in the class.
                           Exp: public static String PROCEDURE_NAME=”StoreProcedureName”;
                       c) In this class you have define first procedure parameter name if it is required. There are two way to
                           define parameter. You can define all parameter in the constructor or in the method. The most efficient
                           way to declare the parameter is through the constructor.
                       d) Before defined parameter you have to pass the DataSource to the spring by overriding the
                           constructor passing DataSource and Store Procedure name.
                           Exp: MyStoreProcedure(DataSource ds){
                                    super(ds, PROCEDURE_NAME);
                       e) Now declared all parameter to pass the data. All parameter name should same name of the Stored
                           Procedure.
                           Exp: declaredParameter(new SqlParametr (“Store Procedure Parameter name”, Types.VARCHAR);
                       f) Call compaile() method. This compaile method create a virtual stored procedure query for the
                           application supported by java. This method internally call the Calleble Statement of the stored
                           procedure.
                           Exp: compile();
          3) Write method to pass the parameter value.
                      a) and set all the value in a HashMap as Key Value paire. Key value should be same name of the Store
                          Procedure parameter name.
                          Esp: Map inParams = new HashMap();
                                  inParams.put("Store Procedure Parameter name ", Value);
                      b) Call execute(inParams) method. This method internally call JDBC execute method and for Prepared
                           Statement and execute Stored Procedure.
          4) Now write statement to call the Store Procedure
                      a) Before calling the Store Procedure you have to confirm to refresh the connection session or hibernate
                          session.
                          Exp: getSession().flush();
                      b) Constuctuct or cll the method to define parameter passing by the DataSource.
                           Exp: MyStoreProcedure proc = new MyStoreProcedure (DataSource);
                       c) Call the parameter value passing method
                           Exp: proc.parametrValuePassMethod(Value);

EXAMPLE:
------------
STORED PROCEDURE steeps in SPRING using HIBERNATE, MySQL exampl
----------------------------------------------------------------------
Store Procedure
---------------------
DELIMITER $$
         DROP PROCEDURE IF EXISTS `DB_NAME`.`STORE_PROCEDURE_NAME`$$
                  CREATE DEFINER=`USER_NAME`@`IP_ADDRESS`
                  PROCEDURE `STORE_PROCEDURE_NAME`(IN PARAM_VALUE VARCHAR(30))

                   UPDATE TABLE_NAME SET COLUMN_NAME = PARAM_VALUE;
          END$$
DELIMITER ;

Store Procedure Class
---------------------------
/**===================================================
* This is the Store Procedure implemantetion calss that
* extends StoredProcedure
*==================================================*/
public class MyStoreProcedure extends StoredProcedure {
           /** The Constant STORED_PROC_NAME. */
           private static final String SORE_PROCEDURE_NAME =”STORE_PROCEDURE_NAME";

            /**=================================================
             * Instantiates a new MyStoreProcedure info Stored Procedure.
             * @param ds the data source
             *=====================================================*/
             public MyStoreProcedure (DataSource ds) {
                        super(ds, SORE_PROCEDURE_NAME);
                        declareParameter(new SqlParameter("PARAM_VALUE", Types.VARCHAR));
                        compile();
              }

              /**=================================================
               * Execute the procedure.
               * @param modified_date
               *=====================================================*/
               public void executeParamValue(String paramValue) {
                          Map inParams = new HashMap();
                          inParams.put("PARAM_VALUE ", paramValue);
                          execute(inParams);
               }
}


Application DAO Class
-------------------------------
/**====================================================
* This is the main DAO Application interface
*===================================================*/
public interface IMyApplicationDAO {
           public void storeAccVchrInfoForGRN();
}

/**===================================================
* This is the main DAO Application class
*==================================================*/
public class MyApplicationDAO implements IMyApplicationDAO {
           /** Data source property */
           private DataSource dataSource;

           /** Data source injector */
           public void setDataSource(DataSource dataSource) {
                      this.dataSource = dataSource;
            }

            /**==================================================
             * call the stored procedure method
             *=================================================*/
             public void storeAccVchrInfoForGRN(String paramValue){
                        getSession().flush();
                        MyStoreProcedure proc = new MyStoreProcedure (dataSource);
                        proc. executeParamValue(paramValue);
              }
}

Application DAO Bean
      

        <bean class="package.myApplicationDAO" id=" myApplicationDAO ">
                       <property name="dataSource">
                                    <ref bean="dataSource"> </ref>
                       </property>
         </bean>

Application Hibernate Data Sourse Bean

          <bean class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close" id="dataSource">
                          <!-- MySQL Driver configuration -->
                          <property name="driverClass" value="com.mysql.jdbc.Driver">
                                       <property name="jdbcUrl" value="jdbc:mysql://IP_ADDRESS:PORT/DB_NAME">
                                                     <property name="user" value="root">
                                                                  <property name="password" value="root"> </property>
                                                    </property>
                                       </property>
                          </property>
           </bean>