6.16.2 Procedure for referencing and updating the database with the native query

With Cosminexus JPA Provider, as a query language other than JPQL, you can directly code a database-specific native query and reference or update a database.

This subsection explains how to use native queries.

Organization of this subsection
(1) How to obtain the Query object
(2) Result set mapping
(3) How to specify the parameters
(4) Obtaining and executing the native query results

(1) How to obtain the Query object

To use native query for obtaining the Query object, you use the following methods of the EntityManager interface provided by Cosminexus JPA Provider.

(a) Query createNativeQuery(String SQL statement)

An example of coding createNativeQuery is as follows. In the argument, specify the native query you want to execute.

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')");

(b) Query createNativeQuery(String SQL statement, Class result storing class)

An example of coding createNativeQuery is as follows. Specify the native query you want to execute in the first argument and the class object for storing the execution result in the second 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 = 'book')",
                  com.hitachi.Order.class);

In this example, if a query is executed, the collection of all the Order entities is returned for the item named 'book'.

Note that if the query results specified in the SELECT clause and the class object specified in the argument are inconsistent, an exception occurs.

(c) Query createNativeQuery(String SQL statement, String result set mapping name)

Specify the native query you want to execute in the first argument and the result set mapping name for storing the execution result in the second argument. Specify the result set mapping with @ SqlResultSetMapping. For details on result set mapping, see (2) Result set mapping.

An example of defining @SqlResultSetMapping and an example of coding createNativeQuery are as follows:

In this example, if a query is executed, the collection of all the Order entities is returned for the item named 'book'. By using @SqlResultSetMapping, you can obtain the same results as the coding example when @NamedQuery is used in 6.16.1(1) How to obtain the Query object.

Note that if the query results specified in the SELECT clause and the @SqlResultSetMapping settings specified in the argument are inconsistent, an exception occurs.

(d) Query createNamedQuery(String query name)

Like JPQL, you can use the createNamedQuery method for a native query. In the native query, specify the named native query name in the argument.

You define the named native query by assigning @NamedNativeQuery in any entity class. In the query name argument, use the name specified in the name attribute of @NamedNativeQuery.

In Cosminexus JPA Provider, you cannot specify multiple named queries with the same name. If multiple named queries with the same name are specified, a warning message KDJE55522-W is output. If such multiple named queries with the same name are specified in Cosminexus JPA Provider, there is no certainty about which query will be operated.

The following is an example of using the createNamedQuery method. In this example, @NamedNativeQuery is used and the query is registered beforehand with the name findBookOrder. By passing the named query name registered in the createNamedQuery method of the application, the query registered beforehand is obtained and used.

Note that with the same persistence unit, you can also use the named native query defined in another entity.

(2) Result set mapping

Result set mapping is a functionality used for mapping and receiving the execution results of the native query in any entity class and for receiving the execution results of the native query using the scalar value.

With result set mapping, the mapping information of the column values obtained as the execution results of the native query is assigned to any entity class by specifying @SqlResultSetMapping.

(a) Coding format of @SqlResultSetMapping

The coding format of @SqlResultSetMapping is as follows:

@SqlResultSetMapping(
 name= Result-set-mapping-name,
 entities= Specify-the-entity-class-for-mapping-the-result-(@EntityResult-array),
 columns= Specify-the-column-for-mapping-the-result-(@ColumnResult-array) )

name attribute
Specify the result set mapping name.
entities attribute
Specify the @EntityResult array. The coding format of @EntityResult is as follows:

@EntityResult(
   entityClass= Specify-the-class-for-mapping-the-result,
   fields= Specify-the-field-for-mapping-the-result-(@FieldResult-array) )

In the entityClass attribute of @EntityResult, specify the entity class for storing the column value. In the field attribute, specify the @FieldResult array.
The coding format of @EntityResult is as follows:

@FiledResult(
   name= Name-of-persistent-property-(or-field)-of-class,
   column= Column-name-of-SELECT-clause-(or-optional-name) )

In the name attribute of @FieldResult, specify the persistence field name of the entity class specified in the entityClass attribute of @EntityResult. Also, in the column attribute, specify the column name.
columns attribute
The columns attribute specifies the @ColumnResult array to receive the execution results of the native query as a scalar value without being stored in the entity class. If you do not need to extract the scalar value, you need not specify the columns attribute. In the name attribute of @ColumnResult, specify the column name for extracting the value. The coding format of @ColumnResult is as follows:

@ColumnResult(
   name= Column-name-of-SELECT-clause-(or-optional-name) )

Note that you can also specify the column name with the alias name specified by AS. If the SELECT clause contains multiple columns with the same name, use the optional name of the column.
(b) Example of usage

In the following example, the query result for employee number 12003 is mapped from the employee table (Employee) and department table (Department) to any entity class (EmployeeSetmap) and the scalar value (EMP_MONTHLY_SALARY column) is received.

Specify the result set mapping name (NativeQuerySetMap) in the second argument of createNativeQuery and execute the result set mapping.

Note that the Object type array for the result of executing the native query by executing @SqlResultSetMapping is as follows:

Object[0]
Object of EmployeeSetmap class
(Values of EMP_EMPLOYEE_ID column, EMP_EMPLOYEE_NAME column, and DEP_DEPARTMENT_NAME column are stored in each field)
Object[1]
Value of EMP_MONTHLY_SALARY column

(3) How to specify the parameters

As in the case of JPQL, the native query can set a value dynamically by using parameters. Code a combination of '?' (question mark) and a numeric value in the location where you want to insert the parameter in the WHERE clause. Specify the parameter value with the setParameter method of the Query interface. However, you cannot use the named parameters of JPQL with the native query.

The coding format of the parameters is as follows:

Query setParameter(int location, Object value)

(4) Obtaining and executing the native query results

As in the case of JPQL, use the following Query interface methods for obtaining and executing the native query results:

For details on these methods, see 6.16.1 Procedure for referencing and updating the database with JPQL.