3

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);
Vortico
  • 2,610
  • 2
  • 32
  • 49

2 Answers2

5

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
  • 100,655
  • 16
  • 128
  • 198
  • 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 Jul 30 '10 at 14:54
  • 2
    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 Jul 30 '10 at 15:15
  • Does this mean that if I have to make some text stored in a case-sensitive way searchable in a case-insensitive way the only way right now in app-engine is to create another field and replicate the given text .toLowerCase? – JohnIdol May 24 '12 at 19:54
  • @JohnIdol That's correct. You can use a computed field to do this for you. – Nick Johnson May 24 '12 at 23:50
  • @NickJohnson cool, thanks! In this case I just needed a match on a field, but - on a slightly different note - does the search API work in the same way (as in, will I have to store all the text twice to-lower/upper if I want to do non-case-sensitive search)? – JohnIdol May 28 '12 at 02:23
  • 1
    @JohnIdol The fulltext search API does normalization and stemming etc for you, so you don't need to do it yourself. – Nick Johnson May 28 '12 at 02:34
4

JDOQL has some String methods available. Check the documentation here.

To Fix your issue you could do the following:

PersistenceManager pm = JDO.factory.getPersistenceManager();

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

List<User> results = (List<User>) query.execute(username.toUpperCase());
Aliaksei Kliuchnikau
  • 13,589
  • 4
  • 59
  • 72
Hendrix
  • 94
  • 4