【DRP】—【SQL】批量删除

/** 2 * 批量删除用户 3 * 4 * 采用一条语句完成删除 5 * 只提交一次 6 * 7 * 采用Statement拼串方式 8 * delete from t_user where user_id in (‘aaaa‘, ‘afff‘, ‘eeee‘) 9 * @param userIds 10 */ 11 public void delUser(String[] userIds) { 12 StringBuilder sbStr = new StringBuilder(); 13 for (int i=0; i<userIds.length; i++) { 14 sbStr.append("‘") 15 .append(userIds[i]) 16 .append("‘") 17 .append(","); 18 } 19 String sql = "delete from t_user where user_id in (" + sbStr.substring(0, sbStr.length() - 1) + ")"; 20 System.out.println("UserManager.delUser() -->>" + sql); 21 Connection conn = null; 22 Statement stmt = null; 23 try { 24 conn = DbUtil.getConnection(); 25 stmt = conn.createStatement(); 26 stmt.executeUpdate(sql); 27 }catch(SQLException e) { 28 e.printStackTrace(); 29 }finally { 30 DbUtil.close(stmt); 31 DbUtil.close(conn); 32 } 33 }

 

 1 /**
 2      * 批量删除用户
 3      * 
 4      * 采用一条语句完成删除
 5      * 只提交一次
 6      * 
 7      * 采用PreparedStatement占位符方式
 8      * 
 9      * delete from t_user where user_id in (?, ?, ?)
10      * @param userIds
11      */
12     public void delUser(String[] userIds) {
13         StringBuilder sbStr = new StringBuilder();
14         for (int i=0; i<userIds.length; i++) {
15             sbStr.append("?");
16             if (i < (userIds.length - 1)) {
17                 sbStr.append(",");
18             }
19         }
20         String sql = "delete from t_user where user_id in (" + sbStr.toString()  +  ")";
21         System.out.println("UserManager.delUser() -->>" + sql);
22         Connection conn = null;
23         PreparedStatement pstmt = null;
24         try {
25             conn = DbUtil.getConnection();
26             pstmt = conn.prepareStatement(sql);
27             for (int i=0; i<userIds.length; i++) {
28                 pstmt.setString(i + 1, userIds[i]);
29             }
30             pstmt.executeUpdate();
31         }catch(SQLException e) {
32             e.printStackTrace();
33         }finally {
34             DbUtil.close(pstmt);
35             DbUtil.close(conn);
36         }
37     }    

 

JDBC 笔记3 通过PreparedStatement 对数据库进行增删改查

总结：不断提高自己的编程效率！~~~~~

【DRP】—【SQL】批量删除

标签：