Spring Data JPA Keywords 关键字查询

在编写查询条件的时候，实用关键字的方式可以快速实现查询，Spring Data JPA 提供了很多可供使用的关键字，对于常用条件的查询都能得到满足。本示例使用Spring Boot + JPA + My SQL来演示在Spring Data JPA中对关键字查询方式的使用：

演示的版本：

1. Spring Boot 2.0.4.RELEASE
2. My SQL 5.*

语法

关键的字使用方式，要注意字段的名称需要以小写开头，而编写方法的时候，以大写字母开头，其方式主要是 “字段” + “关键字”+ 的形式，也可能有“字段”+“关键字”+“字段”等一些其他的形式：

例如：

1. 查询人的名字是Jack Chen

   Iterable findByFirstNameAndLastName(String firstName, String lastName);

2. 查询年龄大于18岁

   Iterable findByAgeGreaterThan(int age);

3. 查询第一名称以**开头（MySQL 默认自动会忽略大小写）

   Iterable findByFirstNameStartingWith(String firstName);

4. 查询薪水在2000到3000之间，并以从小到大排序

   Iterable findBySalaryBetweenOrderBySalaryAsc(double start, double end);

5. 查询1990/01/01之后出生的

   Iterable findByBirthAfter(Date birth);

6. 查询第一名字包含“chen”，并忽略大小写（MySQL 默认自动会忽略大小写）

   Iterable findByFirstNameContainingIgnoreCase(String firstName);

示例

Repository

package com.devnp.repository;

import java.util.Date;

import org.springframework.data.jpa.repository.JpaRepository;

import com.devnp.entity.Person;

public interface PersonRepository extends JpaRepository<Person, Integer> {

    //查询人的名字
    Iterable<Person> findByFirstNameAndLastName(String firstName, String lastName);

    //查询年龄大于18岁
    Iterable<Person> findByAgeGreaterThan(int age);

    //查询第一名称以**开头
    Iterable<Person> findByFirstNameStartingWith(String firstName);

    //查询薪水在2000到3000之间，并以从小到大排序
    Iterable<Person> findBySalaryBetweenOrderBySalaryAsc(double start, double end);

    //查询1990/01/01之后出生的
    Iterable<Person> findByBirthAfter(Date birth);

    //查询第一名字包含“chen”，并忽略大小写
    Iterable<Person> findByFirstNameContainingIgnoreCase(String firstName);

}

测试

package com.devnp;

import java.time.LocalDate;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.devnp.entity.Person;
import com.devnp.repository.PersonRepository;

@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringBootJpaKeywordApplicationTests {

    @Autowired
    private PersonRepository personRepository;

    //先添加一些数据
    @Test
    public void save() {
        List<Person> list = new ArrayList<>();

        LocalDate localDate = LocalDate.of(1900, 1, 1);
        Date date = Date.from(localDate.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant());

        list.add(new Person("NAME-1", "JACK" , 20, new Date(), 2000.00));
        list.add(new Person("NAME-2", "chen" , 20, date, 2000.12));
        list.add(new Person("NAME-3", "TOME" , 25, new Date(), 1500.12));
        list.add(new Person("NAME-4", "Chen" , 11, date, 3000.12));
        list.add(new Person("NAME-5", "Li" , 29, new Date(), 4000.52));

        personRepository.saveAll(list);
    }

    //查询人的名字
    @Test
    public void testFindByFirstNameAndLastName() {
        Iterable<Person> persons = personRepository.findByFirstNameAndLastName("JACK", "NAME-1");

        persons.forEach(System.out::println);
    }

    //查询年龄大于18岁
    @Test
    public void testFindByAgeGreaterThan() {
        Iterable<Person> persons = personRepository.findByAgeGreaterThan(18);
        persons.forEach(System.out::println);
    }

    //查询第一名称以**开头
    @Test
    public void testFindByFirstNameStartingWith() {
        Iterable<Person> persons = personRepository.findByFirstNameStartingWith("chen");
        persons.forEach(System.out::println);
    }

    //查询薪水在2000到3000之间，并以从小到大排序
    @Test
    public void testFindBySalaryBetweenOrderBySalaryAsc() {
        Iterable<Person> persons = personRepository.findBySalaryBetweenOrderBySalaryAsc(2000.00, 3000.00);
        persons.forEach(System.out::println);
    }

    //查询1990/01/01之后出生的
    @Test
    public void testFindByBirthAfter() {
        LocalDate localDate = LocalDate.of(1990, 1, 1);
        Date date = Date.from(localDate.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant());

        Iterable<Person> persons = personRepository.findByBirthAfter(date);
        persons.forEach(System.out::println);
    }

    //查询第一名字包含“chen”，并忽略大小写
    @Test
    public void testFindByFirstNameContainingIgnoreCase() {
        Iterable<Person> persons = personRepository.findByFirstNameContainingIgnoreCase("chen");
        persons.forEach(System.out::println);
    }

}

项目地址

完整演示代码可以通过GitHub : spring-boot-jpa-keyword

附录

关键字常用形式

Keyword	Sample	JPQL snippet
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is,Equals	findByFirstname,findByFirstnameIs, findByFirstnameEquals	… where x.firstname = ?1
Between	findByStartDateBetween	… where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	… where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	… where x.age <= ?1
GreaterThan	findByAgeGreaterThan	… where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age >= ?1
After	findByStartDateAfter	… where x.startDate > ?1
Before	findByStartDateBefore	… where x.startDate < ?1
IsNull	findByAgeIsNull	… where x.age is null
IsNotNull,NotNull	findByAge(Is)NotNull	… where x.age not null
Like	findByFirstnameLike	… where x.firstname like ?1
NotLike	findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1 (parameter bound with appended %)
EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1 (parameter bound with prepended %)
Containing	findByFirstnameContaining	… where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname <> ?1
In	findByAgeIn(Collection ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection ages)	… where x.age not in ?1
True	findByActiveTrue()	… where x.active = true
False	findByActiveFalse()	… where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstame) = UPPER(?1)

关键字列表

Logical keyword	Keyword expressions
AND	And
OR	Or
AFTER	After, IsAfter
BEFORE	Before, IsBefore
CONTAINING	Containing, IsContaining, Contains
BETWEEN	Between, IsBetween
ENDING_WITH	EndingWith, IsEndingWith, EndsWith
EXISTS	Exists
FALSE	False, IsFalse
GREATER_THAN	GreaterThan, IsGreaterThan
GREATER_THAN_EQUALS	GreaterThanEqual, IsGreaterThanEqual
IN	In, IsIn
IS	Is, Equals, (or no keyword)
IS_EMPTY	IsEmpty, Empty
IS_NOT_EMPTY	IsNotEmpty, NotEmpty
IS_NOT_NULL	NotNull, IsNotNull
IS_NULL	Null, IsNull
LESS_THAN	LessThan, IsLessThan
LESS_THAN_EQUAL	LessThanEqual, IsLessThanEqual
LIKE	Like, IsLike
NEAR	Near, IsNear
NOT	Not, IsNot
NOT_IN	NotIn, IsNotIn
NOT_LIKE	NotLike, IsNotLike
REGEX	Regex, MatchesRegex, Matches
STARTING_WITH	StartingWith, IsStartingWith, StartsWith
TRUE	True, IsTrue
WITHIN	Within, IsWithin
ORDERBY	OrderBy
IgnoreCase	IgnoreCase