9.16.1 Procedure for referencing and updating the database with JPQL

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 9.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:

If JPQL is executed, Cosminexus JPA Provider interprets the JPQL contents.
Based on the annotation and O/R mapping file information coded in the target entity class, JPQL is set up in the SQL statements specific to the database product to be connected to and then issued.

This section describes how to use JPQL.

Organization of this subsection

(1) How to obtain the Query object
(2) How to specify the parameters
(3) Obtaining and executing the query results

(1) How to obtain the Query object

To use JPQL for obtaining the Query object, use the following methods of the EntityManager interface provided by Cosminexus JPA Provider. This section describes the methods of the EntityManager interface.

(a) Query createQuery(String JPQL statement)

An example of coding createQuery is as follows. In the argument, specify the JPQL statement you want to execute.

Query q = em.createQuery(
                   "SELECT c  " +
                   "FROM Customer c " +
                   " WHERE c.name LIKE "Smith");

(b) Query createNamedQuery(String query name)

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.

Registering the query name in @NamedQuery

@NamedQuery(
      name="findAllCustomersWithName",
      query="SELECT c FROM Customer c WHERE c.name LIKE :custName"
)
@Entity
public class Customer {
...
}

Example of coding the named query with the createNamedQuery method

@Stateless
public class MySessionBean {
...
  @PersistenceContext
  public EntityManager em;
  ...
  public void doSomething() {
    ...
    Query q = em.createNamedQuery("findAllCustomersWithName") 
                                     .setParameter("custName", "Smith");
  }
}

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

To Page Top

(2) How to specify the parameters

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:

Location 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. The format and example of coding the location parameters is as follows:

Coding format

Query setParameter(int location, Object value)

Coding example

Query q = em.createQuery(
        "SELECT c FROM Customer c WHERE c.balance < ?1")
.setParameter(1, 20000);

Named parameters

Code a combination of ':' (colon) and any string (however, excluding the characters 0 to 9) 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. The format and example of coding the named parameters is as follows:

Coding format
```
Query setParameter(String parameter-name, Object value)
```
Do not specify ':' (colon) at the beginning of the parameter name. Also, the named parameters are case sensitive.

Coding example

Query q = em.createQuery(
        "SELECT c FROM Customer c WHERE c.name LIKE :custName")
        .setParameter("custName", "John");

Note the following when you use parameters:

Do not mix and use the location parameters and named parameters in one query. In Cosminexus JPA Provider, the operations might not function properly if the parameters are mixed.
In the setParameter method, specify as follows when you want to specify the java.util.Date type or java.util.Calendar type objects as parameter values. Note that the time type of the parameter value must be specified using the enumeration type TemporalType. For details, see the Java documentation.
- Query setParameter(int location, Date date, TemporalType time-type)
- Query setParameter(String parameter-name, Date date, TemporalType time-type)
- Query setParameter(int location, Calendar calendar, TemporalType time-type)
- Query setParameter(String parameter-name, Calendar calendar, TemporalType time-type)

To Page Top

(3) Obtaining and executing the query results

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:

(a) Object getSingleResult()

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.

(b) List getResultList()

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.

(c) int executeUpdate()

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.

To Page Top