我有一个值列表(例如1, 2, 3
),我想将该列表传递到SQL查询中:
"select name from tbl where id in" + list
我该如何实现这个?
你需要直接将列表放入 SQL 语句中。
示例:
String sql="select name from tbl where id in ("+StringUtils.join(list, ',')+")";
Statement st=connection.createStatement();
st.execute(sql);
String.join(",", list)
,但是对于字符串参数,此解决方案不适用。 - Pino我看到你在回复评论时使用了HQL。如果是这样的话,Hibernate的开发人员为您提供了方便的方法,在查询中只需指定:
where id in (:ids)
然后使用setParameterList("ids", list)
,并传递您的列表。Hibernate将为您完成所有的展开操作!
select name from tbl where id in (1,2,3)
你需要做的就是构建逗号分隔的项目列表并将其插入字符串中。
哦,还有必要的 SQL 字符串构建警告:不要这样做!
这取决于你如何构建你的SQL。如果你自己构建它(不好的想法),你需要让SQL看起来像这样 -
... where id in (1,2,3)...
由于在你的评论中你指定了Hibernate HQL,类似于 -
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
)
我知道这已经过去很长时间了,但当我寻找解决方案时,这就是返回的页面,正好解决了这个问题。似乎所有答案都不完美,因为有各种原因(包括 SQL 注入的可能性,这是一个真正的问题)。所以,这是我的解决方案:
/**
* 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;
}
}
}
您可以编写类似的代码,然后将uuidsWithRequiredPattern传递给SQL。 在下面的示例中,我需要具有以单引号为前缀、以单引号为后缀并用逗号分隔的uuids。
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(",", "'", "'"));
https://www.pragimtech.com/blog/sql-optimization/sql-server-select-where-in-list/
字符串 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();
}
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();
}