java - JPA repository query that returns all rows with a nth-to-nth relation - Stack Overflow

admin管理员组

文章数量:1334398

I have a table that looks like this:

id	A	B
1	A1	B1
2	A1	B2
3	A2	B2
4	A2	B3

I have a table that looks like this:

id	A	B
1	A1	B1
2	A1	B2
3	A2	B2
4	A2	B3

Then I have a list of items for which I want to find the corresponding database rows. For instance, i might have (in the form of JSON)

[
 {
  "A": "A1",
  "B": "B4",
 },
 {
  "A": "A1",
  "B": "B2",
 }
]

This should now return the rows with Id 2. These items for which I want to query the database can be thousands.

Naively, I came up with

findAllByAinAndBIn(List<A> aValues, List<B> bValues)

but that doesn't work since this doesn't find the rows with a nth element to nth element comparison. What should i do instead?

• java
• spring
• postgresql
• jpa
• spring-data-jpa

Share Improve this question Follow asked Nov 20, 2024 at 16:28 SCMSCM 8566 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

The simplest approach would be to create a concatenated list of ids and then compare them against the database using a JPQL query.

1. Create a concatenated list of the ids

 List<String> matches = jsonArrayList.parallelStream().map(e -> e.getA() + ":" + movie.getB()).toList();

2. Create a JPQL query to compare the concatenated ids against the database

@Query("select m from Entity m where CONCAT(m.a, ':', m.b) IN (:matches)")
List<Entity> findAllMatchingRows(@Param("matches") List<String> matches);

The following query demonstrates an approach using a JSON array of search terms:

WITH
  entity(id, a, b) AS (
    VALUES
      (1, 'A1', 'B1'),
      (2, 'A1', 'B2'),
      (3, 'A2', 'B2'),
      (4, 'A2', 'B3')
  )
SELECT
  entity.*
FROM
  entity JOIN JSONB_ARRAY_ELEMENTS('[{"A": "A1", "B": "B4"}, {"A": "A1", "B": "B2"}]'::JSONB) j(criteria)
  ON (entity.a, entity.b) = (j.criteria ->> 'A', j.criteria ->> 'B');

The following is an example using a JPA native query:

@Query(value = "SELECT entity.* FROM entity JOIN JSONB_ARRAY_ELEMENTS(:criteria) j(criteria) ON (entity.a, entity.b) = (j.criteria ->> 'A', j.criteria ->> 'B')", nativeQuery = true)
Entity findEntityByAAndB(@Param("criteria") String "[{\"A\": \"A1\", \"B\": \"B4\"}, {\"A\": \"A1\", \"B\": \"B2\"}]");

本文标签： javaJPA repository query that returns all rows with a nthtonth relationStack Overflow