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.

MySQL with Servlets - Poor uptime

Now that I have my mobile application working on the smartphone I have been quite pleased with the client. Then I started getting strange timeouts and no matter what I was trying to do I was unable to execute queries against the database. From looking in the Tomcat logs I discovered an interesting exception:

Last packet sent to the server was 3 ms ago.
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2579)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2867)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3255)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1293)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1428)

This led me to research whether this could be a MySQL issue. It turns out, that the connection pool will close all connections after eight hours of inactivity. Their solution of using the "autoReconnect" property apparently will not work under most circumstances. This is covered in section 26.4.5.3.4 on the MySQL reference manual.

The solution? Well it has been suggested the writing a small daemon thread which wakes up every hour and executes some small query should be sufficient to keep the connections open. I have not implemented this yet, but this seems reasonable. In my case I'll probably tie it to one of my servlets in their init() methods.