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.
To use native query for obtaining the Query object, you use the following methods of the EntityManager interface provided by Cosminexus JPA Provider.
An example of coding createNativeQuery is as follows. In the argument, specify the native query you want to execute.
Query q = em.createNativeQuery( |
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( |
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.
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:
@SqlResultSetMapping(name="BookOrderResults", |
Query q = em.createNativeQuery( |
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.
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.
@NamedNativeQuery( name="findBookOrder", |
@Stateless |
Note that with the same persistence unit, you can also use the named native query defined in another entity.
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.
The coding format of @SqlResultSetMapping is as follows:
@SqlResultSetMapping( |
@EntityResult( |
@FiledResult( |
@ColumnResult( |
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.
query = em.createNativeQuery( |
@Entity |
@SqlResultSetMapping( |
Note that the Object type array for the result of executing the native query by executing @SqlResultSetMapping is as follows:
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) |
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.