Workbook 7 delta

Exercise 9.1: Query and EJB QL Basics

To get rid of the warnings for already existing tables, the property toplink.ddl-generation in the persistence.xml file can be commented out on subsequent runs.

By setting the property toplink.logging.level to FINE you can see the SQL statements executed for queries.

The IN Operator and INNER JOIN / Using DISTINCT

For some reason the toplink translation of EJB QL to SQL always inserts the DISTINCT.

The WHERE Clause and IS EMPTY

I do not know why the names are printed in the example run in the book, but I can't find the reason in code.

The WHERE Clause and MEMBER OF

Apparently toplink does not support the query on full entity object as parameter (It does as partial result.), futhermore the query that should have returned some value originally did not, this appears to be a toplink bug, the comparison of the parameter is done against crs instead of against cust. When the order of crs and cust is changed, or a WHERE 0 = 0 clause is introduced before the cust.id = :c the query works.

The NOT MEMBER OF query delivers the same SQL (and result) as the MEMBER OF QUERY, this appears to be very wrong.

GROUP BY and HAVING

Also suffers from the limitation on object level queries (replaced COUNT (res) by COUNT (res.id))

The Result object must accept Long / Double wrappers instead of primitive types.

In case of the HAVING the toplink requires that the HAVING is a search condition over the grouping items or aggregate functions that apply to the grouping item. ( In this case the reservations are not below the name.

Bulk Updates

Nothing special.

Exercise 9.2: Native SQL Queries

By setting the property toplink.logging.level to FINE you can see the SQL statements executed for queries.

Running native SQL is different for different databases, also the issues encountered and the results returned are different. For the platform used in the book the columns maintain case when the SQL query returns, for the oracle database the columns get translated to upper case in the result set, which leads to casing issues if no all upper case was used in explicit column name definitions. Furthermore the count returned in example part 3 is a BigDecimal instead of and Integer.

Using newer Toplink Essentials