How to pass List in sql query

I have of values (1, 2, 3, for example), and I want to pass that list to a SQL query:

"select name from开发者_StackOverflow社区 tbl where id in" + list 

How might I achieve this?

you have to put your list directly to sql statement

example:

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

I see in your reply to a comment that you're using HQL. If that's the case, the Hibernate folks make this one easy for you, in the query, just specify:

where id in (:ids)

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

The SQL syntax is:

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

All you have to do is build the comma separated list of items and insert it in the string.

Oh and the obligatory sql string building warning: don't!

it depends on how you are constructing your sql. If you are constructing it yourself (bad idea) you need to do make the sql look like -

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

Since in your comment you specify hibernate hql, something like -

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

should get you started. Note User is the name of the Object you mapped to the table you want to query.

Pass the list in as a comma seperated list and use a 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
  )

You can write code like and then pass uuidsWithRequiredPattern to SQL. Here in below example I had a requirement to have a uuids prefixed with single quote , suffixed with single quote and delimited with comma.

List<UUID> uuidList = Arrays.asList("581394be-6a27-11ea-bc55-0242ac130003","681394be-6a27-11ea-bc55-0242ac130003")
String uuidsWithRequiredPattern = uuidList.stream()
                .map(UUID::toString)
                .collect(Collectors.joining(",", "'", "'"));

I know this is a long time past but this was the page that returned when I looked for a solution to exactly this problem. It seems that all answers are not perfect for one reason or another (including the possibility of SQL injection which is a real concern).So, here's my solution:-

/**
 * Checks if this range exists in the database already.
 * If it does, it will return the original name.
 * @param products the list of product names to check
 * @return  The range's name in the database
 * @throws SQLException if SQL fails
 *   (or if it exists but with multiple names)
 */
private String getOriginalName(final List<String> products)throws SQLException {
  if (products.isEmpty()) {
    throw new SQLException("Cannot check an empty list");
  }
  try (Connection connection = ConnectionPoolConfigRulesUtils.getConnection();
      PreparedStatement statement = connection.prepareCall(
          "SELECT [rangeName] FROM [dbo].[products] WHERE [productName] IN ("
          + repeatString("?", ", ", products.size()) + ") GROUP BY [rangeName]")) {
    int fieldNo = 1;  //NOPMD DU anomaly
    for (final DbProduct product: products) {
      statement.setInt(fieldNo++, product.getId());
    }
    try (ResultSet rset = statement.executeQuery()) {
      final String rangeName;
      if (rset.next()) {
        //Get the first range name
        rangeName = Nz.nz(rset.getString("rangeName"));
      } else {
        //There isn't one so return empty string
        rangeName = "";
      }
      if (rset.next()) {
        //But, if there are multiple ranges, this is an error so treat it as such
        throw new SQLException("The products you are trying to save exist already in the dabase under more than one range. "
            + "Cannot process the change");
      }
      return rangeName;
    }
  }
}

String sqlQuery = "select 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();
}

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 + ")";

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