Passing array or list of strings in IN clause of a sql query

Question

I am new to Java, so please forgive me if it is a naive question. I have a string that is in comma separated like this "str1, str2,str3" .I need to query data from database that matches that list of strings. I know I can convert "str1,str2,str3" to an array. But would also need to pass this in a IN clause of sql query like this

 select * from Customer where name IN ('str1','str2','str3')

How can I accomplish that since after the conversion of "str1,str2,str3" to an array won't be legal to pass it in IN clause? Any utility method in hibernate that can help taking care of this issue?

Thank you in advance

What are you using to make your queries? Are you just building your sql-queries as Strings and then send them to the database as native queries? — user6073886
– user6073886, Commented Mar 18, 2021 at 17:57
Convert the string str1,str2,str3 into a list and use that list in a prepared statement or hql. — aksappy
– aksappy, Commented Mar 18, 2021 at 18:22
@aksappy But the problem with that translate to this sql name IN ([str1,str2,str3]) query which is invalid.Unless I am missing something — sparks
– sparks, Commented Mar 18, 2021 at 21:15

SternK · Accepted Answer · 2021-03-18 21:31:20Z

1

You can try to use something like this:

List<Customer> customers = entityManager.createQuery(
    "select c " +
    "from Customer c " +
    "where c.name in :names", Customer.class )
.setParameter( "names", Arrays.asList( "str1", "str2" ) )
.getResultList();

See also this section of hibernate documentation.

answered Mar 18, 2021 at 21:31

SternK

13.3k34 gold badges40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing array or list of strings in IN clause of a sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related