Advanced Querying in Apache Cayenne – Apache Cayenne的高级查询

1. Overview

1.概述

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

前面，我们着重介绍了如何开始使用Apache Cayenne。

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

在这篇文章中，我们将介绍如何用ORM编写简单和高级的查询。

2. Setup

2.设置

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

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:

接下来，让我们看看如何在作者的名字列上应用不区分大小写的LIKE类型的查询。

@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:

接下来，endsWith()表达式将只返回一条记录，因为只有一个作者有匹配的名字。

@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:

nin则相反，这里只有 “Vicky “会出现在结果中。

@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:

下面是表达式和ExpressionFactory类中一些可用的表达式列表：

• 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.

更多的书面测试可以在文章的代码源中找到，请查看Github资源库。

4. SelectQuery

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.

它是用户应用程序中使用最广泛的查询类型。SelectQuery描述了一个简单而强大的API，其作用类似于SQL语法，但仍有Java对象和方法跟随构建器模式来构建更复杂的表达式。

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.

这里我们讨论的是一种表达式语言，我们使用Expression （构建表达式）又称限定符和Ordering （对结果进行排序）类来构建查询，接下来ORM会将其转换为本地SQL。

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”:

让我们应用一个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.

这意味着如果你不给查询（SelectQuery）提供任何表达式，结果将是作者表中的所有记录。

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:

可以使用containsIgnoreCaseExp表达式进行类似的查询，以获得所有名字中含有保罗的作者，而不考虑字母的大小写。

@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:

同样，让我们以不区分大小写的方式（containsIgnoreCaseExp）获得名字中含有 “Paul “的作者，以及名字中以字母h结尾（endsWithExp）。

@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:

可以使用Ordering类进行升序排序。

@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.asc())，我们也可以直接使用SortOrder类来获得升序。

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

5.SQLTemplate

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

SQLTemplate也是我们可以与Cayenne一起使用的一个替代方案，不使用对象风格的查询。

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

用SQLTemplate建立查询是直接相对于编写带有一些参数的本地SQL语句。让我们来实现一些快速的例子。

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:

要找到所有记录的作者，我们只需要应用SQL查询select * from Author ，我们会直接看到结果是正确的，因为我们正好有三个保存的作者。

@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”:

接下来，让我们找到名字为 “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

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.

接下来，让我们通过EJBQLQuery来查询数据，EJBQLQuery是作为在Cayenne中采用Java Persistence API的实验的一部分而创建的。

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:

让我们再次用 “Vicky Sarra “的名字搜索作者，但现在用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.

如果我们只是想选择一个列，我们应该使用这个查询“select a.name FROM Author a”。更多的例子可以在Github上的文章源代码中找到。

7. SQLExec

7.SQLExec

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

SQLExec也是一个从Cayenne的M4版本引入的新的流畅查询API。

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

8.结论

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

在这篇文章中，我们看了一些使用Cayenne编写简单和更高级查询的方法。

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

像往常一样，本文的源代码可以在GitHub上找到over。