This tutorial shows how we can call stored procedure in Hibernate API.

Prerequisites

Hibernate 3.x jars, mysql-connector-java jar, jdk 1.6, MySQL 5.x

stored procedure example in hibernate

1. Create a Store Procedure in MySQL in database cdcol

DELIMITER $$

CREATE
    PROCEDURE `cdcol`.`getCds`(cdid BIGINT)
    BEGIN
    SELECT * FROM cds WHERE id=cdid;
    END$$

DELIMITER ;

 


2. Create hibernate.cfg.xml file with the following content and put the file under classpath

<?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.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/cdcol?zeroDateTimeBehavior=convertToNull</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.show_sql">true</property>
    <property name="hibernate.format_sql">true</property>
    <property name="hibernate.current_session_context_class">thread</property>
    <property name="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</property>
    <mapping resource="in/sblog/domain/Cds.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

 

3. Create hibernate.reveng.xml file with the following content and put the file under classpath

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-reverse-engineering PUBLIC "-//Hibernate/Hibernate Reverse Engineering DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-reverse-engineering-3.0.dtd">
<hibernate-reverse-engineering>
  <schema-selection match-catalog="cdcol"/>
  <table-filter match-name="cds"/>
</hibernate-reverse-engineering>

 

4. Create the hibernate mapping file and domain object for cds table which is in cdcol database

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated Mar 25, 2014 8:54:56 PM by Hibernate Tools 3.6.0 -->
<hibernate-mapping>
    <class name="in.sblog.domain.Cds" table="cds" catalog="cdcol">
        <id name="id" type="java.lang.Long">
            <column name="id" />
            <generator class="identity" />
        </id>
        <property name="titel" type="string">
            <column name="titel" length="200" />
        </property>
        <property name="interpret" type="string">
            <column name="interpret" length="200" />
        </property>
        <property name="jahr" type="java.lang.Integer">
            <column name="jahr" />
        </property>
    </class>
    <sql-query name="callCdStoreProcedure">
        <return alias="cd" class="in.sblog.domain.Cds"/>
        <![CDATA[CALL getCds(:cdId)]]>
    </sql-query>
</hibernate-mapping>

 

/**
 * Cds generated by hbm2java
 */
public class Cds implements java.io.Serializable {

    private Long id;
    private String titel;
    private String interpret;
    private Integer jahr;

    public Cds() {
    }

    public Cds(String titel, String interpret, Integer jahr) {
        this.titel = titel;
        this.interpret = interpret;
        this.jahr = jahr;
    }

    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitel() {
        return this.titel;
    }

    public void setTitel(String titel) {
        this.titel = titel;
    }

    public String getInterpret() {
        return this.interpret;
    }

    public void setInterpret(String interpret) {
        this.interpret = interpret;
    }

    public Integer getJahr() {
        return this.jahr;
    }

    public void setJahr(Integer jahr) {
        this.jahr = jahr;
    }

}

 

5. Create the HibernateUtil.java for session factory

import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.SessionFactory;

/**
 * Hibernate Utility class with a convenient method to get Session Factory
 * object.
 *
 * @author admin
 */
public class HibernateUtil {

    private static final SessionFactory sessionFactory;

    static {
        try {
            // Create the SessionFactory from standard (hibernate.cfg.xml)
            // config file.
            sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
        } catch (Throwable ex) {
            // Log the exception.
            System.err.println("Initial SessionFactory creation failed." + ex);
            throw new ExceptionInInitializerError(ex);
        }
    }

    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }
}

 

6. Now create the helper class which will query for invoking store procedure. I will show here two approaches – native sql and named query.

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

/**
 *
 * @author admin
 */
public class StoredProc {
    
    Session session = null;
    
    public StoredProc() {
        session = HibernateUtil.getSessionFactory().getCurrentSession();
    }
    
    public Cds getCdsByNativeSQL(long cdId) {
        try {
            Transaction t = session.beginTransaction();
            Query q = session.createSQLQuery("CALL getCds(:cdId)").addEntity(Cds.class).setParameter("cdId", cdId);
            Cds cd = (Cds) q.uniqueResult();
            return cd;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public Cds getCdsByNamedQuery(long cdId) {
        try {
            Transaction t = session.beginTransaction();
            Query q = session.getNamedQuery("callCdStoreProcedure").setParameter("cdId", cdId);
            Cds cd = (Cds) q.uniqueResult();
            return cd;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

 

7. Now create a main class for testing both the approaches.

public class TestProcedure {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        StoredProc sp = new StoredProc();
        Cds cd1 = sp.getCdsByNativeSQL(1);
        System.out.println("Title: " + cd1.getTitel());
        System.out.println("Interpret: " + cd1.getInterpret());

        Cds cd2 = sp.getCdsByNamedQuery(4);
        System.out.println("Title: " + cd2.getTitel());
        System.out.println("Interpret: " + cd2.getInterpret());
    }

}

 

Outputs:

Hibernate:
    CALL getCds(?)
Title: Beauty
Interpret: Ryuichi Sakamoto
Hibernate:
    CALL getCds(?)
Title: Goodbye Country (Hello Nightclub)
Interpret: Groove Armada

 

That’s all. Thank your for your patience. Please do not forget to leave a comment.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

0 thoughts on “Stored procedure example in Hibernate

  1. Hi

    From the Hibernate docs, I could see that it is recommended to use commit. Any specific reason why you didn’t commit the transaction.

    I’m having a problem where commit of get storedprocedure auto invoking update which I don’t want. I guess this is due to flush but I don’t want this to happen.

    1. the call to close() commits the transaction, that’s why I did not commit. You can use the property false in hibernate configuration to prevent autocommit and use the commit() in java code.

Leave a Reply

Your email address will not be published. Required fields are marked *