Using native Queries with Hibernate and EJB 3

Written by F.Marchioni

What are your options if your Hibernate/EJB application has to deal with a very complex and DB-optimized SQL query ? good interview question....

Hibernate has a built-in object oriented language for querying objects called HQL. This language is quite complete and efficient, so in theory you could translate every SQL query in its equivalent HQL ( and provide the necessary POJOs and configuration files).

However the cost of translating very complex queries can be excessive (including the time spent to build all POJOs which are involved in the query). You might be very smart with HQL but it's still very difficult that you can achieve the same performance of a Query that a DBA spent hours in order to optimize it.

This can be especially true if you are migrating an older JDBC application which has already a stable and tuned list of queries.

In these situations it is convenient to use the native SQL for creating Entity Queries.

You have at least two options :

Create a view which wraps the SQL:

Supposing you have the following query: (not so complex at all but just to show an SQL that is understandable by all developers)

SELECT ename, job, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno (+) = dept.deptno AND job (+) = 'CLERK';

which produces this resultset:

ENAME	JOB	DEPTNO	DNAME
MILLER	CLERK	10	ACCOUNTING
SMITH	CLERK	20	RESEARCH
ADAMS	CLERK	20	RESEARCH
JAMES	CLERK	30	SALES

Then you could wrap the select with a view:

CREATE VIEW Account as
SELECT ename, job, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno (+) = dept.deptno AND job (+) = 'CLERK';

Now you can map your hibernate mapping file or Entity EJB pointing to the Account view:
Hibernate Mapping:

<hibernate-mapping>
    <class name="com.sample.Account" table="Account">
        <id name="ename" type="java.lang.String">
            <column name="ename"  />
            <generator class="native" />
        </id>
        <property name="departmentNumber" type="java.lang.Long">
            <column name="deptno"  />
        </property>
        <property name="jobName" type="java.lang.String">
            <column name="job"  />
        </property>
        <property name="departmentName" type="java.lang.String">
            <column name="dname"  />
        </property>
</hibernate-mapping>

EJB 3 Mapping:

@Entity
@Table(name="Account")

public Class Account {
@Id
@Column(name="ENAME")
String ename;

@Column(name="DEPTNO")
Long departmentNumber;

@Column(name="JOB")
String jobName;

@Column(name="DNAME")
String departmentName;

}

PRO	The fastest solution, if you can generate automatically POJOs and XML files from the DataBase View using Hibernate Tools
CONTROS	If the fields of the query change, you have modify both Java configuration and Database View. When going to production you must deliver DB script for adding DB Views.

Use Hibernate / EJB 3 native queries

Sometimes creating a view it's not just possible because the SQL contains for example binding parameters so you have to use Hibernate / EJB 3 native queries.

The simplest way to create a native Query with Hibernate is using scalars (Objects[])

List list =
session.createSQLQuery("SELECT ename, job, dept.deptno, dname "
+ "FROM emp, dept "
+ "WHERE emp.deptno (+) = dept.deptno AND job (+) = 'CLERK'").list();

In this case you have your Entity returned as Object[].

You can bind your native SQL to an Entity by adding invoking the addEntity method
on the SQLQuery object:

query = session.createSQLQuery("SELECT ename, job, dept.deptno, dname "
+ "FROM emp, dept "
+ "WHERE emp.deptno (+) = dept.deptno AND job (+) = 'CLERK'").addEntity(Account.class);

List <Account> list = query.list();

In this case you return a typed Class (Account) so you need to add to your Hibernate configuration the Account.hbm.xml

What is important to know is that in your Account.hbm.xml, you don't need to reference any table. Actually the Account table doesn't exist , it is just an SQL query transformed into a Java Class. (Quite powerful isn't it ?)

<hibernate-mapping>
    <class name="com.sample.Account"  >
        <id name="ename" type="java.lang.String">
            <column name="ename"  />
            <generator class="native" />
        </id>
        <property name="departmentNumber" type="java.lang.Long">
            <column name="deptno"  />
        </property>
        <property name="jobName" type="java.lang.String">
            <column name="job"  />
        </property>
        <property name="departmentName" type="java.lang.String">
            <column name="dname"  />
        </property>
</hibernate-mapping>

The EJB 3 equivalent is:

Query query = entityManager.createNativeQuery("SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno AND job (+) = 'CLERK'",Account.class);

List <Account> list = query.list();

The same consideration is valid also for the Entity Class, that is you don't have to annotate the corresponding table name:

@Entity

public Class Account {
@Id
@Column(name="ENAME")
String ename;

@Column(name="DEPTNO")
Long departmentNumber;

@Column(name="JOB")
String jobName;

@Column(name="DNAME")
String departmentName;

}

PRO	Pure EJB 3 / Hibernate solution. Doesn't impact the DB. If fields of the query change you have to update just Hibernate files and POJOs.
CONTROS	Since the result of the complex query doesn't reflect in a phisical Table/View, you can't use Hibernate tools to generate Hibernate/EJB artifacts.

JBoss.org Search

Custom Search

Comments

Only registered users can write comments!

How to avoid the 50 seconds start up limit in Eclipse ?

How can you solve deployment errors caused by large war/jar/ear files ?

How do you configure your .war to be deployed after your EJB ?

3.26 Copyright (C) 2008 Compojoom.com / Copyright (C) 2007 Alain Georgette / Copyright (C) 2006 Frantisek Hliva. All rights reserved."