Updated Mar 14, 2009 by treeder
Labels:	Featured, Phase-Support, Manual

JPAQuery

All about querying with SimpleJPA

Introduction

SimpleJPA queries are a subset of the JPA spec due to the limitations of SimpleDB. Keep in mind that many things can be accomplished by doing things in a different way.

Supported

=
<
>
<=
>=
<> (not equal)
AND
OR
IS NULL
IS NOT NULL
LIKE - only "starts-with" due to SimpleDB limitations.
ORDER BY

Limitations

Can only query on a single object type, no joins.

Examples

Basic query

query = entityManager.createQuery("select o from MyTestObject o where o.income = :income and o.age = :age");
query.setParameter("income", 50507.0);
query.setParameter("age", 12);
List<MyTestObject> obs = query.getResultList();

Query with referenced object filter (@ManyToOne)

query = entityManager.createQuery("select o from MyTestObject o where o.anotherObject = :anotherObject");
query.setParameter("anotherObject", anotherObject);
List<MyTestObject> obs = query.getResultList();

Which is equivalent to:

query = entityManager.createQuery("select o from MyTestObject o where o.anotherObject.id = :anotherObjectId");
query.setParameter("anotherObjectId", anotherObject.getId());
List<MyTestObject> obs = query.getResultList();

Can also query down object graph (1 level only right now):

query = entityManager.createQuery("select o from MyTestObject o where o.someOtherObject.name = :name");
query.setParameter("name", "finnigan");
List<MyTestObject> obs = query.getResultList();

But keep in mind, querying down the graph can be slow because it will query for the id's down the graph, then apply them to the top level query.

Like Query:

query = em.createQuery("select o from MyTestObject3 o where o.someField3 like :x");
query.setParameter("x", "fred and%"); 
List<MyTestObject3> obs = query.getResultList();

This will return all MyTestObject3's that start with "fred and".

Sort query:

query = entityManager.createQuery("select o from MyTestObject o where o.income = :income and o.age = :age order by o.age");
query.setParameter("income", 50507.0);
query.setParameter("age", 12);
List<MyTestObject> obs = query.getResultList();

Count:

Count returns a result list with a single entry that contains a Long.

query = entityManager.createQuery("select count(o) from MyTestObject o where o.income = :income and o.age = :age order by o.age");
query.setParameter("income", 50507.0);
query.setParameter("age", 12);
List obs = query.getResultList();
long count = obs.get(0);

QueryBuilder

QueryBuilder helps with creating parameterized queries.

Example usage:

// the one liner
Query qb = new QueryBuilderImpl()
   .append("select o from IndexStats o where o.numKeywords < :x", "x", 10).makeQuery(em);
   List<IndexStats> resultList = qb.getResultList();

private List<Book> getBooks(BookFilter filter) {
    QueryBuilder qb = new QueryBuilderImpl();
    qb.append("select o from Book o where 1 = 1");
    if (filter.getTitle() != null) {
        qb.append(" and o.title = :title", "title", filter.getTitle());
    }
    Query q = qb.makeQuery(em());
    return q.getResultList();
}

Comment by djamescook, Mar 11, 2008

Can you show the actual SimpleDB query that results from the syntax above?

Comment by treeder, May 28, 2008

djamescook: I've added a printQueries config option that will print all the amazon queries to System.out. See: http://code.google.com/p/simplejpa/wiki/Configuration