天天看点

springdata jpa使用Example快速实现动态查询

Example官方介绍

Query by Example (QBE) is a user-friendly querying technique with a simple interface. It allows dynamic query creation and does not require to write queries containing field names. In fact, Query by Example does not require to write queries using store-specific query languages at all.

谷歌翻译: 

按例查询(QBE)是一种用户界面友好的查询技术。 它允许动态创建查询,并且不需要编写包含字段名称的查询。 实际上,按示例查询不需要使用特定的数据库的查询语言来编写查询语句。

Example api的组成

Probe: 含有对应字段的实例对象。

ExampleMatcher:ExampleMatcher携带有关如何匹配特定字段的详细信息,相当于匹配条件。

Example:由Probe和ExampleMatcher组成,用于查询。

限制

属性不支持嵌套或者分组约束,比如这样的查询 firstname = ?0 or (firstname = ?1 and lastname = ?2)

灵活匹配只支持字符串类型,其他类型只支持精确匹配

Limitations 

1. No support for nested/grouped property constraints like firstname = ?0 or (firstname = ?1 and lastname = ?2) 

2. Only supports starts/contains/ends/regex matching for strings and exact matching for other property types

使用

创建实体映射:

@Entity

@Table(name="t_user")

@Data

@AllArgsConstructor

@NoArgsConstructor

@ToString

public class User {

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    private Integer id;

    @Column(name="username")

    private String username;

    @Column(name="password")

    private String password;

    @Column(name="email")

    private String email;

    @Column(name="phone")

    private String phone;

    @Column(name="address")

    private String address;

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

测试查询:

@Test

public void contextLoads() {

    User user = new User();

    user.setUsername("admin");

    Example<User> example = Example.of(user);

    List<User> list = userRepository.findAll(example);

    System.out.println(list);

}

打印的sql语句如下:

Hibernate: 

    select

        user0_.id as id1_0_,

        user0_.address as address2_0_,

        user0_.email as email3_0_,

        user0_.password as password4_0_,

        user0_.phone as phone5_0_,

        user0_.username as username6_0_ 

    from

        t_user user0_ 

    where

        user0_.username=?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

可以发现,试用Example查询,默认情况下会忽略空值,官方文档也有说明:

This is a simple domain object. You can use it to create an Example. By default, fields having null values are ignored, and strings are matched using the store specific defaults. Examples can be built by either using the of factory method or by using ExampleMatcher. Example is immutable.

在上面的测试之中,我们只是只是定义了Probe而没有ExampleMatcher,是因为默认会不传时会使用默认的匹配器。点进方法可以看到下面的代码:

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);

}

1

2

3

4

5

6

7

8

9

10

11

自定匹配器规则

@Test

public void contextLoads() {

    User user = new User();

    user.setUsername("y");

    user.setAddress("sh");

    user.setPassword("admin");

    ExampleMatcher matcher = ExampleMatcher.matching()

            .withMatcher("username", ExampleMatcher.GenericPropertyMatchers.startsWith())//模糊查询匹配开头,即{username}%

            .withMatcher("address" ,ExampleMatcher.GenericPropertyMatchers.contains())//全部模糊查询,即%{address}%

            .withIgnorePaths("password");//忽略字段,即不管password是什么值都不加入查询条件

    Example<User> example = Example.of(user ,matcher);

    List<User> list = userRepository.findAll(example);

    System.out.println(list);

}

打印的sql语句如下:

select

    user0_.id as id1_0_,

    user0_.address as address2_0_,

    user0_.email as email3_0_,

    user0_.password as password4_0_,

    user0_.phone as phone5_0_,

    user0_.username as username6_0_ 

from

    t_user user0_ 

where

    (

        user0_.username like ?

    ) 

    and (

        user0_.address like ?

    )