Native Queries

Native Queries

EJB QL is a very rich syntax and should meet most of your querying needs. Sometimes, though, you want to take advantage of certain proprietary capabilities that are available only on a specific vendor's database.

The entity manager service provides a way to create native SQL queries and map them to your objects. Native queries can return entities, column values, or a combination of the two. The EntityManager interface has three methods for creating native queries: one for returning scalar values, one for returning one entity type, and one for defining a complex result set that can map to a mix of multiple entities and scalar values.

You can always get the underlying JDBC connection through a javax.sql.DataSource injected by the @Resource and execute any SQL statement you need. Be aware that your changes will not be reflected in the current persistence context.

Scalar Native Queries

Query createNativeQuery(String sql)

This creates a native query that returns scalar results. It takes one parameter: your native SQL. It executes as is and returns the result set in the same form as EJB QL returns scalar values.

Simple Entity Native Queries

Query createNativeQuery(String sql, Class entityClass) 

A simple entity native query takes an SQL statement and implicitly maps it to one entity based on the mapping metadata you declared for that entity. It expects that the columns returned in the result set of the native query will match perfectly with the entity's O/R mapping. The entity the native SQL query maps to is determined by the entityClass parameter:

Query query = manager.createNativeQuery(
    "SELECT p.phone_PK, p.phone_number, p.type
        FROM PHONE AS p", Phone.class
);

All the properties of the entities must be pulled.

Complex Native Queries

Query createNativeQuery(String sql, String mappingName) 

This entity manager method allows you to have complex mappings for your native SQL. You can return multiple entities and scalar column values at the same time. The mappingName parameter references a declared @javax.persistence.SqlResultSetMapping . This annotation is used to define how the native SQL results hook back into your O/R model. If your returned column names don't match the parallel annotated property mapping, you can provide a field-to-column mapping for them using @javax.persistence.FieldResult :

package javax.persistence;

public @interface SqlResultSetMapping {
   String name( );
   EntityResult[] entities( ) default {};
   ColumnResult[] columns( ) default {};
}

public @interface EntityResult {
   Class entityClass( );
   FieldResult[] fields( ) default {};
   String discriminatorColumn( ) default "";
}

public @interface FieldResult {
   String name( );
   String column( );
}

public @interface ColumnResult {
   String name( );
}

Let's do a series of examples to show how this would work.

Native queries with multiple entities

First, let's create a native query that returns multiple entity types: a Customer and its Credit Card:

@Entity
@SqlResultSetMapping(name="customerAndCreditCardMapping",
      entities={@EntityResult(entityClass=Customer.class),
                @EntityResult(entityClass=CreditCard.class,
                              fields={@FieldResult(name="id",
                                                   column="CC_ID"),
                                      @FieldResult(name="number",
                                                   column="number")}
                              )})
public class Customer {...}

// execution code
{
   Query query = manager.createNativeQuery(
       "SELECT c.id, c.firstName, cc.id As CC_ID, 
 cc.number" +
               "FROM CUST_TABLE c, CREDIT_CARD_TABLE cc" +
               "WHERE c.credit_card_id = cc.id",
       "customerAndCreditCardMapping");
}

Because the result set returns multiple entity types, we must define an @SqlResultSetMapping . This annotation can be placed on an entity class or method. The entities( ) attribute is set to an array of @EntityResult annotations. Each @EntityResult annotation specifies the entities that will be returned by the native SQL query.

The @javax.persistence.FieldResult annotation is used to explicitly map columns in the query with properties of an entity. The name( ) attribute of @FieldResult identifies the entity bean's property, and the column( ) attribute identifies the result set column returned by the native query.

In this example, we do not need to specify any @FieldResults for Customer, as the native query pulls in each column for this entity. However, since we are only querying the ID and number columns of the CreditCard entity, an @FieldResult annotation should be specified. In the @EntityResult annotation for CreditCard, the fields( ) attribute defines what CreditCard properties each queried column maps to. Because the Customer and CreditCard primary-key columns have the same name, the SQL query needs to distinguish that they are different. The cc.id As CC_ID SQL fragment performs this identification.

We can express this in XML as well:

<entity-mappings>
   <sql-result-set-mapping name="customerAndCreditCardMapping">
      <entity-result entity-class="com.titan.domain.Customer"/>
      <entity-result entity-class="com.titan.domain.CreditCard"/>
         <field-result name="id" column="CC_ID"/>
         <field-result name="number" column="number"/>
      </entity-result>
   </sql-result-set-mapping>
</entity-mappings>

Mixed scalar and entity results

For our final example, let's show an entity and scalar value mix. We'll write a native SQL query that returns a list of cruises and how many reservations each cruise has.

@SqlResultSetMapping(name="reservationCount",
          entities=@EntityResult(name="com.titan.domain.Cruise",
                                 fields=@FieldResult(name="id", column="id")),
          columns=@ColumnResult(name="resCount"))
@Entity
public class Cruise {...}

{
   Query query = manager.createNativeQuery(
      "SELECT c.id, count(Reservation.id) as resCount 

       FROM Cruise c LEFT JOIN Reservation ON c.id = Reservation.CRUISE_ID
       GROUP BY c.id",
       "reservationCount"); 

}

The reservationCount mapping declares that the native SQL query represents a request for a Cruise entity and a count of all the reservations for that cruise. The @FieldResult annotation identifies the c.id column as being associated with a Cruise entity. The @ColumnResult annotation identifies the resCount column as a scalar value.

Here's the XML equivalent:

<entity-mappings>
   <sql-result-set-mapping name="reservationCount">
      <entity-result entity-class="com.titan.domain.Cruise">
          <field-result name="id" column="id"/>
      </entity-result>
      <column-result name="resCount"/>
   </sql-result-set-mapping>
</entity-mappings>