9.17.9 Notes on using JPQL

This section describes the notes on using JPQL.

Organization of this subsection

(1) Notes on the null value
(2) Notes on using JPQL in HiRDB

(1) Notes on the null value

null value in the query result
- If the query result value corresponds to a relation field or state field with a null value, that null value is returned as the result of the query method.
- You can use the IS NOT NULL syntax to remove the null value from the query result set.
- In the fields defined using the numeric series primitive type of Java, a null value cannot be generated as a query result.

null value in the comparison and conditional expressions

If the reference target does not exist in the database, the value is assumed to be null. To search a null value, you can only use the comparison conditions IS NULL and IS NOT NULL.
If the null value is used in a condition other than IS NULL and IS NOT NULL and if that result depends on the null value, the result becomes unknown.
The result of the comparison or arithmetic operation of the null value is always an unknown value.
The result of comparing two null values is an unknown value.
The result of the comparison or arithmetic operation with an unknown value is always an unknown value.

The boolean operator uses three-valued-logic. The following table describes the three-valued-logic expressions for AND, OR, and NOT.

Table 9‒23: Three-valued-logic expression for AND (result of A AND B)
A	B
A	True	False	Unknown
`True`	`True`	`False`	`Unknown`
`False`	`False`	`False`	`False`
`Unknown`	`Unknown`	`False`	`Unknown`

Table 9‒24: Three-valued-logic expression for OR (result of A OR B)
A	B
A	True	False	Unknown
`True`	`True`	`True`	`True`
`False`	`True`	`False`	`Unknown`
`Unknown`	`True`	`Unknown`	`Unknown`

Table 9‒25: Three-valued-logic expression for NOT (result of NOT A)
A	Result of NOT A
`True`	`False`
`False`	`True`
`Unknown`	`Unknown`

To Page Top

(2) Notes on using JPQL in HiRDB

You cannot specify location parameters and named parameters in the arguments of the following JPQL functions:

TRIM, SQRT, ABS, LENGTH, LOWER, MOD, LOCATE, UPPER, CONCAT, SUBSTRING, IS [NOT] NULL

If location parameters and named parameters are specified in the arguments of these functions, the operations might not function properly. Note that if the location parameters and named parameters are specified, a HiRDB SQL syntax error occurs and the PersistenceException exception containing the SQLException exception might be thrown.

If you want to use the functionality of these functions, use the native query.
You cannot specify the question mark (?) parameter on both sides of the four arithmetic operators (+, -, *, /) and both sides of the comparison operators (=, >, >=, <, <=, <>). Also, you cannot specify the question mark (?) parameter on one side of the comparison operators and a literal on the other side. If such a value is specified, the operations might not function properly. If such a value is specified, a HiRDB SQL syntax error occurs and the PersistenceException exception containing the SQLException exception might be thrown.

Instead of executing the four arithmetic operations and comparison operations in JPQL, execute the four arithmetic operations and comparison operations before using JPQL and then use JPQL.

The examples of coding queries that cannot be used when JPQL is used in HiRDB are as follows:

Example 1 of query that cannot be used: Specifying a location parameter in the function argument

Query query1 = em.createQuery(
"SELECT  o FROM TestEntity o "+
                "WHERE o.name=TRIM(LEADING FROM ?1)")
              .setParameter(1, "  HitachiTaro");

Example 2 of query that cannot be used: Specifying a location parameter in the four arithmetic operators

int no_A=2;
int no_B=4;
Query query2 = em.createQuery(
"SELECT  o FROM TestEntity o WHERE o.id = ?1 + ?2")
               .setParameter(1, no_A)
               .setParameter(2, no_B);

Example 3 of query that cannot be used: Specifying a location parameter in the comparison operators

int cmp_no=3;
Query query3 = em.createQuery(
"SELECT  o FROM TestEntity o WHERE o.id = ?1 AND ?1 < 9")
               .setParameter(1, cmp_no);

To Page Top