JPQL and Upper case queries in LIKE

I had a situation where I needed to write a query which would perform upper case comparisons in a LIKE statement. It took me a while to figure this out, mainly because I didn't realize the right-side of the LIKE statement does not support any of the functions. So given a query like this:

  SELECT name FROM Countries WHERE name LIKE '%someValue%';

to write this in a valid JPQL format (assuming we have a persistent bean Country with an attribute name, you would write it out as follows:

  String searchExp = "find me";
  Query qs = entity.createQuery("SELECT c.name FROM Country c WHERE UPPER(c.name) LIKE '%:val%'");
  qs.setParameter("val",searchExp.toUpperCase());

The key here is to convert the right-side in code and then you can use the function UPPER( ) for the left-side of the expression.

This was tested with MySQL and HSQLDB with EclipseLink. There was an article I found here which states that with MySQL 5.1 onward you don't need to use the LOWER function, but I have not verified this yet (nor found the collaborating evidence on MySQL's website). Running against my test system in HSQLDB (in memory) it did not find the results in my unit tests.

HSQLDB/EclipseLink Bug Filed

Well I got around to filing the uniqueness column constraint issue for HSQLDB and EclipseLink. The Bug ID is: 240618.

EclipseLink - Initial Impressions

As was suggested by Doug Clarke, I took a look at EclipseLink. I was actually caught a little off guard on the whole subject, as with the birth of my daughter I had pretty much gone under a rock for the past few months. EclipseLink is yet another JPA provider, but there are some interesting aspects to it:

• It was chosen by Sun Microsystems to serve as the reference implementation for Java Persistence 2.0.
• The JPA development community has essentially switched from TopLink JPA right over to EclipseLink.
• EclipseLink is bringing further capabilities with support for MOXy (JAXB), SDO and OSGI to name a few.

There are several good articles on the tool itself from the EclipseLink home page.

I downloaded the most recent release from the Eclipse website, and set about setting up my applications to use it. Generally speaking, I had tried to make only API calls to the javax.persistence APIs, thus I had very little package dependency changes. Since EclipseLink is based off TopLink virtually all the classes from TopLink appear in EclipseLink under a different naming convention. Basically oracle.toplink.essentials became org.eclipse.persistence. Since I have a nice level of unit testing around my services, I was able to quickly identify the places that were failing. In particular, I had some Query Hints that I was applying if the provider was the TopLink provider. I had to replicate this functionality for EclipseLink code paths (fortunately it was only in a few places). I think the biggest impact was on the Upgrade Tooling in which the SQLSchemaUpdateUtility had package dependencies on TopLink Essentials. Instead I roled this into a StatementExecutor interface/implementation and used reflection to call the APIs. Similarly, I did something similar for EclipseLink if I detected it as the persistence provider.

One observation I have noticed is that EclipseLink takes significantly longer to "initialize" than TopLink Essentials. This is most noticeable with the Unit Tests and HSQLDB, where the execution time is approximately ~ 5.0 seconds different. I brought up my JavaSE application in Netbeans 6.1 using the Profiler, and there is a significant time lag building and initializing the EntityManagerFactory. I am not convinced yet that there isn't a setting I am missing which is causing this lag. For example, launching my JavaSE application (which will query to see if there are any upgrades needed, query for all collections and countries, display the Swing UI etc.), almost 56% of the total startup time was spent in the deploy() method of the EntityManagerSetupImpl.

EclipseLink provides a nice compact javax.persistence_1.0.0.jar file which represents all of the J2EE javax.persistence classes required to compile. This is great for application development, and means you can provide your JPA provider at runtime (along with the persistence.xml configuration). Of course, this assumes you are using non-compile dependencies for things like QueryHints etc.

The documentation for EclipseLink seems a little disorganized, but overall there is a lot of information available through the EclipseLink Wiki User Guide.

I am looking forward to using some of the query features (such as fetches for foreign key objects) and it will take some time to really take advantage of the broader features being offered.

Commentary for my HSQLDB and Uniqueness Post

I figured I should post an update about the HSQLDB issue regarding the JPA "unqiue=true" constraint issue. As was suggested by Doug Clarke, I took a look at EclipseLink and it too suffered from the same issue. I will file a bug sometime this weekend with EclipseLink (Last week their Bugs link was broken). As I was thinking about the solution, I think this could affect more than HSQLDB potentially and therefore I think the way to handle this would be add a new method to org.eclipse.persistence.internal.databaseaccess.DatabasePlatform named boolean shouldUniqueKeyBeHandledAsConstraint() and then the code I mentioned earlier could be genericized further with the HSQLPlatform class implementing the aforementioned method with a value of true.

HSQLDB and Toplink : Uniqueness Constraints

After some effort, I was able to figure out a way to place the UNIQUE constraint from the @Column definition on an JPA entity bean, and have it handled properly with the Toplink JPA. I was actually surprised that even with build 40 or Toplink Essentials v2.1 this was still an issue. The problem was, if you defined a value of unique=true in your JPA column annotation, Toplink Essentials would insert the UNIQUE keyword in the create table routine. This would break on HSQLDB, which does not support this keyword during column descriptor creation. The challenge was to fool Toplink into handling the unique attributes like contraints which it would add after the table was created with an ALTER TABLE sequence.

The route I have chosen for now to solve this for my unit testing needs was to provide replacement Toplink-Essentials class files ahead of the Toplink-Essentials JAR file used by my application. Therefore my applications all run with the approved Toplink distributable, however my unit tests run with the instrumented files. There were two small changes I had to make:

(1) Modified oracle.toplink.essentials.tools.schemaframework.FieldDefinition to not write out the unique field keyword if the database was HSQL. From Line 168 of the appendDBString method:

   if(isUnique() && !session.getPlatform().isHSQL()) {
      session.getPlatform().printFieldUnique(writer, shouldPrintFieldIdentityClause);
   }

(2) Modified oracle.toplink.essentials.tools.schemaframework.DefaultTableGenerator to add the unique constraints if the database is HSQL on line 253 of initTableSchema()

 if( dbField.isUnique() && databasePlatform.isHSQL()) {
   tblDef.addUniqueKeyConstraint(dbTbl.getName(),dbField.getName());
 }

Currently I have not posted any information to the Glassfish project with these updates. I am not certain this is the ideal way to achieve this, but from my unit testing perspective I appear to be off to the races. If you are interested in these changes, please contact me and I'll look into working to get them submitted for Glassfish.

JPA Unit Testing

Up until now, I have been testing my stamp services using a test-schema that lives on my MySQL server. This server is remote (well in my basement connected to a 100Mb ethernet). While this has worked well for me, as the number of unit tests have increased in my code, the time of the tests is also increasing. Eskatos's wrote a great little blog article which introduced HSQLDB to my vernacular (see Unit test JPA Entities with in-memory database). Of course in his scenario, hibernate was used in place of Toplink. However I was intrigued by the idea of using a in-memory only database for unit tests. So I set about to get my unit tests to run. This turned out to be tricky to get working using Toplink. The first issue I ran into was that the tables refused to be created on startup. It turns out, this is due to an issue with the toplink.target.database property missing from the persistence.xml file. This was outlined in a useful blog TopLink JPA and HSQLDB Quirk.

Even after making these changes however, I still could not get toplink to properly create the tables. It turns out, I had several entity beans which had name fields defined as unique=true. This caused the UNIQUE keyword to be written in the CREATE TABLE statements by Toplink which appears to be an invalid syntax for the HSQLDB database. After removing this JPA constraint from the affected objects I was able to successfully create the tables and run my tests. I also had some minor refactoring to do in some SQL utilities to leverage the persistenceUnit configuration, but I was very impressed with the speed.

Overall, my test suite went from executing in approximately eighteen seconds down to just four. While eighteen seconds may not seem like a long time, it was sufficiently long to disrupt my work efficiency. I decided to retain my MySQL peristence unit (for occasional "live" DB testing), and have now configured two test targets in Eclipse which take a org.javad.jpa.serviceName environment variable to switch between the hsqldb and toplink-test persistence units.

The final activity I will have left to do, is to determine a way to reinsert the unique statements in my entity beans without have the SQL generated for HSQLDB. There are a few threads out there, so I should be able to come up with something.

Finally, here is my persistence unit configuration for the HSQLDB database:

<persistence-unit name="hsqldb" transaction-type="RESOURCE_LOCAL">
  <provider>oracle.toplink.essentials.PersistenceProvider</provider>
  <class>org.javad.stamp.model.Album</class>
  <class>org.javad.stamp.model.CatalogueNumberReference</class>
  <class>org.javad.stamp.model.Category</class>
  <class>org.javad.stamp.model.Country</class>
  <class>org.javad.stamp.model.Stamp</class>
  <class>org.javad.stamp.model.StampCollection</class>
  <class>org.javad.model.ClassVersion</class>
  <class>org.javad.services.TestEntityWithIdentity</class>
  <properties>
    <property name="toplink.jdbc.user" value="sa"/>
    <property name="toplink.jdbc.password" value=""/>
    <!-- <property name="toplink.logging.level" value="FINEST"/> -->
    <property name="toplink.jdbc.url" value="jdbc:hsqldb:mem:."/>
    <property name="toplink.jdbc.driver" value="org.hsqldb.jdbcDriver"/>
    <property name="toplink.ddl-generation" value="create-tables"/>
    <property name="toplink.target-database" value="HSQL"/>
  </properties>
</persistence-unit>