views:

25

answers:

1

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);
+2  A: 

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.

Nick Johnson
Wow, really? It sounds like an inefficient and error-prone solution. I have found the toLowerCase() method on http://www.jpox.org/docs/1_2/jdo/jdoql_methods.html so I may be able to query `toLowerCase(username) == toLowerCase(usernameParam)`. This seems a little inefficient and not very elegant, but it might work.
Vortico
It's the exact opposite of inefficient. Comparing every entry in the database to your query, which is what either of the approaches you mention in your question will do, is inefficient. toLowerCase() on the field won't work for the same reason.
Nick Johnson