MASTERTHEBOSS.COM

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:

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>

@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;

}

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;

}