JPA EntityManager find with case sensitive key

Question

I'm trying to use the JPA EntityManager find() method. My primary key is a string which corresponds to a user's name.

I am using JPA / Hibernate / MYSQL.

My problem is a search for user 'David' matches user 'david' due, I assume, to the case insensitive string matching in the underlying MYSQL. This causes me a whole heap of problems!!

Has anybody got an elegant solution to this? I could do a native SQL call and use the BINARY operator as documented here: http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

Anyone got a better solution? Ta.

Answer 1

Can you change the column type such that it is case insensitive? The MySQL guide has information on how to do so.

This is more or less what you found with the BINARY operator but it applies to the column type instead of when you run a SELECT

Answer 2

In JPA you have nice string functions that you can use in your queries (see EJB Persistence Specification, section 4.6.16.1). Something like the following should do the trick for you, in case you are using EJB QL:

User findUserByName(String userName) {
  Query q = em.createQuery("FROM User WHERE lower(username) = :username");
  q.setParameter("username", userName.toLowerCase());
  return (User) q.getSingleResult();
}

As you said you were using Hibernate, you can always go for criteria queries, with their built in option for ignore case. It just looks cleaner, and hopefully we get something similar for the next JPA spec.

As the previous commenter pointed out, it might be better to do this on the DB level for large tables. Most DBs should support that.