spring boot Jpa 按例查询 复杂查询

按例查询即Query by Example (QBE),有时我们字段很多,前端向我们进行数据查询调用时,我们对每个字段写select where and查询语句比较繁琐,利用Jpa的Example可以解决这个问题

举例:

User实体

package com.southwind.springboottest.entity;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import java.math.BigDecimal;

@Entity
@Data
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String name;
    private String username;
    private String password;
    private BigDecimal idcard;
    private Integer status;
}

UserRepository:

package com.southwind.springboottest.repository;

import com.southwind.springboottest.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface UserRepository extends JpaRepository<User,Integer> {
    public List<User> findByUsername(String username);
}

测试:

package com.southwind.springboottest;

import com.southwind.springboottest.entity.User;
import com.southwind.springboottest.repository.UserRepository;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;

import java.util.List;

@SpringBootTest
class SpringboottestApplicationTests {
    @Autowired
    private UserRepository repository;
        // 测试按例查询
    @Test
    public void queryByExample(){
        User user = new User();
        user.setName("张三");
        Example<User> example = Example.of(user);
        List<User> list = repository.findAll(example);
        System.out.println(list);
    }

}

结果输出:

多字段查询测试:

    // 测试多字段查询
    @Test
    public void queryByExample2(){
        User user = new User();
        // 查询name和Id满足下面条件的user
        user.setName("abc");
        user.setId(7);
        Example<User> example = Example.of(user);
        List<User> list = repository.findAll(example);
        System.out.println(list);
    }

结果输出:

Example.of方法使用默认的匹配器ExampleMatcher:
默认的匹配器会忽略空值,即字段值为Null时直接忽略掉
注意上面的id都是Integer类型，所以默认值是null，假如id是int类型，默认值为0，则会将id=0加入where条件进行匹配，因此在类里面如果有基本数据类型，一定要记得使用withIgnorePaths来忽略这个属性（下面有使用的例子）

static <T> Example<T> of(T probe) {
    return new TypedExample(probe, ExampleMatcher.matching());
}

static ExampleMatcher matching() {
    return matchingAll();
}

static ExampleMatcher matchingAll() {
    return (new TypedExampleMatcher()).withMode(ExampleMatcher.MatchMode.ALL);
}

我们也可以自定义匹配器规则:

    // 测试自定义匹配器规则
    @Test
    public void testMatcher(){
        User user = new User();
        user.setName("a");
        user.setPassword("hahaha");
        user.setUsername("是");

        ExampleMatcher matcher = ExampleMatcher.matching()
                .withMatcher("name", match -> match.startsWith())  //前缀匹配a%
                .withMatcher("username", match -> match.contains()) //全部模糊匹配%b%
                .withIgnorePaths("password");  //忽略字段passowrd，不管它是什么都不加入匹配
        Example<User> example = Example.of(user, matcher);
        List<User>list = repository.findAll(example);
        System.out.println(list);
    }
                

数据库user表:

查询结果: