Как заменить оператор IN на JOIN в динамических критериях с использованием спецификации JPA? ⇐ JAVA

[Anonymous]

Мне нужно создать критерии динамического фильтра на основе полученных мной фильтров. Первоначально я использовал подход спецификации, и в рабочей среде он работает должным образом. Однако новое требование заключается в том, что мне нужно вызвать функцию SQL и использовать выходные данные для построения критериев.
Чтобы добиться этого, я использовал собственный подход к запросу через репозиторий JPA для вызова функцию SQL и подготовьте список городов и штатов на основе местоположения источника и назначения. Я передал результат оператору IN, но при вызове функции SQL получил следующее исключение: Причина: com.microsoft.sqlserver.jdbc.SQLServerException: во входящем запросе слишком много параметров. Сервер поддерживает максимум 2100 параметров. Уменьшите количество параметров и повторите запрос.
Мне нужно решение, которое интегрируется с моим существующим кодом, чтобы удовлетворить этому новому требованию, не нарушая построение динамических критериев для других фильтров.
Запросить полезную нагрузку:

Код: Выделить всё

{
"pageNumber": 1,
"pageSize": 20,
"laneFilters": [
{
"fieldName": "destinationCity",
"fieldType": "Text",
"operation": "Equals",
"valueOne": "Chicago",
"values": null
},
{
"fieldName": "destinationState",
"fieldType": "Text",
"operation": "Equals",
"valueOne": "IL",
"values": null
},
{
"fieldName": "originCity",
"fieldType": "Text",
"operation": "Equals",
"valueOne": "Atlanta",
"values": null
},
{
"fieldName": "originState",
"fieldType": "Text",
"operation": "Equals",
"valueOne": "GA",
"values": null
},
{
"fieldName": "modeName",
"fieldType": "Text",
"operation": "Contains",
"valueOne": "tl",
"values": null
}
],
"carrierId": null
}

Код уровня обслуживания:

Код: Выделить всё

@Override
public List getAllLanes(LanePageableDTO lanePageable) throws ServiceException {
Map fieldNameAndValueMap = processCarrierFilters(lanePageable.getLaneFilters());
Map cityStatePartitionedMap = getCityAndStatePartitionedData(
Optional.ofNullable(fieldNameAndValueMap.get(Constants.FIELD_NAME_ORIGIN_CITY)).orElse(null),
Optional.ofNullable(fieldNameAndValueMap.get(Constants.FIELD_NAME_ORIGIN_STATE)).orElse(null),
Optional.ofNullable(fieldNameAndValueMap.get(Constants.FIELD_NAME_DESTINATION_CITY)).orElse(null),
Optional.ofNullable(fieldNameAndValueMap.get(Constants.FIELD_NAME_DESTINATION_STATE)).orElse(null)
);
LaneFilterSpecification filterSpecification = new LaneFilterSpecification(
lanePageable.getLaneFilters(),
lanePageable.getCarrierId(), cityStatePartitionedMap
);
return laneRepository.findAll(filterSpecification);
}

Логика вызова функций SQL и построения данных:
Это просто объект для справочной таблицы, которого нет в базе данных. Я использую

Код: Выделить всё

SQL Plus

[/b] как база данных.

Код: Выделить всё

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ZipCodeEntity implements Serializable {

private static final long serialVersionUID = 3365453943900329675L;

@Id
@Column(name = "Zip")
private String zip;

@Column(name = "LocationType")
private String locationType;

@Column(name = "Radius")
private Integer radius;

@Column(name = "City")
private String city;

@Column(name = "StateCode")
private String stateCode;
}

Код: Выделить всё

@Repository
public interface ZipCodeRepository extends JpaRepository {

@Query(value = "WITH CTE_ZipCodes AS (" + "SELECT Zip, LocationType, Radius, City, StateCode "
+ "FROM [dbo].[GetZipCodeWithinRadius](0, :callerScreen, :srcCity, :srcState, :dstCity, :dstState, 150, 150)"
+ ") SELECT * FROM CTE_ZipCodes", nativeQuery = true)
List findZipCodesWithinRadius(@Param("callerScreen") String callerScreen,
@Param("srcCity") String srcCity, @Param("srcState") String srcState, @Param("dstCity") String dstCity,
@Param("dstState") String dstState);
}

Вывод функции SQL:


Разделенные данные о городе и штате Логика построения:

Код: Выделить всё

private Map processCarrierFilters(List carrierPreferredLaneFilters) {
// Create a map to retrieve specific values based on fieldName
return carrierPreferredLaneFilters.stream()
// Only include filters with non-null values
.filter(filter -> filter.getValueOne() != null)
.collect(Collectors.toMap(CarrierPreferredLaneFilter::getFieldName,
CarrierPreferredLaneFilter::getValueOne,
// in case of duplicates, keep the existing value
(existing, replacement) -> existing));
}

private Map getCityAndStatePartitionedData(String srcCity, String srcState, String dstCity, String dstState) {
// Fetch all records in a single query
List zipCodes = zipCodeRepository.findZipCodesWithinRadius(
"Lane", srcCity, srcState, dstCity, dstState
);

// Partition the data and extract city and state lists in one go
return zipCodes.stream()
.collect(Collectors.partitioningBy(
zipCode -> "Source".equalsIgnoreCase(zipCode.getLocationType()),
Collectors.collectingAndThen(
Collectors.toList(),
list -> Map.of(
"cities", list.stream().map(ZipCodeEntity::getCity).collect(Collectors.toList()),
"states", list.stream().map(ZipCodeEntity::getStateCode).collect(Collectors.toList())
)
)
));
}

Логика спецификации (частичная):

Код: Выделить всё

public class LaneFilterSpecification implements Specification {

private static final long serialVersionUID = -5817708002524725894L;

private List laneFilters;

private Long carrierId;

private Map cityStatePartitionedMap;

public LaneFilterSpecification(List laneFilters,
Long carrierId, Map cityStatePartitionedMap) {
super();
this.laneFilters = laneFilters;
this.carrierId = carrierId;
this.cityStatePartitionedMap = cityStatePartitionedMap;
}

@Override
public Predicate toPredicate(Root root, CriteriaQuery query,
CriteriaBuilder criteriaBuilder) {
List
  predicates = new ArrayList();
if (nonNull(laneFilters) && !laneFilters.isEmpty()) {
for (LaneFilter laneFilter : laneFilters) {

String operation = StringUtils.isNotBlank(laneFilter.getOperation())
? laneFilter.getOperation().toLowerCase()
: StringUtils.EMPTY;

switch (FilterOperationEnum.getOperation(operation)) {
case CONTAINS:
addFilterContains(predicates, root, criteriaBuilder, laneFilter);
break;
case NOT_CONTAINS:
addFilterNotContains(predicates, root, criteriaBuilder, laneFilter);
break;
case EQUALS:
addFilterEquals(predicates, root, criteriaBuilder, laneFilter);
break;
case GREATER_THAN:
addFilterGreaterThan(predicates, root, criteriaBuilder, laneFilter);
break;
case GREATER_THAN_EQUAL:
addFilterGreaterThanEquals(predicates, root, criteriaBuilder, laneFilter);
break;
case LESS_THAN:
addFilterLessThan(predicates, root, criteriaBuilder, laneFilter);
break;
case LESS_THAN_EQUAL:
addFilterLessThanEquals(predicates, root, criteriaBuilder, laneFilter);
break;
case IN_RANGE:
addFilterInRange(predicates, root, criteriaBuilder, laneFilter);
break;
case IN:
addFilterIN(predicates, root, criteriaBuilder, laneFilter);
break;
default:
break;

}
}
}

if (nonNull(carrierId)) {
predicates.add(criteriaBuilder.equal(carrierJoin(root).get(Constants.FIELD_NAME_CARRIER_ID), carrierId));
}

return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
}

private void addFilterEquals(List predicates, Root root,
CriteriaBuilder criteriaBuilder, LaneFilter laneFilter) {
if (StringUtils.isNotBlank(laneFilter.getValueOne())) {
String trimmedValueOne = laneFilter.getValueOne().trim().toUpperCase();
if (laneFilter.getFieldName().equalsIgnoreCase(Constants.FIELD_NAME_ORIGIN_CITY)) {
if (isNull(carrierId) && !cityStatePartitionedMap.isEmpty()
&& !cityStatePartitionedMap.get(Boolean.TRUE).isEmpty()
&& !cityStatePartitionedMap.get(Boolean.TRUE).get("cities").isEmpty()
&& !cityStatePartitionedMap.get(Boolean.TRUE).get("states").isEmpty()) {
addFilterIN(predicates, root, criteriaBuilder, laneFilter);
} else {
Predicate pOriginCity = criteriaBuilder
.equal(criteriaBuilder.upper(root.get(Constants.FIELD_NAME_ORIGIN_CITY)), trimmedValueOne);

Predicate pOriginState = criteriaBuilder
.equal(criteriaBuilder.upper(root.get(Constants.FIELD_NAME_ORIGIN_STATE)), trimmedValueOne);

predicates.add(criteriaBuilder.or(pOriginCity, pOriginState));
}
} else if (laneFilter.getFieldName()
.equalsIgnoreCase(Constants.FIELD_NAME_DESTINATION_CITY)) {
if (isNull(carrierId) && !cityStatePartitionedMap.isEmpty()
&& !cityStatePartitionedMap.get(Boolean.FALSE).isEmpty()
&& !cityStatePartitionedMap.get(Boolean.FALSE).get("cities").isEmpty()
&&  !cityStatePartitionedMap.get(Boolean.FALSE).get("states").isEmpty()) {
addFilterIN(predicates, root, criteriaBuilder, laneFilter);
} else {
Predicate pDestinationCity = criteriaBuilder
.equal(criteriaBuilder.upper(root.get(Constants.FIELD_NAME_DESTINATION_CITY)), trimmedValueOne);

Predicate pDestinationState = criteriaBuilder.equal(
criteriaBuilder.upper(root.get(Constants.FIELD_NAME_DESTINATION_STATE)), trimmedValueOne);

predicates.add(criteriaBuilder.or(pDestinationCity, pDestinationState));
}
} else {
if (StringUtils.isNoneBlank(laneFilter.getFieldType())) {
switch (FilterFieldTypeEnum.getFieldType(laneFilter.getFieldType().toLowerCase())) {
case TEXT:
addTextFilterEquals(predicates, root, criteriaBuilder, laneFilter);
break;
case NUMBER:
addNumberFilterEquals(predicates, root, criteriaBuilder, laneFilter);
break;
case DATE:
addDateFilterEquals(predicates, root, criteriaBuilder, laneFilter);
break;
default:
break;
}
}
}
}

}

private void addFilterIN(List predicates, Root root,
CriteriaBuilder criteriaBuilder, LaneFilter laneFilter) {

if (isNull(carrierId)) {
if (Constants.FIELD_NAME_ORIGIN_CITY.equalsIgnoreCase(laneFilter.getFieldName())) {
Predicate orgCityInPredicate = root.get(Constants.FIELD_NAME_ORIGIN_CITY)
.in(cityStatePartitionedMap.get(Boolean.TRUE).get("cities"));
Predicate orgStateInPredicate = root.get(Constants.FIELD_NAME_ORIGIN_STATE)
.in(cityStatePartitionedMap.get(Boolean.TRUE).get("states"));
predicates.add(criteriaBuilder.and(orgCityInPredicate, orgStateInPredicate));
}

if (Constants.FIELD_NAME_DESTINATION_CITY.equalsIgnoreCase(laneFilter.getFieldName())) {
Predicate orgCityInPredicate = root.get(Constants.FIELD_NAME_DESTINATION_CITY)
.in(cityStatePartitionedMap.get(Boolean.FALSE).get("cities"));
Predicate orgStateInPredicate = root.get(Constants.FIELD_NAME_DESTINATION_STATE)
.in(cityStatePartitionedMap.get(Boolean.FALSE).get("states"));

predicates.add(criteriaBuilder.and(orgCityInPredicate, orgStateInPredicate));
}
}
}

private Join carrierJoin(Root root) {
return root.join(Constants.FIELD_NAME_CARRIER, JoinType.INNER);
}
}

Проблема: В настоящее время я использую оператор IN для фильтрации по городам и штатам, но достиг предела SQL Server для параметров (2100). Чтобы избежать этой проблемы, мне нужно заменить оператор IN на JOIN в запросе SQL.
Я не предоставил весь код спецификации, поскольку он слишком длинный, но я попробовал использовать этот подход и получить вышеупомянутая ошибка.
Как я могу реорганизовать существующий код спецификации JPA, чтобы заменить оператор IN оператором JOIN на основе данных, полученных из функции SQL, и гарантировать, что построение динамических критериев для другие фильтры не затронуты?
Буду очень благодарен за оперативную помощь!
Я пробовал использовать оператор IN, как уже упоминал. выше.

Подробнее здесь: https://stackoverflow.com/questions/791 ... cification