1. Overview

1.概述

In this quick tutorial, we’re going to cover different ways of creating LIKE queries in Spring JPA Repositories.

在这个快速教程中，我们将介绍在Spring JPA存储库中创建LIKE查询的不同方法。

We’ll start by looking at the various keywords we can use while creating query methods. Then we’ll cover the @Query annotation with named and ordered parameters.

我们将首先看一下在创建查询方法时可以使用的各种关键字。然后我们将介绍带有命名和排序参数的@Query注解。

2. Setup

2.设置

For our example, we’ll be querying a movie table.

对于我们的例子，我们将查询一个movie表。

Let’s define our Movie entity:

让我们来定义我们的Movie实体。

@Entity
public class Movie {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;
    private String title;
    private String director;
    private String rating;
    private int duration;

    // standard getters and setters
}

With our Movie entity defined, let’s create some sample insert statements:

有了我们的Movie实体的定义，让我们创建一些插入语句的样本。

INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(1, 'Godzilla: King of the Monsters', ' Michael Dougherty', 'PG-13', 132);
INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(2, 'Avengers: Endgame', 'Anthony Russo', 'PG-13', 181);
INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(3, 'Captain Marvel', 'Anna Boden', 'PG-13', 123);
INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(4, 'Dumbo', 'Tim Burton', 'PG', 112);
INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(5, 'Booksmart', 'Olivia Wilde', 'R', 102);
INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(6, 'Aladdin', 'Guy Ritchie', 'PG', 128);
INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(7, 'The Sun Is Also a Star', 'Ry Russo-Young', 'PG-13', 100);

3. LIKE Query Methods

3.LIKE查询方法

For many simple LIKE query scenarios, we can take advantage of a variety of keywords to create query methods in our repositories.

对于许多简单的LIKE查询场景，我们可以利用各种关键字在我们的存储库中创建查询方法。。

Let’s explore them now.

现在让我们来探讨一下这些问题。

3.1. Containing, Contains, IsContaining and Like

3.1.包含, 包含, 是包含和相似

Let’s look at how we can perform the following LIKE query with a query method:

让我们来看看我们如何用查询方法执行下面的LIKE查询。

SELECT * FROM movie WHERE title LIKE '%in%';

First, let’s define query methods using Containing, Contains and IsContaining:

首先，让我们使用Containing、Contains和IsContaining定义查询方法。

List<Movie> findByTitleContaining(String title);
List<Movie> findByTitleContains(String title);
List<Movie> findByTitleIsContaining(String title);

Let’s call our query methods with the partial title in:

让我们用部分标题in来调用我们的查询方法。

List<Movie> results = movieRepository.findByTitleContaining("in");
assertEquals(3, results.size());

results = movieRepository.findByTitleIsContaining("in");
assertEquals(3, results.size());

results = movieRepository.findByTitleContains("in");
assertEquals(3, results.size());

We can expect each of the three methods to return the same results.

我们可以期望这三种方法中的每一种都能返回相同的结果。

Spring also provides us with a Like keyword, but it behaves slightly differently in that we’re required to provide the wildcard character with our search parameter.

Spring也为我们提供了一个Like关键字，但是它的行为略有不同，我们需要在搜索参数中提供通配符。

Let’s define a LIKE query method:

我们来定义一个LIKE查询方法。

List<Movie> findByTitleLike(String title);

Now we’ll call our findByTitleLike method with the same value we used before but including the wildcard characters:

现在我们将调用我们的findByTitleLike方法，其值与我们之前使用的相同，但包括通配符。

results = movieRepository.findByTitleLike("%in%");
assertEquals(3, results.size());

3.2. StartsWith

3.2.StartsWith

Let’s look at the following query:

让我们看看下面这个查询。

SELECT * FROM Movie WHERE Rating LIKE 'PG%';

We’ll use the StartsWith keyword to create a query method:

我们将使用StartsWith关键字来创建一个查询方法。

List<Movie> findByRatingStartsWith(String rating);

With our method defined, let’s call it with the value PG:

定义好我们的方法后，让我们用PG值来调用它。

List<Movie> results = movieRepository.findByRatingStartsWith("PG");
assertEquals(6, results.size());

3.3. EndsWith

3.3.EndsWith

Spring provides us with the opposite functionality with the EndsWith keyword.

Spring通过EndsWith关键字为我们提供了相反的功能。

Let’s consider this query:

让我们考虑一下这个疑问。

SELECT * FROM Movie WHERE director LIKE '%Burton';

Now we’ll define an EndsWith query method:

现在我们要定义一个EndsWith查询方法。

List<Movie> findByDirectorEndsWith(String director);

Once we’ve defined our method, let’s call it with the Burton parameter:

一旦我们定义了我们的方法，让我们用Burton参数调用它。

List<Movie> results = movieRepository.findByDirectorEndsWith("Burton");
assertEquals(1, results.size());

3.4. Case Insensitivity

3.4.大小写不敏感

We often want to find all the records containing a certain string regardless of the case. In SQL, we might accomplish this by forcing the column to all capital or lowercase letters and providing the same with values we’re querying.

我们经常想找到所有包含某个字符串的记录，而不管其大小写。在SQL中，我们可以通过强制该列为所有大写或小写字母，并提供与我们所查询的值相同的方式来实现这一目的。

With Spring JPA, we can use the IgnoreCase keyword combined with one of our other keywords:

对于Spring JPA，我们可以使用IgnoreCase关键字与我们的其他关键字之一相结合。

List<Movie> findByTitleContainingIgnoreCase(String title);

Now we can call the method with the and expect to get results containing both lowercase and uppercase results:

现在我们可以用the调用该方法，并期望得到包含小写和大写的结果。

List<Movie> results = movieRepository.findByTitleContainingIgnoreCase("the");
assertEquals(2, results.size());

3.5. Not

3.5. 不

Sometimes we want to find all the records that don’t contain a particular string. We can use the NotContains, NotContaining and NotLike keywords to do that.

有时我们想找到所有不包含某个特定字符串的记录。我们可以使用NotContains、NotContaining和NotLike关键字来实现这个目的。

Let’s define a query using NotContaining to find movies with ratings that don’t contain PG:

让我们使用NotContaining来定义一个查询，以找到评分不包含PG的电影。

List<Movie> findByRatingNotContaining(String rating);

Now let’s call our newly defined method:

现在我们来调用我们新定义的方法。

List<Movie> results = movieRepository.findByRatingNotContaining("PG");
assertEquals(1, results.size());

To achieve functionality that finds records where the director’s name doesn’t start with a particular string, we’ll use the NotLike keyword to retain control over our wildcard placement:

为了实现找到导演的名字不是以特定字符串开头的记录的功能，我们将使用NotLike关键字来保留对我们通配符位置的控制。

List<Movie> findByDirectorNotLike(String director);

Finally, let’s call the method to find all the movies where the director’s name starts with something other than An:

最后，让我们调用这个方法来找到所有导演的名字不是以An开头的电影。

List<Movie> results = movieRepository.findByDirectorNotLike("An%");
assertEquals(5, results.size());

We can use NotLike in a similar way to accomplish a Not combined with the EndsWith kind of functionality.

我们可以以类似的方式使用NotLike来完成Not与EndsWith相结合的那种功能。

4. Using @Query

4.使用@Query

Sometimes we need to create queries that are too complicated for Query Methods or would result in absurdly long method names. In those cases, we can use the @Query annotation to query our database.

有时，我们需要创建对于查询方法来说过于复杂的查询，或者会导致方法名称长得离谱。在这些情况下，我们可以使用@Query注解来查询我们的数据库。

4.1. Named Parameters

4.1.命名的参数

For comparison purposes, we’ll create a query that’s equivalent to the findByTitleContaining method we defined earlier:

为了比较，我们将创建一个查询，相当于我们之前定义的findByTitleContaining方法。

@Query("SELECT m FROM Movie m WHERE m.title LIKE %:title%")
List<Movie> searchByTitleLike(@Param("title") String title);

We include our wildcards in the query we supply. The @Param annotation is important here because we’re using a named parameter.

我们在提供的查询中包括我们的通配符。@Param注解在这里很重要，因为我们正在使用一个命名的参数。

4.2. Ordered Parameters

4.2 有序参数

In addition to named parameters, we can use ordered parameters in our queries:

除了命名参数外，我们还可以在查询中使用有序参数。

@Query("SELECT m FROM Movie m WHERE m.rating LIKE ?1%")
List<Movie> searchByRatingStartsWith(String rating);

We have control of our wildcards, so this query is the equivalent of the findByRatingStartsWith query method.

我们可以控制我们的通配符，所以这个查询相当于findByRatingStartsWith查询方法。

Let’s find all the movies with a rating starting with PG:

让我们找到所有评分以PG开头的电影。

List<Movie> results = movieRepository.searchByRatingStartsWith("PG");
assertEquals(6, results.size());

When we use ordered parameters in LIKE queries with untrusted data, we should escape incoming search values.

当我们在LIKE查询中使用有序的参数与不信任的数据时，我们应该逃避传入的搜索值。

If we’re using Spring Boot 2.4.1 or later, we can use the SpEL escape method:

如果我们使用Spring Boot 2.4.1或更高版本，我们可以使用SpEL>escape方法。

@Query("SELECT m FROM Movie m WHERE m.director LIKE %?#{escape([0])} escape ?#{escapeCharacter()}")
List<Movie> searchByDirectorEndsWith(String director);

Now let’s call our method with the value Burton:

现在让我们用Burton的值调用我们的方法。

List<Movie> results = movieRepository.searchByDirectorEndsWith("Burton");
assertEquals(1, results.size());

5. Conclusion

5.总结

In this short article, we learned how to create LIKE queries in Spring JPA Repositories.

在这篇短文中，我们学习了如何在Spring JPA Repositories中创建LIKE查询。

First, we learned how to use the provided keywords to create query methods.

首先，我们学习了如何使用提供的关键词来创建查询方法。

Then we learned how to accomplish the same tasks using the @Query parameter with both named and ordered parameters.

然后我们学习了如何使用@Query参数来完成同样的任务，包括命名参数和有序参数。

The full example code is available over on GitHub.

完整的示例代码可在GitHub上获得，。