条件对的In子句 [英] In clause for a list of pair of conditions

问题描述

有一张表，我需要在其中通过应用和条件来获取配对值列表中的分页记录，以下为说明

There is a table from where I need to fetch paginated records by applying and condition in a list of paired values, Below is the explanation

让我们说我有一个课程Billoflading，其中有很多字段 表格中的两个重要字段是

Lets say I have a class Billoflading and there are various fields in it The two important fields in the table are

1. tenant
2. billtype

1. tenant
2. billtype

我有一个包含

[
   {`tenant1`, `billtype1`},
   {`tenant2`, `billtype2`},
   {`tenant3`, `billtype3`},
   ....
  ]

我需要一个JPA查询，其中的提取将类似于 findByTenantAndBilltypeOrTenantAndBillTypeOr .....

I need a JPA query where the fetch will be like findByTenantAndBilltypeOrTenantAndBillTypeOr.....

在简单的sql查询中，它将类似于

in simple sql query it will be like

Select * from `Billoflading` where 
`tenant` = 'tenant1' and billtype = 'billtype1'
OR `tenant` = 'tenant2' and billtype = 'billtype2'
OR `tenant` = 'tenant3' and billtype = 'billtype3'
OR ......... so on..

我尝试如下编写JPA查询

I tried writing a JPA query as follows

Page<Billoflading> findByTenantInAndBillTypeIn(List<String> tenants, List<String> billTypes, Page page);

但是这也有交叉记录 即它给出了tenant1和billtype2，benant2和billtype 3的记录，依此类推...在结果集中不需要这些记录

but this had crossover records as well i.e it gave records for tenant1 and billtype2, benant2 and billtype 3 so on... which are not needed in the result set

任何人都可以解决这个问题，并帮助我找到一个简单的解决方案，例如

can anyone please solve this and help me finding a simple solution like

Page<Billoflading> findByTenantAndBillTypeIn(Map<String, String> tenantsAndBilltyes, Page page);

我也已经准备好在JPA中进行本机查询，我所需要的是应该没有交叉，因为这是一个非常敏感的数据

I am also ready for the native queries in JPA all I need is there should be no crossovers as this is a very sensitive data

我遇到的另一种解决方法是获取记录并应用Java 8过滤器，但没有.页面中的记录数量减少了

The other workaround I had was fetching the records and applying java 8 filters and that works but the no. of records in a page gets reduced

推荐答案

Section 4.6.9 of the JPA specification makes it clear that this is not supported by JPQL, at least not in the form of an in-clause:

4.6.9在表达式中 在条件表达式中使用比较运算符[NOT] IN的语法如下:

4.6.9 In Expressions The syntax for the use of the comparison operator [NOT] IN in a conditional expression is as follows:

in_expression ::=
    {state_valued_path_expression | type_discriminator} [NOT] IN
    { ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter } 

in_item ::= literal | single_valued_input_parameter

state_valued_pa​​th_expression必须具有字符串，数字，日期，时间，时间戳或枚举值.

The state_valued_path_expression must have a string, numeric, date, time, timestamp, or enum value.

文字和/或输入参数值必须类似于state_valued_pa​​th_expression类型的相同抽象模式类型. (请参阅第4.12节).

The literal and/or input parameter values must be like the same abstract schema type of the state_valued_path_expression in type. (See Section 4.12).

子查询的结果必须类似于state_valued_pa​​th_expression类型的相同抽象模式类型.

The results of the subquery must be like the same abstract schema type of the state_valued_path_expression in type.

它对元组不起作用.

您最好的选择是创建一个Specification，以构造所需的AND和OR的组合.请参阅此博客文章如何创建Specifications

Your best bet is probably to create a Specification to construct the combination of AND and OR you require. See this blog article how to create Specifications

这篇关于条件对的In子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！