Of Stack

springdata jpa uses Example to quickly implement dynamic query function

  • 2021-12-12 08:44:47
  • OfStack
Directory Example Official Introduction Example api Composition Limitation Use Test Query Custom Matcher Rule Supplement Official Creation ExampleMatcher Example (1.8 lambda) StringMatcher Parameter Summary

Official introduction of 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.

Google Translate:

Query by Example (QBE) is a user-friendly query technology. It allows queries to be created dynamically without writing queries containing field names. In fact, querying by example does not require writing query statements in the query language of a specific database.

Composition of Example api

Probe An instance object containing the corresponding field. ExampleMatcher ExampleMatcher carries detailed information about how to match a particular field, which is equivalent to matching criteria. Example Consists of Probe and ExampleMatcher for queries.

Limit

Property does not support nesting or grouping constraints, such as queries like firstname =? 0 or (firstname =? 1 and lastname =? 2) Flexible matching only supports string types, while other types only support exact matching

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

Use

Create Entity Mappings: 


@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;
}

Test query 


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

The printed sql statement is as follows: 


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=?

It can be found that when you try Example query, null values will be ignored by default, and the official document also explains:

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.

In the above test, we only defined Probe instead of ExampleMatcher, because the default matcher will be used when it is not transmitted by default. Click on the method to see the following code: 


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

Custom Matcher Rule 


@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())// Fuzzy query matches the beginning, that is {username}%
            .withMatcher("address" ,ExampleMatcher.GenericPropertyMatchers.contains())// All fuzzy queries, that is, %{address}%
            .withIgnorePaths("password");// Ignore fields, that is, regardless of password No query condition is added to any value 
    Example<User> example = Example.of(user ,matcher);
    List<User> list = userRepository.findAll(example);
    System.out.println(list);
}

The printed sql statement is as follows: 


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

The parameters are as follows:

2018-03-24 13:26:57.425 TRACE 5880 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [y%]
2018-03-24 13:26:57.425 TRACE 5880 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [VARCHAR] - [%sh%]

Supplement

Official Creation of ExampleMatcher Example (1.8 lambda) 


ExampleMatcher matcher = ExampleMatcher.matching()
  .withMatcher("firstname", match -> match.endsWith())
  .withMatcher("firstname", match -> match.startsWith());
}

StringMatcher Parameters

Matching 生成的语句 说明
DEFAULT (case-sensitive) firstname = ?0 默认（大小写敏感）
DEFAULT (case-insensitive) LOWER(firstname) = LOWER(?0) 默认（忽略大小写）
EXACT (case-sensitive) firstname = ?0 精确匹配（大小写敏感）
EXACT (case-insensitive) LOWER(firstname) = LOWER(?0) 精确匹配（忽略大小写）
STARTING (case-sensitive) firstname like ?0 + ‘%' 前缀匹配（大小写敏感）
STARTING (case-insensitive) LOWER(firstname) like LOWER(?0) + ‘%' 前缀匹配（忽略大小写）
ENDING (case-sensitive) firstname like ‘%' + ?0 后缀匹配（大小写敏感）
ENDING (case-insensitive) LOWER(firstname) like ‘%' + LOWER(?0) 后缀匹配（忽略大小写）
CONTAINING (case-sensitive) firstname like ‘%' + ?0 + ‘%' 模糊查询（大小写敏感）
CONTAINING (case-insensitive) LOWER(firstname) like ‘%' + LOWER(?0) + ‘%' 模糊查询（忽略大小写）

Description:

1. By default (withIgnoreCase () is not called) is case sensitive.

2. There is also an regex in api, but I tested and reported errors under mysql, so I don't know the specific function.

Summarize

Through the use of springdata jpa can be through Example to quickly achieve dynamic query, while with Pageable can achieve fast paging query function.

For non-string attributes, you can only match exactly. For example, if you want to query the user information registered in a certain period of time, you cannot query it through Example

Related articles: