Hibernate named query examples

Scattered HQL string literals in Java code are hard to maintain and look ugly. To avoid it, Hibernate come out a technique called “names queries”, it lets developer to put all HQL into the XML mapping file or via annotation.

XML mapping file:

<?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="com.dto.EmployeeDTO"  table="EMPLOYEES">

            <id name="id" column="ID"><generator class="assigned"/></id>

            <property name="empId" column="EMP_ID" />

            <property name="empFirstName" column="EMP_FIRST_NAME" />

            <property name="empLastName" column="EMP_LAST_NAME" />

            <property name="createdOn" column="CREATED_ON"/>

      </class>

      <!-- Native SQL in named query -->

      <sql-query name="updateEmployee">

            <![CDATA[

                  UPDATE EMPLOYEES SET EMP_LAST_NAME=:empLastName WHERE EMP_ID=:employeeId

            ]]>

      </sql-query>

      <!-- HQL in named query -->

      <query name="findEmpById">

        <![CDATA[from EmployeeDTO e where e.empId = :employeeId]]>

    </query>

</hibernate-mapping>

HQL and Native SQL in annotation

<!-- HQL in named query -->

@NamedQueries({

     @NamedQuery(

                name = "updateEmployee",

                query = "from EmployeeDTO e where e.empId = :employeeId"

     )

})

<!-- Native SQL in named query -->

@NamedNativeQueries({

     @NamedNativeQuery(

                name = "updateEmployee",

                query = "UPDATE EMPLOYEES SET EMP_LAST_NAME=:empLastName WHERE EMP_ID=:employeeId",

                resultClass = EmployeeDTO.class

     )

})

@Entity

@Table(name = "EMPLOYEES")

public class EmployeeDTO implements java.io.Serializable {

     ...

}

Java Code to use the named Query:

import java.util.List;

import org.hibernate.Query;

import org.hibernate.Session;

import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

public class HibernateNamedQuery extends HibernateDaoSupport {

      public int updateEmployee(String p_employeeId, String p_modifiedBy) {

            Session session = null;

            try {

                  session = getHibernateTemplate().getSessionFactory().openSession();

                  Query query = session.getNamedQuery("updateEmployee");

                 

                  query = query.setString("empLastName", "Kumar");

                 

                  int rowsUpdated = query.executeUpdate();

     

                  return rowsUpdated;

            } finally {

                  releaseSession(session);

            }

      }

     

      public List<Object[]> fetchEmployeeDetails(String empId) {

            Session session = null;

            try {

                  session = getHibernateTemplate().getSessionFactory().openSession();

                 

                  Query query = session.getNamedQuery("findEmpById");

                  query = query.setString("employeeId",empId);

                 

                  return query.list();

            } finally {

                  releaseSession(session);

            }

      }

}