Advanced Querying in Apache Cayenne

1. Overview

Previously, we’ve focused on how to get started with Apache Cayenne.

In this article, we’ll cover how to write simple and advanced queries with the ORM.

2. Setup

The setup is similar to the one used in the previous article.

Additionally, before each test, we save three authors and at the end, we remove them:

• Paul Xavier
• pAuL Smith
• Vicky Sarra

3. ObjectSelect

Let’s start simple, and look at how we can get all authors with names containing “Paul”:

@Test
public void whenContainsObjS_thenWeGetOneRecord() {
    List<Author> authors = ObjectSelect.query(Author.class)
      .where(Author.NAME.contains("Paul"))
      .select(context);

    assertEquals(authors.size(), 1);
}

Next, let’s see how we can apply a case-insensitive LIKE type of query on the Author’s name column:

@Test
void whenLikeObjS_thenWeGetTwoAuthors() {
    List<Author> authors = ObjectSelect.query(Author.class)
      .where(Author.NAME.likeIgnoreCase("Paul%"))
      .select(context);

    assertEquals(authors.size(), 2);
}

Next, the endsWith() expression will return just one record as only one author has the matching name:

@Test
void whenEndsWithObjS_thenWeGetOrderedAuthors() {
    List<Author> authors = ObjectSelect.query(Author.class)
      .where(Author.NAME.endsWith("Sarra"))
      .select(context);
    Author firstAuthor = authors.get(0);

    assertEquals(authors.size(), 1);
    assertEquals(firstAuthor.getName(), "Vicky Sarra");
}

A more complex one is querying Authors whose names are in a list:

@Test
void whenInObjS_thenWeGetAuthors() {
    List names = Arrays.asList(
      "Paul Xavier", "pAuL Smith", "Vicky Sarra");
 
    List<Author> authors = ObjectSelect.query(Author.class)
      .where(Author.NAME.in(names))
      .select(context);

    assertEquals(authors.size(), 3);
}

The nin one is the opposite, here only “Vicky” will be present in the result:

@Test
void whenNinObjS_thenWeGetAuthors() {
    List names = Arrays.asList(
      "Paul Xavier", "pAuL Smith");
    List<Author> authors = ObjectSelect.query(Author.class)
      .where(Author.NAME.nin(names))
      .select(context);
    Author author = authors.get(0);

    assertEquals(authors.size(), 1);
    assertEquals(author.getName(), "Vicky Sarra");
}

Note that these two following codes are same as they both will create an expression of the same type with the same parameter:

Expression qualifier = ExpressionFactory
  .containsIgnoreCaseExp(Author.NAME.getName(), "Paul");

Author.NAME.containsIgnoreCase("Paul");

Here is a list of some available expressions in Expression and ExpressionFactory classes:

• likeExp: for building the LIKE expression
• likeIgnoreCaseExp: used to build the LIKE_IGNORE_CASE expression
• containsExp: an expression for a LIKE query with the pattern matching anywhere in the string
• containsIgnoreCaseExp: same as containsExp but using case-insensitive approach
• startsWithExp: the pattern should match the beginning of the string
• startsWithIgnoreCaseExp: similar to the startsWithExp but using the case-insensitive approach
• endsWithExp: an expression that matches the end of a string
• endsWithIgnoreCaseExp: an expression that matches the end of a string using the case-insensitive approach
• expTrue: for boolean true expression
• expFalse: for boolean false expression
• andExp: used to chain two expressions with the and operator
• orExp: to chain two expressions using the or operator

More written tests are available in the code source of the article, please check the Github repository.

4. SelectQuery

It’s the widest-used query type in user applications. The SelectQuery describes a simple and powerful API that acts like SQL syntax, but still with Java Objects and methods followed with builder patterns to construct more complex expressions.

Here we’re talking about an expression language where we build queries using both Expression (to build expressions) aka qualifier and Ordering (to sort results) classes that are next converted to native SQL by the ORM.

To see this in action, we’ve put together some tests that show in practice how to build some expressions and sorting data.

Let’s apply a LIKE query to get Authors with the name like “Paul”:

@Test
void whenLikeSltQry_thenWeGetOneAuthor() {
    Expression qualifier 
      = ExpressionFactory.likeExp(Author.NAME.getName(), "Paul%");
    SelectQuery query 
      = new SelectQuery(Author.class, qualifier);
    
    List<Author> authorsTwo = context.performQuery(query);

    assertEquals(authorsTwo.size(), 1);
}

That means if you don’t provide any expression to the query (SelectQuery), the result will be all the records of the Author table.

A similar query can be performed using the containsIgnoreCaseExp expression to get all authors with the name containing Paul regardless the case of the letters:

@Test
void whenCtnsIgnorCaseSltQry_thenWeGetTwoAuthors() {
    Expression qualifier = ExpressionFactory
      .containsIgnoreCaseExp(Author.NAME.getName(), "Paul");
    SelectQuery query 
      = new SelectQuery(Author.class, qualifier);
    
    List<Author> authors = context.performQuery(query);

    assertEquals(authors.size(), 2);
}

Similarly, let’s get authors with names containing “Paul”, in a case case-insensitive way (containsIgnoreCaseExp) and with the name that ends (endsWithExp) with the letter h:

@Test
void whenCtnsIgnorCaseEndsWSltQry_thenWeGetTwoAuthors() {
    Expression qualifier = ExpressionFactory
      .containsIgnoreCaseExp(Author.NAME.getName(), "Paul")
      .andExp(ExpressionFactory
        .endsWithExp(Author.NAME.getName(), "h"));
    SelectQuery query = new SelectQuery(
      Author.class, qualifier);
    List<Author> authors = context.performQuery(query);

    Author author = authors.get(0);

    assertEquals(authors.size(), 1);
    assertEquals(author.getName(), "pAuL Smith");
}

An ascending order can be performed using the Ordering class:

@Test
void whenAscOrdering_thenWeGetOrderedAuthors() {
    SelectQuery query = new SelectQuery(Author.class);
    query.addOrdering(Author.NAME.asc());
 
    List<Author> authors = query.select(context);
    Author firstAuthor = authors.get(0);

    assertEquals(authors.size(), 3);
    assertEquals(firstAuthor.getName(), "Paul Xavier");
}

Here instead of using query.addOrdering(Author.NAME.asc()), we can also just use the SortOrder class to get the ascending order:

query.addOrdering(Author.NAME.getName(), SortOrder.ASCENDING);

Relatively there is the descending ordering:

@Test
void whenDescOrderingSltQry_thenWeGetOrderedAuthors() {
    SelectQuery query = new SelectQuery(Author.class);
    query.addOrdering(Author.NAME.desc());

    List<Author> authors = query.select(context);
    Author firstAuthor = authors.get(0);

    assertEquals(authors.size(), 3);
    assertEquals(firstAuthor.getName(), "pAuL Smith");
}

As we’ve seen in the previous example – another way to set this ordering is:

query.addOrdering(Author.NAME.getName(), SortOrder.DESCENDING);

5. SQLTemplate

SQLTemplate is also one alternative we can use with Cayenne to don’t use object style querying.

Build queries with SQLTemplate is directly relative to write native SQL statements with some parameters. Let’s implement some quick examples.

Here is how we delete all authors after each test:

@After
void deleteAllAuthors() {
    SQLTemplate deleteAuthors = new SQLTemplate(
      Author.class, "delete from author");
    context.performGenericQuery(deleteAuthors);
}

To find all recorded Authors we just need to apply the SQL query select * from Author and we’ll directly see that the result is correct as we have exactly three saved authors:

@Test
void givenAuthors_whenFindAllSQLTmplt_thenWeGetThreeAuthors() {
    SQLTemplate select = new SQLTemplate(
      Author.class, "select * from Author");
    List<Author> authors = context.performQuery(select);

    assertEquals(authors.size(), 3);
}

Next, let’s get the Author with the name “Vicky Sarra”:

@Test
void givenAuthors_whenFindByNameSQLTmplt_thenWeGetOneAuthor() {
    SQLTemplate select = new SQLTemplate(
      Author.class, "select * from Author where name = 'Vicky Sarra'");
    List<Author> authors = context.performQuery(select);
    Author author = authors.get(0);

    assertEquals(authors.size(), 1);
    assertEquals(author.getName(), "Vicky Sarra");
}

6. EJBQLQuery

Next, let’s query data through the EJBQLQuery, which was created as a part of an experiment to adopt the Java Persistence API in Cayenne.

Here, the queries are applied with a parametrized object style; let’s have a look at some practical examples.

First, the search of all saved authors will look like this:

@Test
void givenAuthors_whenFindAllEJBQL_thenWeGetThreeAuthors() {
    EJBQLQuery query = new EJBQLQuery("select a FROM Author a");
    List<Author> authors = context.performQuery(query);

    assertEquals(authors.size(), 3);
}

Let’s search the Author again with the name “Vicky Sarra”, but now with EJBQLQuery:

@Test
void givenAuthors_whenFindByNameEJBQL_thenWeGetOneAuthor() {
    EJBQLQuery query = new EJBQLQuery(
      "select a FROM Author a WHERE a.name = 'Vicky Sarra'");
    List<Author> authors = context.performQuery(query);
    Author author = authors.get(0);

    assertEquals(authors.size(), 1);
    assertEquals(author.getName(), "Vicky Sarra");
}

An even better example is updating the author:

@Test
void whenUpdadingByNameEJBQL_thenWeGetTheUpdatedAuthor() {
    EJBQLQuery query = new EJBQLQuery(
      "UPDATE Author AS a SET a.name "
      + "= 'Vicky Edison' WHERE a.name = 'Vicky Sarra'");
    QueryResponse queryResponse = context.performGenericQuery(query);

    EJBQLQuery queryUpdatedAuthor = new EJBQLQuery(
      "select a FROM Author a WHERE a.name = 'Vicky Edison'");
    List<Author> authors = context.performQuery(queryUpdatedAuthor);
    Author author = authors.get(0);

    assertNotNull(author);
}

If we just want to select a column we should use this query “select a.name FROM Author a”. More examples are available in the source code of the article on Github.

7. SQLExec

SQLExec is also a new fluent query API introduced from version M4 of Cayenne.

A simple insert looks like this:

@Test
void whenInsertingSQLExec_thenWeGetNewAuthor() {
    int inserted = SQLExec
      .query("INSERT INTO Author (name) VALUES ('Baeldung')")
      .update(context);

    assertEquals(inserted, 1);
}

Next, we can update an author based on his name:

@Test
void whenUpdatingSQLExec_thenItsUpdated() {
    int updated = SQLExec.query(
      "UPDATE Author SET name = 'Baeldung' "
      + "WHERE name = 'Vicky Sarra'")
      .update(context);

    assertEquals(updated, 1);
}

We can get more details from the documentation.

8. Conclusion

In this article, we’ve looked at a number of ways to write simple and more advanced queries using Cayenne.

As always, the source code for this article can be found over on GitHub.