Rules for data type manipulation in EJB query
When using an EJB query to work with data types, certain rules must be followed.
We can use a CMP field of any type in a SELECT clause. We must, however, use fields of only the following types in search conditions and in grouping or ordering operations:
- Primitive types: byte, short, int, long, float, double, boolean, char
- Object types: Byte, Short, Integer, Long, Float, Double, BigDecimal, String, Boolean, Character, java.util.Calendar, java.util.Date
- JDBC types: java.sql.Date, java.sql.Time, java.sql.Timestamp
- Binary string: byte
If ALL of the following conditions occur:
- a CMP field of one of the basic types listed previously is mapped to an SQL column using a converter
- the CMP field appears prior to a basic predicate
- following the predicate is a literal or input parameter
then the toData() method of the converter is used to compute the SQL search value.
For example, given a converter that maps the integer value 10 to the string value "Ten" the following EJB query:
e.cmp = 10
is translated into the following SQL query:
column = 'Ten'
If we include a more complicated predicate, such as in the following example:
e.cmp * 10 > e.salary
in a finder or select query, you receive the Cannot push down query error message. Use the dynamic EJB query service for such multi-function queries; the dynamic query run time processes the predicate in the application server.
Overall, converters preserve equality, collating sequence, and NULL values. If a converter does not meet these requirements, avoid using it for CMP field comparison operations.
A user type cannot be used in a comparison operation or expression. We can, however, use subfields of the user type in a path expression. For example, consider the CMP addr field with the type com.exam.Address, and street, city, and state subfields. The following syntax for a query on this CMP field is not valid:
e.addr = ?1
However, a query that designates one of the subfields is valid:
e.addr.street = ?1
A CMP field can be mapped to an SQL column using Java serialization. Using the CMP field in predicates or expressions for deployment queries usually results in the Cannot push down query error message. The dynamic query run time processes the expression by reading and deserializing all instances of the user type in the application server.
However, this expensive process sacrifices performance. We can maintain performance by using a composer in a deployment EJB query. In the previous example, to map the addr field to a binary type, you use a composer to map each subfield to a binary column in the database.
Related concepts
EJB query language EJB query compatibility issues with SQL Database restrictions for EJB query
Related tasks
Use EJB query