How to pass a list in sql query

Question

I have values ​​( 1, 2, 3 , for example), and I want to pass this list to an SQL query:

 "select name from tbl where id in" + list 

How can I achieve this?

Answer 1

you need to put your list directly in sql statement

Example:

 String sql="select name from tbl where id in ("+StringUtils.join(list, ',')+")"; Statement st=connection.createStatement(); st.execute(sql); 

Answer 2

In response to the comment you are using HQL, I see. If so, the Hibernate people make it easy for you, in the request, simply indicate:

 where id in (:ids) 

and then use setParameterList("ids", list) , passing in your list. Hibernate will do the best for you!

Answer 3

SQL syntax:

 select name from tbl where id in (1,2,3) 

All you have to do is create a list of items separated by commas and paste it into a string.

Oh, and the mandatory string string warning sql: do not!

Answer 4

Pass the list as a comma separated list and use the split function to split it into a temporary table variable.

 CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512)) RETURNS table AS RETURN ( WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop > 0 ) SELECT pn, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s FROM Pieces ) 

Answer 5

it depends on how you build your sql. If you build it yourself (bad idea), you need to make sql look like

... where id in (1,2,3)...

Since in your comment you are specifying hibernate hql, something like -

 Query query = session.createSQLQuery("from User where id in :ids "); query.setParameter("ids", idsList); List list = query.list(); 

should start. Note User is the name of the object that you have mapped to the table you want to query.

Answer 6

String sqlQuery = "select a name from tbl, where id in" + sqlFormatedList (list);

 private String sqlFormatedList(List<Integer> list){ StringBuilder sb = new StringBuilder(); sb.append("("); for (Integer i : list){ sb.append(i+","); } sb.deleteCharAt(sb.length() -1); sb.append(")"); return sb.toString(); } 

Answer 7

Using Java 8 and above:

 String inClause = list.stream().map(id-> String.valueOf(id)).collect(toStringJoiner(",")); String sql = "select name from tbl where name in (" + inClause + ")"; 

Answer 8

I tried the solution provided by Farmour. It is simple, efficient and works. I changed it according to my need ... & also tried debugging to ensure that it is formatted exactly the way I want.

public static String sqlFormatedList (list listList) {StringBuilder sb = new StringBuilder (); sb.append ("(");

  for (String str : codeList){ sb.append("'"+str+"',"); } sb.deleteCharAt(sb.length() -1); sb.append(")"); return sb.toString(); } 

Analysis in brief: Input pairs: [ABC, MNP, XYZ]; as a list of strings in Java format

Output: ('ABC', 'MNP', 'XYZ'); as a String for use in an SQL query.

sqlFormattedString = ('ABC', 'MNP', 'XYZ');

In this query, the above line is o / p. select * from tableName as tn, where tn.code in sqlFormattedString;

PS: codeList in the parameter list - java.util.List