Java Persistence Query Language

Native Query in Java Persistence Query Language

Queries may be expressed in native SQL. The result of a native query may
consist of entities, scalar values, or a combination of two.

Native query is intended to provide support for those cases where it is
necessary to use the native SQL of the target database in use. Native
queries are not expected to be portable across databases.
When multiple entites are returned by a native query, the entities must
be specified and mapped to the column results of the SQL statement in a
SqlResultSetMapping metadata definition. This result set mapping
metadata can then be used by the persistence runtime to map the JDBC
results into the expected objects.

The use of named parameters is not defined for native queries. Only
positional parameter binding for

SQL queries may be used by portable applications.

Support for joins is currently limited to single-valued relationships.



Below is several examples to show how to define SqlResultSetMapping
metadata acoording to the result return type and entity class.

I. Native query that returns entities of a single entity class

If the results of the query are limited to entites of a single entity
class, a simpler form may be used and SqlResultSetMapping metadata is
not required.

This example in which a native SQL query returns entites of a
single entity class does not use SqlResultSetMapping and the entity class that specifies the type of the
result is passed in as an argument.

 Query q = em.createNativeQuery(

   "SELECT o.id, o.quantity, o.item " +

   "FROM Order o, Item i " +

   "WHERE (o.item = i.id) AND (i.name = 'widget')",

   com.acme.Order.class);

 List orders = q.getResultList();

When executed, this query will return a Collection of all Order entites
for items named "widget".

II. Native query that returns mutiple entity types

The following query and SqlResultSetMapping metadata illustrates the
return of multiple entity types and assumes default metadata and column
name defaults.

 @SqlResultSetMapping(name="OrderItemResults",

   entities={

       @EntityResult(entityClass=com.acme.Order.class),

       @EntityResutl(entityClass=com.acme.Item.class)

   }

 )

 @Entity

 public class Employee {

 ...

 }

Note that the SqlResultSetMapping is always defined before entity class definition.
And the following is the execution of native query.
 Query q = em.createNativeQuery(

   "SELECT o.id, o.quantity, o.item, i.id, i.name, i.description " +

   "FROM Order o, Item i " +

   "WHERE (o.quantity > 25) AND (o.item = i.id)",

   "OrderItemResults");

 List result = q.getResultList();


 int s = result.size();

 for(int i=0; i<s; i++){

      Object obj = result.get(i);

      Object[] objectArray = (Object[]) obj;

      Object object1 = objectArray[0];

      Object object2 = objectArray[1];

      Order order = (Order) object1;

      Item item = (Item)object2;

 }

When an entity is being returned, the SQL statement should select all of
the columns that are mapped to the entity object. This should include
foreign key columns to related entities. Note that column aliases must
be used in the SQL SELECT clause where the SQL result would otherwise
contain multiple columns of the same name.

III. Native query that includes aliases in SQL statement

An example of combining multiple entity types and that includes aliases
in the SQL statement requires that the column names be explicitly mapped
to the entity fields. The FieldResult annotation is used for this purpose.


 

Query q = em.createNativeQuery(

     "SELECT o.id AS order_id, " +


     "o.quantity AS order_quantity, " +


    
"o.item AS order_item, " +


    
"i.id, i.name, i.description " +


    
"FROM Order o, Item i " +


    
"WHERE (order_quantity > 25) AND (order_item = i.id)",


    
"OrderItemResults");

 @SqlResultSetMapping(name="OrderItemResults",


    entities={


     @EntityResult(entityClass=com.acme.Order.class, fields={


       
@FieldResult(name="id", column="order_id"),


       
@FieldResult(name="quantity", column="order_quantity"),


       
@FieldResult(name="item", column="order_item")}),


    
@EntityResult(entityClass=com.acme.Item.class)


   
}

 )
 

IV. Native query that has scalar result types in query result

Scalar result types can be included in the query result by specifying
the ColumnResult annotation in

the metadata.


 Query q = em.createNativeQuery(


    
"SELECT o.id AS order_id, " +


    
"o.quantity AS order_quantity, " +


    
"o.item AS order_item, " +


    
"i.name AS item_name, " +


    
"FROM Order o, Item i " +


    
"WHERE (order_quantity > 25) AND (order_item = i.id)",


    
"OrderResults");


 
@SqlResultSetMapping(name="OrderResults",


    entities={


     
@EntityResult(entityClass=com.acme.Order.class, fields={


     
@FieldResult(name="id", column="order_id"),


     
@FieldResult(name="quantity", column="order_quantity"),


     
@FieldResult(name="item", column="order_item")})},


    columns={


     
@ColumnResult(name="item_name")}


 
)

V. Entity class that has composite foreign key

When the returned entity type is the owner of a single-valued
relationship and the foreign key is a composite
foreign key (composed of multiple columns), a FieldColumn element should
be used for each
of the foreign key columns. The FieldColumn element must use a dot (".")
notation form to indicate
which column maps to each property or field of the target entity primary
key. The dot-notation form
described below is not required to be supported for any usage other than
for composite foreign keys.



If the target entity has a primary key of type IdClass, this
specification takes the form of the name of

the field or property for the relationship, followed by a dot ("."),
followed by the name of the field or

property of the primary key in the target entity.

Example:


 Query q = em.createNativeQuery(


    
"SELECT o.id AS order_id, " +


    
"o.quantity AS order_quantity, " +


    
"o.item_id AS order_item_id, " +


    
"o.item_name AS order_item_name, " +


    
"i.id, i.name, i.description " +


    
"FROM Order o, Item i " +


    
"WHERE (order_quantity > 25) AND (order_item_id = i.id) AND


    
(order_item_name = i.name)",


    
"OrderItemResults");


 
@SqlResultSetMapping(name="OrderItemResults",


    
entities={


      
@EntityResult(entityClass=com.acme.Order.class, fields={


         
@FieldResult(name="id", column="order_id"),


         
@FieldResult(name="quantity", column="order_quantity"),


         
@FieldResult(name="item.id", column="order_item_id")}),


          @FieldResult(name="item.name", column="order_item_name")}),


      @EntityResult(entityClass=com.acme.Item.class)


    
}

 )

VI. Entity class that has a primary key if type EmbeddedId

If the target entity has a primary key of type EmbeddedId, this
specification is composed of the name
of the field or property for the relationship, followed by a dot ("."),
followed by the name or the field or
property of the primary key (i.e., the name of the field or property
annotated as EmbeddedId), followed
by the name of the corresponding field or property of the embedded
primary key class.

Example:


 String sqlString="SELECT e.FNAME AS EMP_FNAME, " +

     "e.LNAME AS EMP_LNAME, " +

     "e.FK_DEPTNO AS EMP_DEPT_DNO, "+

     "e.FK_DEPTNAME AS EMP_DEPT_DNAME, "+

     "d.DESCRIPTION AS DEPT_DESC "+

     "FROM DEPARTMENTENTITY d, EMPLOYEEENTITY e "+

     "WHERE (e.FK_DEPTNO=d.DEPTNO) AND (e.FK_DEPTNAME = d.DEPTNAME)";

 @SqlResultSetMapping(name="EmployeeDepartmentResults",

    entities={

       @EntityResult(entityClass=pe.ejb.ejb30.entity.ejb.Employee.class, fields={

           @FieldResult(name="employeePk.fname", column="EMP_FNAME"),

           @FieldResult(name="employeePk.lname", column="EMP_LNAME"),

          
@FieldResult(name="department.departmentPk.dno", column="EMP_DEPT_DNO"),

           @FieldResult(name="department.departmentPk.dname",

               column="EMP_DEPT_DNAME")})},

    columns={

       @ColumnResult(name="DEPT_DESC")}

  )
 

The FieldResult elements for the composite foreign key are combined to
form the primary key

EmbeddedId class for the target entity. This may then be used to
subsequently retrieve the entity if

the relationship is to be eagerly loaded.

Posted at 10:26AM Sep 01, 2006 by jielin in Sun  |  Comments[9]