Shulou(Shulou.com)06/02 Report--

This article will explain in detail how springdata jpa uses Example to quickly achieve dynamic query function. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

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 translator:

Query by example (QBE) is a user-friendly query technology. It allows you to create queries dynamically, and there is no need to write queries that contain field names. In fact, the example query does not need to use the query language of a specific database to write the query statement.

The composition of Example api

Probe: the instance object that contains the corresponding field.

ExampleMatcher:ExampleMatcher carries detailed information about how to match specific fields, which is equivalent to matching criteria.

Example: consists of Probe and ExampleMatcher for querying.

Limit

Attributes do not support nesting or grouping constraints, such as such a query firstname =? 0 or (firstname =? 1 and lastname =? 2)

Flexible matching only supports string types, and 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 an entity mapping:

Entity@Table (name= "t_user") @ Data@AllArgsConstructor@NoArgsConstructor@ToStringpublic 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 @ Testpublic void contextLoads () {User user = new User (); user.setUsername ("admin"); Example example = Example.of (user); List 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 null values are ignored by default in the trial Example query, which is also stated in the official documentation:

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 just defined Probe instead of ExampleMatcher because the default matcher is used when it is not passed by default. You can see the following code in the click-in method:

Static Example 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 @ Testpublic 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 matching beginning, that is, {username}% .withMatcher ("address", ExampleMatcher.GenericPropertyMatchers.contains ()) / / all fuzzy queries, that is,% {address}% .withIgnorePaths ("password") / / ignore the field, that is, no query condition Example example = Example.of (user, matcher); List list = userRepository.findAll (example); System.out.println (list);} is added regardless of the value of password.

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 o.h.type.descriptor.sql.BasicBinder 26 TRACE 57.425 5880-[main] o.h.type.descriptor.sql.BasicBinder: binding parameter [1] as [VARCHAR]-[y%]

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

Supplement the official ExampleMatcher creation example (1.8 lambda) ExampleMatcher matcher = ExampleMatcher.matching () .withMatcher ("firstname", match-> match.endsWith ()) .withMatcher ("firstname", match-> match.startsWith ()) } the statement generated by the StringMatcher parameter Matching states that DEFAULT (case-sensitive) firstname =? 0 default (case sensitive) DEFAULT (case-insensitive) LOWER (firstname) = LOWER (? 0) default (ignore case) EXACT (case-sensitive) firstname =? 0 exact match (case sensitive) EXACT (case-insensitive) LOWER (firstname) = LOWER (0) exact match (ignore case) STARTING (case-sensitive) firstname like? 0 +'% 'prefix Matching (case sensitive) STARTING (case-insensitive) LOWER (firstname) like LOWER (? 0) +'% 'prefix matching (ignore case) ENDING (case-sensitive) firstname like'%'+? 0 suffix matching (case sensitive) ENDING (case-insensitive) LOWER (firstname) like'%'+ LOWER (0) suffix matching (ignore case) CONTAINING (case-sensitive) firstname like'%'+? 0 +'% 'fuzzy query Lowercase sensitive) CONTAINING (case-insensitive) LOWER (firstname) like'%'+ LOWER (? 0) +'% 'fuzzy query (ignore case)

Description:

1. By default (no call to withIgnoreCase ()) is case-sensitive.

2. There is also a regex in api, but I test the error under mysql and do not know the specific function.

On "springdata jpa how to use Example to quickly achieve dynamic query function" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.