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
 
0
0
0
s2smodern