JPQL is a query language used for searching and updating the database and for using the database functionality such as the set function. While the SQL is a query language using a table as the target, JPQL is a query language defined in the JPA specifications using an entity class as the target.
You can define a query in an annotation or the O/R mapping file. If an entity is defined in the same persistence unit as the query, you can use the abstract schema type expressing the entity set in the query. Also, if you use the path expression, you can use a query across the relationship defined in the persistence unit. For details on the path expression, see 6.17.4(2) Path expression.
If you code and execute JPQL in an application, the SQL statements are issued for the database to be connected to in the following order:
This subsection describes how to use JPQL.
To use JPQL for obtaining the Query object, use the following methods of the EntityManager interface provided by Cosminexus JPA Provider. This subsection describes the methods of the EntityManager interface.
An example of coding createQuery is as follows. In the argument, specify the JPQL statement you want to execute.
Query q = em.createQuery( |
A query that can be given a name and defined in advance is called a named query. You define a named query by assigning @NamedQuery in any entity class. Specify the query name in the name attribute of @NamedQuery and then specify the JPQL statement in the query attribute.
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 KDJE55535-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 defining @NamedQuery. In this example, @NamedQuery is used and the query is registered beforehand with the name findAllCustomersWithName. By passing the named query name registered in the createNamedQuery method of the application, the query registered beforehand is obtained and used.
@NamedQuery( |
@Stateless |
Note that with the same persistence unit, you can also use the named query defined in another entity.
When you generate a query with JPQL, you can use a parameter in the conditional expression coded in the WHERE clause and set the value dynamically. Set the parameter value with the setParameter method of the Query interface. The parameters include location parameters and named parameters. The following is a description of each parameter:
Query setParameter(int location, Object value) |
Query q = em.createQuery( |
Query setParameter(String parameter-name, Object value) |
Query q = em.createQuery( |
Note the following when you use parameters:
You use the following methods of the Query interface to execute the generated query, to return the query results, and to execute update query. The following points describe each method:
Use this method to return the query result as a single object.
When you execute this method, the data is searched. As a result of the search, the single hit line is stored in the entity object and returned with the Object type. The return value of the Object type must be cast in the target entity class.
If multiple lines are hit, the NonUniqueResultException exception occurs. If no lines are hit, the NoResultException exception occurs.
Use this method to return the query result as a list.
When you execute this method, the data is searched. As a result of the search, the multiple hit lines are stored in the entity object and returned in a list. Multiple lines are assumed to be returned as execution results; therefore, if no lines are hit, an empty list is returned.
Use this method to execute the update query.
When you execute this method, a query will be executed to simultaneously delete or update multiple lines in a table. The execution result returns the number of lines hit.