页: 1

例如:

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

我在你的答复中看到你再次使用总部L。 如果是这样的话,那么,在问询中,自由的民间人士就这样容易了:

where id in (:ids)

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

The syntax is:

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

你们必须做的是制定 separated分开的物品清单,并在插图中加入。

Oh and theompulsory sql string Building Appeal: do.

这取决于你如何构建你的q。 如果你正在构筑 yourself(坏思想)的话,你需要做些事情,使 s像——

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

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

你们应该开始。 注<代码>User是你向您希望询问的表格标明的目标名称。

通过按组别开列的清单,并利用分部分功能将其分成一个临时表变量。

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

我知道这是一个很长的过去,但是,当我寻求解决该问题的办法时,这又是一页。 看来,所有答案都不完美,原因有一(三) 。 因此,我的解决办法是:

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

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/

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