If you're using a relational database it's pretty easy to execute case-
insensitive queries because it's a native feature of the database. It
usually looks something like this:
select * from Person where UPPER(lastName) = 'ROSS'
This is a challenge for the App Engine Datastore because we rely on
executing scans over a limited subset of your data and returning
results as we encounter them. Consider the following strings in
lexicographic order:
...
ROSE
...
ROSS
...
ROSTA
...
Rose
...
Ross
...
Rosta
...
rose
...
ross
...
rosta
...
As you can see there could be an unlimited number of rows in between
'ROSS' and 'Ross', and also between 'Ross' and 'ross' (okay not
unlimited, we do have a limit on indexed string length, but definitely
Large), so if we start scanning at 'ROSS' we might have to skip a huge
number of results before we hit 'Ross', and that doesn't scale. This
is why the datastore doesn't support case-insensitive queries.
Fortunately it's not hard to implement support for case-insensitive
queries in your application. Here's the approach: for each field that
you want to query in a case-insensitive way, create a duplicate field
that stores the value of that field in either all upper or lowercase
letters. Then, have your model object register a pre-persist callback
with your persistence framework (JPA or JDO), and then populate the
duplicate fields inside that callback. Here's what it looks like.
JPA:
import javax.persistence.PrePersist;
import javax.persistence.PreUpdate;
@Entity
public class Person {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String lastName;
private String uppercaseLastName;
public Long getId() {
return id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
@PrePersist
@PreUpdate
public void prePersist() {
if (lastName != null) {
uppercaseLastName = lastName.toUpperCase();
} else {
uppercaseLastName = null;
}
}
}
public List<Person> getPeopleByLastName(String lastName) {
Query q = em.createQuery("select from Person where uppercaseLastName
= :p");
q.setParameter("p", lastName.toUpperCase());
return (List<Person>) q.getResultList();
}
JDO:
import javax.jdo.listener.StoreCallback;
@PersistenceCapable(identityType = IdentityType.APPLICATION)
public class Person implements StoreCallback {
@PrimaryKey
@Persistent(valueStrategy = IdGeneratorStrategy.IDENTITY)
private Long id;
private String lastName;
private String uppercaseLastName;
public Long getId() {
return id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public void jdoPreStore() {
if (lastName != null) {
uppercaseLastName = lastName.toUpperCase();
} else {
uppercaseLastName = null;
}
}
}
public List<Person> getPeopleByLastName(String lastName) {
Query q = pm.newQuery(Person.class, "uppercaseLastName == :p");
return (List<Person>) q.execute(lastName.toUpperCase());
}
On 21 mar, 17:41, vchalmel <groupeprojeten...@gmail.com> wrote:
> Hi !
>
> I wonder how to compare a String stored in a datastore entity (field
> "login") to an entry (also named login) considering their lowercase
> version :
> - I tried : "SELECT count(this) FROM "+Gestionnaire.class.getName()+"
> WHERE LowerCase(login)== LowerCase('"+login+"')" which throws an
> exception
>
> I have another problem with this query, I don't want to exclude the
> one-character login like, maybe "1", but this also fires an exception
> ( java.lang.Character cannot be cast to
> com.google.appengine.api.datastore.Key )
--
You received this message because you are subscribed to the Google Groups
"Google App Engine" group.
To post to this group, send email to google-appeng...@googlegroups.com.
To unsubscribe from this group, send email to
google-appengine+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/google-appengine?hl=en.
