Tuesday, April 6, 2010

Google App Engine datastore case insensitive queries

I started working on small App Engine based project and very soon noticed that there is no possibility to execute case insensitive queries on App Engine Datastore. This was absolute must-have for my application so I performed some googling and came with simple yet effective solution I want to share.

The idea is to store lower-case versions of fields that are used in case-insensitive queries.
So I have class Person like this one (simplified):
@PersistenceCapable
public class Person implements Serializable {
 @PrimaryKey
 @Persistent(valueStrategy = IdGeneratorStrategy.IDENTITY)
 private Key key;
 private String firstName;
 private String lastName;
 //other fields
 //getters and setters
}
and I need to search persons by first/last name ignoring case.
Class should be extended to look like this one:
@PersistenceCapable
public class Person implements Serializable, javax.jdo.listener.StoreCallback {
 @PrimaryKey
 @Persistent(valueStrategy = IdGeneratorStrategy.IDENTITY)
 private Key key;
 private String firstName;
 private String lastName;
 private String firstNameLC;
 private String lastNameLC;
 @Override
 public void jdoPreStore() {
  firstNameLC = firstName == null ? null : firstName.toLowerCase();
  lastNameLC = lastName == null ? null : lastName.toLowerCase();
 } 
 //other fields
 //getters and setters
}
This forces firstNameLC and lastNameLC fields to be filled with lowercase versions of firstName and lastName fields on every update.
Note that you don't need getters and setters for lowercase version fields.
Query can be performed like this one:
{
    PersistenceManagerFactory factory = JDOHelper.getPersistenceManagerFactory("transactions-optional");
    PersistenceManager pm = factory.getPersistenceManager();
    List<person> persons = (List<person>) pm.newQuery("SELECT FROM Person WHERE firstNameLC == name PARAMETERS String name").execute("jOhN".toLowerCase());
}

That's all

UPD: found same solution on GOOGLE APP ENGINE JAVA PERSISTENCE blog. I have no idea why I missed it before.

No comments:

Post a Comment