How in JPA create list of pairs as parameter?

Question

In my Java aplication I want to create SQL query which will in the end will be looking like this:

SELECT * FROM my_table t WHERE (t.a, t.b) IN ((a1,b1),(a2,b2),(a3,b3), ... )

How to generate it?

My code looks like this:

public List<MyEntity> getMyEntity(List<Long> alist, List<Long> blist) {

    String stringQuery = "SELECT * FROM my_table t WHERE (t.a , t.b) in (:alist, :blist)"; 

    // This is kinda how I whould like my query to look, but I guess I will generate something like:
    // SELECT * FROM my_table t WHERE (t.a, t.b) IN ((a1, a2, a3, ...), (b1, b2, b3, ...))
    // which isn't the same and it isn't what I want 

    Query query = getEntityManager().createNativeQuery(stringQuery, MyEntity.class);

    // I'm using native query because in my aplication above query is in fact much more complicated, 
    // and I can't use standard JPA query. I cut those parts here, because they aren't directly related to my problem
    // in the other way that they force to use native query.

    query.setParameter("alist", alist);
    query.setParameter("blist", blist);

    return query.getResultList();
}

a native query does not have NAMED parameters. It has positional parameters (if you value portability). Generate your SQL based on how many params there are. — Neil Stockton, Jul 06 '15 at 15:19
Then something like this should work `"("+data.stream().map(d -> "("+d.a+","+d.b+")").collect(Collectors.joining(","))+")"` — M at, Feb 23 '22 at 07:56

score 1 · Accepted Answer · edited May 23 '17 at 11:58

1

You use a native query, it seem that there is no list parameter at all for native queries:

Set list parameter to native query

How to use a dynamic parameter in a IN clause of a JPA named query?

You have to concatenate the SQL manually.

edited May 23 '17 at 11:58

Community

1
1

answered Jul 06 '15 at 15:28

pdem

3,880
1
24
38

How in JPA create list of pairs as parameter?

1 Answers1