Ignore case in JDO query

Question

I would like to select a list of results from a database, but the == operator for JDO queries is case-sensitive. Is there a way to select "USER", "user", and "User" from a table using a single parameter?

In MySQL you have the LIKE operator, and in Java the equalsIgnoreCase function. However, neither of them work in this example.

PersistenceManager pm = JDO.factory.getPersistenceManager();

Query query = pm.newQuery(User.class, "username == usernameParam");
query.declareParameters("String usernameParam");

List<User> results = (List<User>) query.execute(username);

Answer 1

You need to store a copy of your field in a case insensitive manner - lower case, for example, though a 'collation case' is better if it's available. Then, query on that.

The reason for this is that there's no way to efficiently search a regular index in a 'case insensitive' manner.