java search
Loading
Help improve Java Search

Tuesday, April 10, 2007

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.

1 comment:

  1. Hi,
    I am naveen Kumar working as a software engineer. Thank you so much for providing me the information on the related technologies witha adetailed example.
    please do continue this series of blogs on various aspects of Java /j2ee technologies.

    ReplyDelete