PreparedStatement中in语句的用法

Question

请问哪位大虾用过PreparedStatement,SQL语句里包含in(?) 这个里面我要放多个数据,比如: in('first','second','third'),要怎么为参数设置值啊?

Answer 1

Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/firefly", "root", "200493128");

    //创建参数列表
List<String> list = new ArrayList<String>();
list.add("U1");
list.add("U2");
list.add("U3");

    //根据参数列表的大小生成in串
StringBuffer buffer = new StringBuffer();
for (int i = 0; i < list.size(); i++) {
    buffer.append("?, ");
}
buffer.deleteCharAt(buffer.length() - 1);
buffer.deleteCharAt(buffer.length() - 1);

pstmt = con.prepareStatement("select * from logon where id in ("
        + buffer.toString() + ")");

    //根据参数列表设置sql参数
for (int i = 0; i < list.size(); i++) {
    pstmt.setString(i + 1, list.get(i));
}

    //运行
rs = pstmt.executeQuery();
while (rs.next()) {
    System.out.println("      id : " + rs.getString("id"));
    System.out.println("password : " + rs.getString("password"));
    System.out.println("");
}
 } finally {
rs.close();
pstmt.close();
con.close();

}

也可以将“//根据参数列表的大小生成in串”和“//根据参数列表设置sql参数”合并，
直接list直接生成最终的sql文，传入pstmt。