1

I need using JPA to find records by matching 2 grouped parameters from a table but my problem is that I don't know how to pass an object or grouped parameter in SQL, if that is possible at all.

Let's say we have the User table:

USER_TABLE

name VARCHAR,
email VARCHAR,
address VARCHAR

I need to find all users where grouped name and email match. Something like this:

SELECT FROM USER_TABLE 
WHERE (name, email) 
IN (('John', 'john@mail'), ('Oliver', 'oliver@mail'));

It works in SQL tool but now I need to use this SQL from Java

To do so I udpated it to accept a parameter

SELECT FROM USER_TABLE 
WHERE (name, email) 
IN (?);

So my question is HOW do I pass parameters or so that they will be grouped?

I tried to create a parameter as it is below but it won't work because it is not a valid type

String param = "('John', 'john@mail'), ('Oliver', 'oliver@mail')";
db.execute(sql, param)

Is that possible at all?

Octtavius
  • 563
  • 8
  • 29
  • Try without the external brackets as suggested here: https://stackoverflow.com/questions/4378824/adding-in-clause-list-to-a-jpa-query – MShaposhnik Jun 13 '18 at 13:34
  • @MShaposhnik it doesn't work. It is not the brackets that causes the issue but the grouping of 2 paramters as a single one. I will probably use StringBuilder as it seems the only way to do it. Not that secured but the only way. Thanks – Octtavius Jun 13 '18 at 14:30

2 Answers2

2

You do it this way:

SELECT FROM USER_TABLE 
WHERE (name, email) 
IN ((?, ?), (?, ?));

Each parameter takes the place of a single scalar value, not a list of values, and not any other SQL syntax like parentheses. You need to put all the parens into your prepared statement, then use parameters only for the values.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
-3

use jpa Query,something like that:

@Query("select * from User s where s.id in (:ids)")

Page methodName(@Param(value = "ids") List ids);

urwish
  • 3
  • 2