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();
}