Rules for data type manipulation in EJB query

When using an Enterprise JavaBeans (EJB) query to work with data types, certain rules must be followed.

You can use a CMP field of any type in a SELECT clause. You 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 you 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. You 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. You can maintain performance by using a composer in a deployment EJB query. In the previous example, if you want to map the addr field to a binary type, you use a composer to map each subfield to a binary column in the database.