CODEWITHSUNDEEP
javasql
n92
n92

Reputation: 7602

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 tbl where id in" + list

How might I achieve this?

Upvotes: 20

Views: 120433

Answers (10)

Rob Marrowstone
Rob Marrowstone

Reputation: 1264

I see, in your reply to a comment, that you're using HQL. If that's the case, the Hibernate folks made 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!

Upvotes: 11

ZiZiCodes
ZiZiCodes

Reputation: 1

You could also use regex inside of your query to be able to parse input provided as a single string/varchar composed of multiple ids separated by commas. In my case, it was useful in a plain slq environment without orm knowing the size of the provided parameters collection. It could be something like this:

select name from tbl where id in 
    (SELECT REGEXP_SUBSTR(:ids, '[^,]+', 1, LEVEL) FROM DUAL
    CONNECT BY REGEXP_SUBSTR(:ids, '[^,]+', 1, LEVEL) IS NOT NULL)

In order for it to work the ids varchar should look something like: 'id1,id2,id3'.

You can control :ids parsing with regex as you wish, sometimes it might be useful to have nonalphanumeric signs combination to avoid unnecessary parsing.

Upvotes: 0

Tatool
Tatool

Reputation: 41

I'm adding this here in case someone stumbles upon it - like me. The easy way to parameterize the list to be used in the "IN" clause is to use the STRING_SPLIT function.

Declare @FirstNamesList nvarchar(100) = 'Mark,John,Sara' Select * from STRING_SPLIT(@FirstNamesList, ',')

https://www.pragimtech.com/blog/sql-optimization/sql-server-select-where-in-list/

Upvotes: 0

Paul
Paul

Reputation: 31

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;
    }
  }
}

Upvotes: 0

DYD
DYD

Reputation: 1

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(",", "'", "'"));

Upvotes: 0

Kamal Parvez
Kamal Parvez

Reputation: 7

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();
    }

Upvotes: -1

hvgotcodes
hvgotcodes

Reputation: 120198

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.

Upvotes: 1

corsair
corsair

Reputation: 668

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

Upvotes: 13

user891973
user891973

Reputation:

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
  )

Upvotes: 0

Blindy
Blindy

Reputation: 67380

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!

Upvotes: 2

Related Questions