jpa search helper
3.3.0
用于在 Spring Boot 中使用 JPA 构建和运行高级动态查询的库。
Map<String, String> ，支持带有查询参数的 GET 端点。通过jpa-search-helper，您的控制器* 将能够接收如下请求：
模式1 ：
curl -X GET
' https://myexampledomain.com/persons?
firstName=Biagio
&lastName_startsWith=Toz
&birthDate_gte=19910101
&country_in=IT,FR,DE
&address_eq=Via Roma 1,Via Milano/,1,20 West/,34th Street
&company.name_in=Microsoft,Apple,Google
&company.employees_between=500,5000 '模式2 ：
curl -X POST -H " Content-type: application/json " -d ' {
"filter" : {
"operator": "and", // the first filter must contain a root operator: AND, OR or NOT
"filters" : [
{
"operator": "eq",
"key": "firstName",
"value": "Biagio"
},
{
"operator": "or",
"filters": [
{
"operator": "startsWith",
"key": "lastName",
"value": "Toz",
"options": {
"ignoreCase": true
}
},
{
"operator": "endsWith",
"key": "lastName",
"value": "ZZI",
"options": {
"ignoreCase": true,
"trim" : true
}
}
]
},
{
"operator": "in",
"key": "company.name",
"values": ["Microsoft", "Apple", "Google"]
},
{
"operator": "or",
"filters": [
{
"operator": "gte",
"key": "birthDate",
"value": "19910101"
},
{
"operator": "lte",
"key": "birthDate",
"value": "20010101"
}
]
},
{
"operator": "between",
"key" : "company.employees",
"values": [500, 5000],
"options": {
"negate": true
}
}
]
},
"options": {
"pageSize": 10,
"pageOffset": 0,
"sortKey": "birthDate",
"sortDesc": false
}
} ' ' https://myexampledomain.com/persons '..你是怎么做到的？阅读此自述文件！
*请注意：该库不公开控制器/HTTP 端点，而仅提供将构建和执行查询的存储库。
| JPA 搜索助手 | 春季启动 | 爪哇 |
|---|---|---|
| [v0.0.1 - v2.1.1] | 3.2.x | [17 - 23] |
| [v3.0.0 - v3.2.2] | 3.3.x | [17 - 23] |
| [v3.3.0 - 最新] | 3.4.x | [17 - 23] |
<dependency>
<groupId>app.tozzi</groupId>
<artifactId>jpa-search-helper</artifactId>
<version>3.3.0</version>
</dependency>
implementation 'app.tozzi:jpa-search-helper:3.3.0
@Searchable注解首先将@Searchable注释应用到域模型中的字段，或者应用到 JPA 实体中您想要可供搜索的字段。如果您想要在其他对象中搜索某些字段，请使用@NestedSearchable注释这些字段。
@ Data
public class Person {
@ Searchable
private String firstName ;
@ Searchable
private String lastName ;
@ Searchable ( entityFieldKey = "dateOfBirth" )
private Date birthDate ;
@ Searchable
private String country ;
@ Searchable
private String fillerOne ;
@ Searchable
private String fillerTwo ;
@ NestedSearchable
private Company company ;
@ Data
public static class Company {
@ Searchable ( entityFieldKey = "companyEntity.name" )
private String name ;
@ Searchable ( entityFieldKey = "companyEntity.employeesCount" )
private int employees ;
}
}该注释允许您指定：
核心属性：
entityFieldKey ：实体 bean 上定义的字段名称（如果在实体 bean 上使用注释，则无需指定）。如果未指定，键将是字段名称。
targetType ：实体的托管对象类型。如果未指定，库会尝试根据字段类型获取它（例如，没有目标类型定义的整数字段将为INTEGER ）。如果没有与管理的类型兼容的类型，它将作为字符串进行管理。托管类型：
STRING 、 INTEGER 、 DOUBLE 、 FLOAT 、 LONG BIGDECIMAL 、 BOOLEAN十进制、布尔型、 DATE 、 LOCALDATE 、本地LOCALDATETIME 、本地LOCALTIME 、 OFFSETDATETIME日期时间、 OFFSETTIME 、 ZONEDDATETIME 。验证属性：
datePattern ：仅适用于DATE 、 LOCALDATE 、 LOCALDATETIME 、 LOCALTIME 、 OFFSETDATETIME 、 OFFSETTIME 、 ZONEDDATETIME目标类型。定义要使用的日期模式。maxSize, minSize ：值的最大/最小长度。maxDigits, minDigits ：仅适用于数字类型。最大/最小位数。regexPattern ：正则表达式模式。decimalFormat ：仅适用于十进制数字类型。默认#.##其他：
sortable ：如果为 false，则该字段可以用于搜索，但不能用于排序。默认值：true。trim ：应用修剪。tags ：如果域模型字段可以对应于多个实体字段，则很有用（该示例可在下面找到）。allowedFilters ：专门允许的过滤器。notAllowedFilters ：不允许的过滤器。likeFilters ：允许类似的过滤器（ contains 、 startsWith 、 endsWith ）。默认值：true。继续这个例子，我们的实体类：
@ Entity
@ Data
public class PersonEntity {
@ Id
private Long id ;
@ Column ( name = "FIRST_NAME" )
private String firstName ;
@ Column ( name = "LAST_NAME" )
private String lastName ;
@ Column ( name = "BIRTH_DATE" )
private Date dateOfBirth ;
@ Column ( name = "COUNTRY" )
private String country ;
@ Column ( name = "FIL_ONE" )
private String fillerOne ;
@ Column ( name = "FIL_TWO" )
private String fillerTwo ;
@ OneToOne
private CompanyEntity companyEntity ;
}
@ Entity
@ Data
public class CompanyEntity {
@ Id
private Long id ;
@ Column ( name = "NAME" )
private String name ;
@ Column ( name = "COUNT" )
private Integer employeesCount ;
}JPASearchRepository接口您的 Spring JPA 存储库必须扩展JPASearchRepository<YourEntityClass> 。
@ Repository
public interface PersonRepository extends JpaRepository < PersonEntity , Long >, JPASearchRepository < PersonEntity > {
}在您的经理中，或在您的服务中，或您想要使用存储库的任何地方：
模式 1 ：定义一个映射<filter_key#options, value> ：
// ...
@ Autowired
private PersonRepository personRepository ;
public List < Person > advancedSearch () {
// Pure example, in real use case it is expected that these filters can be passed directly by the controller
Map < String , String > filters = new HashMap <>();
filters . put ( "firstName_eq" , "Biagio" );
filters . put ( "lastName_startsWith#i" , "Toz" ); // ignore case
filters . put ( "birthDate_gte" , "19910101" );
filters . put ( "country_in" , "IT,FR,DE" );
filters . put ( "company.name_eq#n" , "Bad Company" ); // negation
filters . put ( "company.employees_between" , "500,5000" );
filters . put ( "fillerOne_null#n" , "true" ); // not null
filters . put ( "fillerTwo_empty" , "true" ); // empty
// Without pagination
List < PersonEntity > fullSearch = personRepository . findAll ( filters , Person . class );
filters . put ( "birthDate_sort" : "ASC" ); // sorting key and sorting order
filters . put ( "_limit" , "10" ); // page size
filters . put ( "_offset" , "0" ); // page offset
// With pagination
Page < PersonEntity > sortedAndPaginatedSearch = personRepository . findAllWithPaginationAndSorting ( filters , Person . class );
// ...
}
// ...模式 2 ：您需要使用JPASearchInput代替地图，为简单起见，此处显示为 JSON 格式。
{
"filter" : {
"operator" : " and " , // the first filter must contain a root operator: AND, OR or NOT
"filters" : [
{
"operator" : " eq " ,
"key" : " firstName " ,
"value" : " Biagio "
},
{
"operator" : " or " ,
"filters" : [
{
"operator" : " startsWith " ,
"key" : " lastName " ,
"value" : " Toz " ,
"options" : {
"ignoreCase" : true
}
},
{
"operator" : " endsWith " ,
"key" : " lastName " ,
"value" : " ZZI " ,
"options" : {
"ignoreCase" : true ,
"trim" : true
}
}
]
},
{
"operator" : " in " ,
"key" : " company.name " ,
"values" : [ " Microsoft " , " Apple " , " Google " ]
},
{
"operator" : " or " ,
"filters" : [
{
"operator" : " gte " ,
"key" : " birthDate " ,
"value" : " 19910101 "
},
{
"operator" : " lte " ,
"key" : " birthDate " ,
"value" : " 20010101 "
}
]
},
{
"operator" : " empty " ,
"key" : " fillerOne " ,
"options" : {
"negate" : true
}
},
{
"operator" : " between " ,
"key" : " company.employees " ,
"values" : [ 500 , 5000 ],
"options" : {
"negate" : true
}
}
]
},
"options" : {
"pageSize" : 10 ,
"pageOffset" : 0 ,
"sortKey" : " birthDate " ,
"sortDesc" : false
}
}通过模式 2，可以使用AND 、 OR和NOT来管理复杂的过滤器（见下文）。
InvalidFieldException 。InvalidValueException 。JPASearchException | 过滤器名称 | 图书馆钥匙 | 支持的模式 |
|---|---|---|
| 和 | 和 | 1, 2 |
| 或者 | 或者 | 2 |
| 不是 | 不是 | 2 |
通过模式 1 ，所有过滤器仅组成AND搜索。
要使用其他运算符OR和NOT ，您必须使用模式 2
| 过滤器名称 | 图书馆钥匙 | SQL | 支持的模式 | 所需值 |
|---|---|---|---|---|
| 等于 | 情商 | sql_col = 值 | 1,2 | 是的 |
| 包含 | 包含 | sql_col LIKE '%val%' | 1,2 | 是的 |
| 在 | 在 | sql_col IN (val1, val2, ..., valN) | 1,2 | 是的 |
| 开始于 | 开始于 | sql_col LIKE 'val%' | 1,2 | 是的 |
| 结束于 | 结束于 | sql_col LIKE '%val' | 1,2 | 是的 |
| 大于 | GT | sql_col > val | 1,2 | 是的 |
| 大于或等于 | 通用电气 | sql_col >= 值 | 1,2 | 是的 |
| 少于 | 其 | sql_col < 值 | 1,2 | 是的 |
| 小于或等于 | LTE | sql_col <= val | 1,2 | 是的 |
| 之间 | 之间 | val1 和 val2 之间的 sql_col | 1,2 | 是的 |
| 无效的 | 无效的 | sql_col 为空 | 1,2 | 不 |
| 空的 | 空的 | sql_collection_col 为 NULL | 1,2 | 不 |
模式1
| 选项说明 | 图书馆钥匙 |
|---|---|
| 忽略大小写 | ＃我 |
| 否定 | #n |
| 修剪 | #t |
选项键必须附加到过滤器中；例如?firstName_eq#i=Biagio或?firstName_eq#i#n=Biagio
模式2
| 选项说明 | 库密钥（Java 属性） |
|---|---|
| 忽略大小写 | 忽略大小写 |
| 否定 | 否定 |
| 修剪 | 修剪 |
对于每个过滤器，可以定义options
{
// ...
{
"operator" : " eq " ,
"key" : " firstName " ,
"value" : " Biagio " ,
"options" : {
"ignoreCase" : true ,
"trim" : false ,
"negate" : true
}
}
// ...
}Java对象：
@ Data
public static class JPASearchFilterOptions {
private boolean ignoreCase ;
private boolean trim ;
private boolean negate ;
} | 过滤器名称 | 钥匙 | 固定值 |
|---|---|---|
| 限制（页面大小） | 限制 | |
| 偏移量（页码） | 抵消 | |
| 种类 | 种类 | 上升、下降 |
模式1 ：例如？firstName_sort=DESC&_limit=10&_offset=0
模式2 ：值根options ：
{
"filter" : {
// ...
},
"options" : {
"sortKey" : " firstName " ,
"sortDesc" : true ,
"pageSize" : 10 ,
"pageOffset" : 1
}
}Java对象：
@ Data
public static class JPASearchOptions {
private String sortKey ;
private Boolean sortDesc = false ;
private Integer pageSize ;
private Integer pageOffset ;
private List < String > selections ;
}, : 例如?myField_in=test1,test2 --> 要搜索的值：[" test1 ", " test2 "]/, : 例如?myField_in=test1,test2/,test3 --> 要搜索的值：[" test1 ", " test2,test3 "] @Projectable注解首先将@Projectable注释应用于您想要可供选择的域模型或 JPA 实体中的字段。如果您想要在其他对象中选择某些字段，请使用@NestedProjectable注释这些字段。
@ Data
public class Person {
@ Searchable
private String firstName ;
@ Projectable
@ Searchable
private String lastName ;
@ Projectable ( entityFieldKey = "dateOfBirth" )
@ Searchable ( entityFieldKey = "dateOfBirth" )
private Date birthDate ;
@ Searchable
private String country ;
@ Searchable
private String fillerOne ;
@ Searchable
private String fillerTwo ;
@ NestedProjectable
@ NestedSearchable
private Company company ;
@ Data
public static class Company {
@ Searchable ( entityFieldKey = "companyEntity.name" )
private String name ;
@ Projectable ( entityFieldKey = "companyEntity.employeesCount" )
@ Searchable ( entityFieldKey = "companyEntity.employeesCount" )
private int employees ;
}
}该注释允许您指定：
核心属性：
entityFieldKey ：实体 bean 上定义的字段名称（如果在实体 bean 上使用注释，则无需指定）。如果未指定，键将是字段名称。JPASearchRepository接口您的 Spring JPA 存储库必须扩展JPAProjectionRepository<YourEntityClass> 。
@ Repository
public interface PersonRepository extends JpaRepository < PersonEntity , Long >, JPASearchRepository < PersonEntity >, JPAProjectionRepository < PersonEntity > {
} 在您的经理中，或在您的服务中，或您想要使用存储库的任何地方：
模式 1 ：定义（或添加到用于模式 1 搜索的地图）地图：
, // ...
@ Autowired
private PersonRepository personRepository ;
public List < Person > advancedSearch () {
// Pure example, in real use case it is expected that these filters can be passed directly by the controller
Map < String , String > filters = new HashMap <>();
filters . put ( "firstName_eq" , "Biagio" );
filters . put ( "lastName_startsWith#i" , "Toz" ); // ignore case
filters . put ( "birthDate_gte" , "19910101" );
filters . put ( "country_in" , "IT,FR,DE" );
filters . put ( "company.name_eq#n" , "Bad Company" ); // negation
filters . put ( "company.employees_between" , "500,5000" );
filters . put ( "fillerOne_null#n" , "true" ); // not null
filters . put ( "fillerTwo_empty" , "true" ); // empty
// Selections
filters . put ( "selections" , "lastName,birthDate,company.employees" );
// Without sorting
List < Map < String , Object >> result = personRepository . projection ( filters , Person . class , PersonEntity . class );
filters . put ( "birthDate_sort" : "ASC" ); // sorting key and sorting order
// With sorting
List < Map < String , Object >> sortedAndPaginatedSearch = personRepository . projectionWithSorting ( filters , Person . class , PersonEntity . class );
// ... convert the list of maps into your model
}
// ...模式 2 ：您需要使用JPASearchInput代替地图，为简单起见，此处显示为 JSON 格式。
{
"filter" : {
"operator" : " and " , // the first filter must contain a root operator: AND, OR or NOT
"filters" : [
{
"operator" : " eq " ,
"key" : " firstName " ,
"value" : " Biagio "
},
{
"operator" : " or " ,
"filters" : [
{
"operator" : " startsWith " ,
"key" : " lastName " ,
"value" : " Toz " ,
"options" : {
"ignoreCase" : true
}
},
{
"operator" : " endsWith " ,
"key" : " lastName " ,
"value" : " ZZI " ,
"options" : {
"ignoreCase" : true ,
"trim" : true
}
}
]
},
{
"operator" : " in " ,
"key" : " company.name " ,
"values" : [ " Microsoft " , " Apple " , " Google " ]
},
{
"operator" : " or " ,
"filters" : [
{
"operator" : " gte " ,
"key" : " birthDate " ,
"value" : " 19910101 "
},
{
"operator" : " lte " ,
"key" : " birthDate " ,
"value" : " 20010101 "
}
]
},
{
"operator" : " empty " ,
"key" : " fillerOne " ,
"options" : {
"negate" : true
}
},
{
"operator" : " between " ,
"key" : " company.employees " ,
"values" : [ 500 , 5000 ],
"options" : {
"negate" : true
}
}
]
},
"options" : {
"pageSize" : 10 ,
"pageOffset" : 0 ,
"sortKey" : " birthDate " ,
"sortDesc" : false ,
"selections" : [
" lastName " ,
" birthDate " ,
" company.employees "
]
}
}对于这两种模式，投影将返回 List<Map<String, Object>> 结果，其中映射结构和键将反映实体结构（需要明确toJson(entityList) == toJson(mapList) ）
注1：
请注意：默认投影强制所有连接关系为 LEFT JOIN。如果您不希望出现此行为，请选择使用存储库方法（带有“Classic”后缀的方法），该方法允许您仅修改要修改的关系
注2：
投影，无论您是否想要，都将始终提取代表实体（或相关实体）主键的字段
注3：
不支持分页
InvalidFieldException 。JPASearchException 可以强制使用 fetch 进行联接，以允许 Hibernate（或您的 JPA 框架）对实体上定义的关系执行单个查询。这只有在没有分页的情况下才有可能：
// ...
Map < String , JoinFetch > fetches = Map . of ( "companyEntity" , JoinFetch . LEFT );
personRepository . findAll ( filters , Person . class , fetches );
// ...如果你有一个领域模型是多个实体转换的结果，那么可以显式指定一个映射（字符串，字符串），其键代表领域模型字段的名称，值是领域模型字段的名称要搜索的实体：
// ...
Map < String , String > entityFieldMap = Map . of ( "company" , "companyEntity.name" );
// Without pagination
personRepository . findAll ( filters , Person . class , fetches , entityFieldMap );
// With pagination
personRepository . findAllWithPaginationAndSorting ( filters , Person . class , entityFieldMap );
// ...另一个特殊情况可能是一个对象可以在域模型中重复以表示实体的多个部分。搜索的解决方案：
@ Entity
public class CoupleEntity {
@ Id
private Long id ;
@ Column ( name = "p1_fn" )
private String p1FirstName ;
@ Column ( name = "p1_ln" )
private String p1LastName ;
@ Column ( name = "p2_fn" )
private String p2FirstName ;
@ Column ( name = "p2_ln" )
private String p2LastName ;
}
@ Data
public class Couple {
@ NestedSearchable
private Person p1 ;
@ NestedSearchable
private Person p2 ;
@ Data
public static class Person {
@ Searchable ( tags = {
@ Tag ( fieldKey = "p1.firstName" , entityFieldKey = "p1FirstName" ),
@ Tag ( fieldKey = "p2.firstName" , entityFieldKey = "p2FirstName" ),
})
private String firstName ;
@ Searchable ( tags = {
@ Tag ( fieldKey = "p1.lastName" , entityFieldKey = "p1LastName" ),
@ Tag ( fieldKey = "p2.lastName" , entityFieldKey = "p2LastName" ),
})
private String lastName ;
}
}curl - request GET
- url ' https://www.myexampledomain.com/couples?
p1.firstName_iEq=Romeo
&p2.firstName_iEq=Giulietta ' 请注意：该库不公开任何端点，因此没有控制器。此处提供了详尽且完整的示例项目。
控制器：
@ RestController
@ RequestMapping ( "/persons" )
public class PersonController {
@ Autowired
private PersonManager personManager ;
@ GetMapping ( produces = MediaType . APPLICATION_JSON_VALUE )
public List < Person > findPersons ( @ RequestParam Map < String , String > requestParams ) {
return personManager . find ( requestParams );
}
@ GetMapping ( path = "/projection" , produces = MediaType . APPLICATION_JSON_VALUE )
public List < Person > projection ( @ RequestParam Map < String , String > requestParams ) {
return personManager . projection ( requestParams );
}
}服务/管理器 bean：
@ Service
public class PersonManager {
@ Autowired
private PersonRepository personRepository ;
public List < Person > find ( Map < String , String > filters ) {
return personRepository . findAllWithPaginationAndSorting ( filters , Person . class ). stream (). map ( this :: toModel ). toList ();
}
public List < Person > projection ( Map < String , String > filters ) {
return personRepository . projection ( filters , Person . class , PersonEntity . class ). stream (). map ( this :: toModel ). toList ();
}
private static Person toModel ( PersonEntity personEntity ) {
// ...
}
private static Person toModel ( Map < String , Object > entityMap ) {
// ...
}
}卷曲：
curl - X GET
' http://localhost:8080/persons?
firstName=Biagio
&lastName_startsWith=Toz
&birthDate_gte=19910101
&country_in=IT,FR,DE
&company.name_in=Microsoft,Apple
&company.employees_between=500,5000 '或者
curl - X GET
' http://localhost:8080/persons/projection?
firstName=Biagio
&lastName_startsWith=Toz
&birthDate_gte=19910101
&country_in=IT,FR,DE
&company.name_in=Microsoft,Apple
&company.employees_between=500,5000
&selections=firstName,birthDate ' 控制器：
@ RestController
@ RequestMapping ( "/persons" )
@ Validated
public class PersonController {
@ Autowired
private PersonManager personManager ;
@ PostMapping ( produces = MediaType . APPLICATION_JSON_VALUE , consumes = MediaType . APPLICATION_JSON_VALUE )
public List < Person > findPersons ( @ Valid @ RequestBody JPASearchInput input ) {
return personManager . find ( input );
}
}
@ PostMapping ( path = "/projection" , produces = MediaType . APPLICATION_JSON_VALUE , consumes = MediaType . APPLICATION_JSON_VALUE )
public List < Person > projection ( @ Valid @ RequestBody JPASearchInput input ) {
return personManager . projection ( input );
}
}服务/管理器 bean：
@ Service
public class PersonManager {
@ Autowired
private PersonRepository personRepository ;
public List < Person > find ( JPASearchInput input ) {
return personRepository . findAllWithPaginationAndSorting ( input , Person . class ). stream (). map ( this :: toModel ). toList ();
}
public List < Person > find ( JPASearchInput input ) {
return personRepository . projection ( input , Person . class , PersonEntity . class ). stream (). map ( this :: toModel ). toList ();
}
private static Person toModel ( PersonEntity entity ) {
// ...
}
private static Person toModel ( Map < String , Object > entityMap ) {
// ...
}
}卷曲：
curl -X POST -H " Content-type: application/json " -d ' {
"filter" : {
"operator": "and", // the first filter must contain a root operator: AND, OR or NOT
"filters" : [
{
"operator": "eq",
"key": "firstName",
"value": "Biagio"
},
{
"operator": "or",
"filters": [
{
"operator": "startsWith",
"key": "lastName",
"value": "Toz",
"options": {
"ignoreCase": true
}
},
{
"operator": "endsWith",
"key": "lastName",
"value": "ZZI",
"options": {
"ignoreCase": true,
"trim" : true
}
}
]
},
{
"operator": "in",
"key": "company.name",
"values": ["Microsoft", "Apple", "Google"]
},
{
"operator": "or",
"filters": [
{
"operator": "gte",
"key": "birthDate",
"value": "19910101"
},
{
"operator": "lte",
"key": "birthDate",
"value": "20010101"
}
]
},
{
"operator": "between",
"key" : "company.employees",
"values": [500, 5000],
"options": {
"negate": true
}
}
]
},
"options": {
"pageSize": 10,
"pageOffset": 0,
"sortKey": "birthDate",
"sortDesc": false
}
} ' ' http://localhost:8080/persons '或者
curl -X POST -H " Content-type: application/json " -d ' {
"filter" : {
"operator": "and", // the first filter must contain a root operator: AND, OR or NOT
"filters" : [
{
"operator": "eq",
"key": "firstName",
"value": "Biagio"
},
{
"operator": "or",
"filters": [
{
"operator": "startsWith",
"key": "lastName",
"value": "Toz",
"options": {
"ignoreCase": true
}
},
{
"operator": "endsWith",
"key": "lastName",
"value": "ZZI",
"options": {
"ignoreCase": true,
"trim" : true
}
}
]
},
{
"operator": "in",
"key": "company.name",
"values": ["Microsoft", "Apple", "Google"]
},
{
"operator": "or",
"filters": [
{
"operator": "gte",
"key": "birthDate",
"value": "19910101"
},
{
"operator": "lte",
"key": "birthDate",
"value": "20010101"
}
]
},
{
"operator": "between",
"key" : "company.employees",
"values": [500, 5000],
"options": {
"negate": true
}
}
]
},
"options": {
"sortKey": "birthDate",
"sortDesc": false,
"selections" : [
"birthDate",
"firstName",
"lastName"
]
}
} ' ' http://localhost:8080/persons/projection '
