Dinesh on Java

Named Queries in Hibernate Example

Often times, developer like to put HQL string literals scatter all over the Java code, this method is hard to maintain and look ugly. Fortunately, Hibernate come out a technique called “named queries” , it lets developer to put all HQL into the XML mapping file or via annotation.

Named Query

Named Query is very useful concept in hibernate. It lets you separate queries from coding section of the application to the mapping xml file(.hbm files). The query is given unique name for the entire application. The application can use the query by using the name of the query. This way the application is able to use the same query multiple times without writing the same query multiple times.

1. XML mapping file:

student.hbm.xml

Native SQL in mapping file:

<hibernate-mapping>
    <class name="com.sdnext.hibernate.tutorial.dto.Student" table="STUDENT">
        <id column="ID" name="id" type="int">
  <generator class="increment">
           
  </generator></id>
        <property name="rollNumber" type="int">
            <column name="ROLL_NUMBER">
        </column></property>
        <property name="studentName" type="String">
            <column name="STUDENT_NAME"></column>
        </property>
        <property name="course" type="String">
            <column name="COURSE"></column>
        </property>
    </class>
 
    <sql-query name="findStudentByRollNumber">
       <return alias="student" class="com.sdnext.hibernate.tutorial.dto.Student"> 
         <!--[CDATA[
            select * from Student student where student.rollNumber = :rollNumber
          ]]-->
     </return>
     </sql-query>
  </hibernate-mapping>

HQL in mapping file

<hibernate-mapping>
    <class name="com.sdnext.hibernate.tutorial.dto.Student" table="STUDENT">
        <id column="ID" name="id" type="int">
  <generator class="increment">
           
  </generator></id>
        <property name="rollNumber" type="int">
            <column name="ROLL_NUMBER">
        </column></property>
        <property name="studentName" type="String">
            <column name="STUDENT_NAME"></column>
        </property>
        <property name="course" type="String">
            <column name="COURSE"></column>
        </property>
    </class>
 
    <query name="findStudentByRollNumber">
        <!--[CDATA[
            from Student student where student.rollNumber = :rollNumber
        ]]-->
    </query>
  </hibernate-mapping>

You can place a named query inside hibernate-mapping element, but do not put before the class element, Hibernate will prompt invalid mapping file, all your named queries have to put after the class element.

hibernate.cfg.xml

<hibernate-configuration> 
 <session-factory> 
  <!-- Database connection settings -->
   <property name="connection.driver_class">com.mysql.jdbc.Driver</property> 
   <property name="connection.url">jdbc:mysql://localhost:3306/hibernateDB2</property> 
   <property name="connection.username">root</property> 
   <property name="connection.password">root</property> 

  <!-- JDBC connection pool (use the built-in) -->
   <property name="connection.pool_size">1</property> 

  <!-- SQL dialect -->
   <property name="dialect">org.hibernate.dialect.MySQLDialect</property> 

  <!-- Enable Hibernate's automatic session context management -->
    <property name="current_session_context_class">thread</property> 
  
  <!-- Disable the second-level cache -->
   <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property> 

  <!-- Echo all executed SQL to stdout -->
   <property name="show_sql">true</property> 
  
  <!-- Drop and re-create the database schema on startup -->
   <property name="hbm2ddl.auto">update</property> 
   
   
   <mapping resource="student.hbm.xml">
      
  </mapping></session-factory> 
 </hibernate-configuration>

HibernateTestDemo.java

package com.sdnext.hibernate.tutorial;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

import com.sdnext.hibernate.tutorial.dto.Student;

public class HibernateTestDemo {
 /**
  * @param args
  */
 public static void main(String[] args) 
 {
  SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
  Session session = sessionFactory.openSession();
  session.beginTransaction();
  
  Query query = session.getNamedQuery("findStudentByRollNumber").setInteger("rollNumber", 3);
  
  List students = query.list();
  for(Student student : students)
  {
   System.out.println(student);
  }
  session.getTransaction().commit();
  session.close();
 }
}

2. Annotation:

HQL in annotation

@NamedQueries({
 @NamedQuery(
 name = "findStudentByRollNumber",
 query = "from Student student where student.rollNumber = :rollNumber"
 )
})
@Entity
@Table(name = "STUDENT")
public class Student implements java.io.Serializable {
...

Native SQL in annotation

@NamedNativeQueries({
 @NamedNativeQuery(
 name = "findStudentByRollNumber",
 query = "select * from Student student where student.rollNumber = :rollNumbe"
        resultClass = Student.class
 )
})
@Entity
@Table(name = "STUDENT")
public class Student implements java.io.Serializable {
...

In native SQL, you have to declare the ‘resultClass‘ to let Hibernate know what is the return type, failed to do it will caused the exception “org.hibernate.cfg.NotYetImplementedException: Pure native scalar queries are not yet supported”.

Example:

Student.java

package com.sdnext.hibernate.tutorial.dto;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@NamedQueries({  
  @NamedQuery(  
  name = "findStudentByRollNumber",  
  query = "from Student student where student.rollNumber = :rollNumber"  
  )  
 })  
@Entity
@Table(name="STUDENT")
public class Student implements Serializable 
{
 /**
  * serialVersionUID
  */
 private static final long serialVersionUID = 8633415090390966715L;
 @Id
 @Column(name="ID")
 @GeneratedValue(strategy=GenerationType.AUTO)
 private int id;
 @Column(name="STUDENT_NAME")
 private String studentName;
 @Column(name="ROLL_NUMBER")
 private int rollNumber;
 @Column(name="COURSE")
 private String course;
 public int getId() {
  return id;
 }
 public void setId(int id) {
  this.id = id;
 }
 public String getStudentName() {
  return studentName;
 }
 public void setStudentName(String studentName) {
  this.studentName = studentName;
 }
 public int getRollNumber() {
  return rollNumber;
 }
 public void setRollNumber(int rollNumber) {
  this.rollNumber = rollNumber;
 }
 public String getCourse() {
  return course;
 }
 public void setCourse(String course) {
  this.course = course;
 }
 public String toString()
 {
  return "ROLL Number: "+rollNumber+"| Name: "+studentName+"| Course: "+course;
 }
}

hibernate.cfg.xml

<hibernate-configuration> 
 <session-factory> 
  <!-- Database connection settings -->
   <property name="connection.driver_class">com.mysql.jdbc.Driver</property> 
   <property name="connection.url">jdbc:mysql://localhost:3306/hibernateDB2</property> 
   <property name="connection.username">root</property> 
   <property name="connection.password">root</property> 

  <!-- JDBC connection pool (use the built-in) -->
   <property name="connection.pool_size">1</property> 

  <!-- SQL dialect -->
   <property name="dialect">org.hibernate.dialect.MySQLDialect</property> 

  <!-- Enable Hibernate's automatic session context management -->
    <property name="current_session_context_class">thread</property> 
  
  <!-- Disable the second-level cache -->
   <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property> 

  <!-- Echo all executed SQL to stdout -->
   <property name="show_sql">true</property> 
  
  <!-- Drop and re-create the database schema on startup -->
   <property name="hbm2ddl.auto">update</property> 
   
   
   <mapping class="com.sdnext.hibernate.tutorial.dto.Student">
      
  </mapping></session-factory> 
 </hibernate-configuration>

HibernateTestDemo.java

package com.sdnext.hibernate.tutorial;

import java.util.List;

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

import com.sdnext.hibernate.tutorial.dto.Student;

public class HibernateTestDemo {
 /**
  * @param args
  */
 public static void main(String[] args) 
 {
  SessionFactory sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
  Session session = sessionFactory.openSession();
  session.beginTransaction();
  
  Query query = session.getNamedQuery("findStudentByRollNumber").setInteger("rollNumber", 3);
  
  List students = query.list();
  for(Student student : students)
  {
   System.out.println(student);
  }
  session.getTransaction().commit();
  session.close();
 }
}

Output:

log4j:WARN No appenders could be found for logger (org.hibernate.cfg.annotations.Version).

log4j:WARN Please initialize the log4j system properly.

Hibernate: select student0_.ID as ID0_, student0_.COURSE as COURSE0_, student0_.ROLL_NUMBER as ROLL3_0_, student0_.STUDENT_NAME as STUDENT4_0_ from STUDENT student0_ where student0_.ROLL_NUMBER=?
ROLL Number: 3| Name: Adesh Rajput| Course: MA

In the Next Chapter we will discuss about the Criteria Query.

<<Previous Chapter 28<< >>N ext Chapter 30>>

View Comments

farhan says:

December 15, 2017 at 12:09 pm

This explanation is also good like others but request you to please add one more example of using native query in code in your previous topic.
- Dinesh Rajput says:
  
  December 15, 2017 at 10:01 pm
  
  Thanks Farhan for learning with us. https://www.dineshonjava.com/hibernate-3-on-baby-steps/