476
社区成员
发帖
与我相关
我的任务
分享JAVA sql NOT IN list语句
我试图从记录ID NOT IN列表的数据库中选择记录.
看看@我的问题如下.
String Sqlids = "2,6,3,9"; // this is dynamic so the number of element is unknown
String str= "SELECT TOP 1 * FROM student WHERE ID NOT IN (2,6,3,9) ORDER BY NEWID()";
PreparedStatement stat = con.prepareStatement(str);
ResultSet rs = stat.executeQuery();
上面的陈述工作很精细,但如果我改成它
String Sqlids = "2,6,3,9";
String str= "SELECT TOP 1 * FROM student WHERE ID NOT IN (Sqlids) ORDER BY NEWID()";
PreparedStatement stat = con.prepareStatement(str);
ResultSet rs = stat.executeQuery();
//i also try this
String Sqlids = "2,6,3,9";
String str= "SELECT TOP 1 * FROM student WHERE ID NOT IN (?) ORDER BY NEWID()";
PreparedStatement stat = con.prepareStatement(str);
stat.setString(1,Sqlids );
ResultSet rs = stat.executeQuery();
上面的声明没有过滤由于Sqlids是一个字符串,它将其视为一个参数,因此它返回重复的行,是否存在用于存储2,6,3,9等值的整数格式？因为Sqlids来自名为SqlidList的arraylist我尝试这样的somtin
Iterator iTr = SqlidList.iterator();
while(iTr.hasNext()){
stat.setString(1,iTr.next().toString()+",");
}
但是setString(1, – )因为在while循环中不可用
...全文
请发表友善的回复…
发表回复
weixin_38067275 2019-09-12
- 打赏
- 举报
将id转换为String []后使用Connection#createArrayOf
String[] ids = {"2", "6", "3", "9"};
String str= "SELECT TOP 1 * FROM student WHERE ID NOT IN ? ORDER BY NEWID()";
PreparedStatement stat = con.prepareStatement(str);
stat.setArray(1, con.createArrayOf("text",ids));
ResultSet rs = stat.executeQuery();
如果JDBC驱动程序不支持createArrayOf(如本例所示),我可能只是构造查询字符串,例如：
String Sqlids = "2,6,3,9";
String str= "SELECT TOP 1 * FROM student WHERE ID NOT IN ("+Sqlids+") ORDER BY NEWID()";
或者,如果您有一组ID,请使用实用程序方法来创建数组内容：
public static String toSqlArray(List<String> strings) {
StringBuilder sb = new StringBuilder();
boolean doneOne = false;
for(String str: strings){
if(doneOne){
sb.append(", ");
}
sb.append("'").append(str).append("'");
doneOne = true;
}
return sb.toString();
}
