How do I create a list of pairs as a parameter in JPA?
In my Java application, I want to create a SQL query that at the end looks like this:
SELECT * FROM my_table t WHERE (t.a, t.b) IN ((a1,b1),(a2,b2),(a3,b3), ... )
How do I 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();
}
+3
source to share
1 answer
You are using your own query, there seems to be no list parameters for your own queries:
Set a list parameter for your own query
How to use dynamic parameter in IN section of JPA query named?
You need to merge the SQL manually.
+1
source to share