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.

1 comment:

  1. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a Java developer learn from Java Training in Chennai. or learn thru Java Online Training in India . Nowadays Java has tons of job opportunities on various vertical industry.

    ReplyDelete