Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Call Stored Procedure In Hibernate

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 6.09k
    Comment on it

    Call Stored Procedure In Hibernate: Here is the simple answer of question 'How to call stored procedure in Hibernate?'.A stored procedure is a declarative SQL statements stored inside the database. A stored procedure can be invoked by triggers, using other stored procedures or applications such as Java, C#, PHP, etc. Hibernate provides the easy way to call the stored procedures. There are different ways to create query to call stored procedures in hibernate. As simple JDBC query we can also pass the parameters in stored procedures.

    Suppose user has a following stored procedure in the database :

    DELIMITER $$
    CREATE PROCEDURE 'getCustomers'(status varchar(20))
    BEGIN
       SELECT * FROM customerInfo WHERE status = status;
       END $$
    DELIMITER ;
    

    In database you can call stored procedure using triggers and direct put following statement :

    CALL getCustomers('Active');
    

    In Hibernate create a DAO class and use following code:

    1) Using Native Query :

    Query query = session.createSQLQuery(
            "CALL getCustomers(:status)")
            .addEntity(Customer.class)
            .setParameter("status", "Active");
    
    List result = query.list();
    for(int i=0; i<result.size(); i++){
            Customer customer = (Customer)result.get(i);
            System.out.println(customer.getCustomerName());
    }
    

    2) Using Named Native Query :

    At the top of Customer model just declare the NamedNative Query

    @NamedNativeQueries({
            @NamedNativeQuery(
            name = "callCustomerStoreProcedure",
            query = "CALL getCustomers(:status)",
            resultClass = Customer.class
            )
    })
    @Entity
    @Table(name = "customerInfo")
    public class Customer implements java.io.Serializable {
    private int customerId;
    private String customerName;
    private String status
    ---
    }
    

    In DAO code:

    Query query = session.getNamedQuery("callCustomerStoreProcedure")
            .setParameter("status", "Active");
    List result = query.list();
    for(int i=0; i<result.size(); i++){
            Customer customer = (Customer)result.get(i);
            System.out.println(customer.getCustomerName());
    }
    

    3) Using Sql query in XML mapping Customer.hbm.xml

    <hibernate-mapping>
        <class name="com.evon.common.Customer" table="customerInfo" >
            <id name="customerId" type="java.lang.Integer">
                <column name="customerId" />
                <generator class="identity" />
            </id>
            <property name="customerName" type="string">
             <column name="customerName" length="30" not-null="true" unique="true" />
            </property>
        <property name="status" type="string">
             <column name="status" length="20" not-null="true" />
            </property>
            ...
        </class>
    
        <sql-query name="callCustomerStoreProcedure">
            <return alias="customer" class="com.evon.common.Customer"/>
            <![CDATA[CALL getCustomers(:status)]]>
        </sql-query>
    
    </hibernate-mapping>
    

    In DAO code :

    Query query = session.getNamedQuery("callCustomerStoreProcedure").setParameter("status", "Active");
    List result = query.list();
    for(int i=0; i<result.size(); i++){
            Customer customer = (Customer)result.get(i);
            System.out.println(customer.getCustomerName());
    }
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: