Native Queries with Hibernate Annotations

Hibernate EntityManager implements the programming interfaces and lifecycle rules as defined by the EJB3 persistence specification. Together with Hibernate Annotations, this wrapper implements a complete (and standalone) EJB3 persistence solution on top of the mature Hibernate core. In this post I will describe how map native queries (plain SQL) using Hibernate Annotations. Hibernate Annotations supports the use of Native queries through the @NamedNativeQuery and the @SqlResultSetMapping annotations.

• @NamedNativeQuery: Specifies a native SQL named query.
• @SqlResultSetMapping: Used to specify the mapping of the result of a native SQL query.

You will not need any EJB container support. At a minimum, you will need Hibernate core and Hibernate Annotations. The entire list of required Jar files is provided at the end.

1. The Hibernate Configuration File: Nothing new here. Except that there are no mappings defined. I used programmatic declaration of mapping for this example as shown in the following steps.

   <!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="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
     <property name="connection.url">jdbc:oracle:thin:@localhost:1521/orcl</property>
     <property name="connection.username">scott</property>
     <property name="connection.password">tiger</property>
     <property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
     <property name="hibernate.current_session_context_class">thread</property>
    </session-factory>
   </hibernate-configuration>

   hibernate.cfg.xml
2. The Entity class:

   package data;
   
   import javax.persistence.Column;
   import javax.persistence.Entity;
   import javax.persistence.EntityResult;
   import javax.persistence.FieldResult;
   import javax.persistence.Id;
   import javax.persistence.NamedNativeQuery;
   import javax.persistence.SqlResultSetMapping;
   
   @Entity
   @SqlResultSetMapping(name = "implicit", entities = @EntityResult(entityClass = data.Employee.class))
   @NamedNativeQuery(name = "implicitSample", query = "select e.empno empNumber, e.ename empName, e.job empJob, e.sal empSalary, salg.grade empGrade from emp e, salgrade salg where e.sal between salg.losal and salg.HISAL", resultSetMapping = "implicit")
   //@SqlResultSetMapping(name = "explicit", entities = { @EntityResult(entityClass = data.Employee.class, fields = { 
   //    @FieldResult(name = "empNumber", column = "empno"), 
   //    @FieldResult(name = "empName", column = "ename"),
   //    @FieldResult(name = "empJob", column = "job"), 
   //    @FieldResult(name = "empSalary", column = "sal"), 
   //    @FieldResult(name = "empGrade", column = "grade") }) })
   //@NamedNativeQuery(name = "implicitSample", 
   //    query = "select e.empno empno, e.ename ename, e.job job, e.sal sal, salg.grade grade from emp e, salgrade salg where e.sal between salg.losal and salg.HISAL", resultSetMapping = "explicit")
   public class Employee {
   
    private String empNumber;
   
    private String empName;
   
    private String empJob;
   
    private Double empSalary;
   
    private int empGrade;
   
    @Column
    @Id
    public int getEmpGrade() {
      return empGrade;
    }
   
    public void setEmpGrade(int empGrade) {
      this.empGrade = empGrade;
    }
   
    @Column
    public String getEmpJob() {
      return empJob;
    }
   
    public void setEmpJob(String empJob) {
      this.empJob = empJob;
    }
   
    @Column
    public String getEmpName() {
      return empName;
    }
   
    public void setEmpName(String empName) {
      this.empName = empName;
    }
   
    @Column
    public String getEmpNumber() {
      return empNumber;
    }
   
    public void setEmpNumber(String empNumber) {
      this.empNumber = empNumber;
    }
   
    @Column
    public Double getEmpSalary() {
      return empSalary;
    }
   
    public void setEmpSalary(Double empSalary) {
      this.empSalary = empSalary;
    }
   
   }

   Employee.java
   • The implitic mapping (the uncommented @NamedNativeQuery and @SqlResultSetMapping declarations are used for implicitly mapping the ResultSet to the entity class. Note that the SQL column names match the field names in the class. If the names do not match then you can set the name attribute of the @Column annotation to the column name in the query.
   • The commented @NamedNativeQuery and the @SqlResultSetMapping declarations explicitly map the fields to the columns. This will come in handy when using joins and composite keys etc.
   • Note the package definitions refer to javax.persistence and not the hibernate packages. If the packages are not declared properly, you will most likely end up with some exceptions like the following

     org.hibernate.hql.ast.QuerySyntaxException: Employee is not mapped.

     While there are other causes for this exception, the package declarations did cause a little trouble for me.
3. The Client:

   import java.util.List;
   
   import org.hibernate.Session;
   import org.hibernate.SessionFactory;
   import org.hibernate.cfg.AnnotationConfiguration;
   import org.hibernate.cfg.Configuration;
   
   import data.Employee;
   
   public class Client {
   
    public static void main(String[] args) {
      Configuration config = new AnnotationConfiguration().addAnnotatedClass(Employee.class).configure();
   
      SessionFactory sessionFactory = config.buildSessionFactory();
      Session session = sessionFactory.getCurrentSession();
   
      List result = null;
      try {
        session.beginTransaction();
   
        result = session.getNamedQuery("implicitSample").list();
        System.out.println("Result size : " + result.size());
        session.getTransaction().commit();
      } catch (Exception e) {
        e.printStackTrace();
      }
      System.out.println(result.size());
    }
   
   }

   Client.java
4. Jar Files: The following jar files need to be included in the classpath

   hibernate3.jar
   commons-collections-2.1.1.jar
   antlr-2.7.6.jar
   commons-logging-1.0.4.jar
   hibernate-annotations.jar
   ejb3-persistence.jar
   hibernate-commons-annotations.jar
   dom4j-1.6.1.jar
   ojdbc14.jar
   jta.jar
   log4j-1.2.11.jar
   xerces-2.6.2.jar
   xml-apis.jar
   cglib-2.1.3.jar
   asm.jar

   All the Jar files will be available in the hibernate download. The hibernate-annotations.jar file is available in the hibernate annotations download.

This example was tested on Java 5 Update 9 with Hibernate version 3.2.3 and Hibernate Annotations Version: 3.3.0.GA.