文讲述的是多种方式进行数据库的批量删除(以删除用户信息为例)
方式一:
- /**
- * 批量删除用户
- *
- * 使用事务处理方式进行删除
- *
- * @param userIds
- * @return
- */
- public boolean delUsers(String[] userIds){
- boolean flag = false;
- Connection conn = null;
- PreparedStatement pstmt = null;
- String sql = "delete from t_user where user_id=?";
- try {
- conn = DbUtil.getConnection();
- conn.setAutoCommit(false);
- pstmt = conn.prepareStatement(sql);
- for(int i =0 ;i<userIds.length;i++){
- pstmt.setString(1,userIds[i].trim());
- pstmt.addBatch();
- }
- pstmt.executeBatch(); //批量执行
- conn.commit();//提交事务
- flag = true;
- } catch (SQLException e) {
- try {
- conn.rollback(); //进行事务回滚
- } catch (SQLException ex) {
- }
- }finally {
- DbUtil.close(pstmt);
- DbUtil.close(conn);
- }
- return flag;
- }
- /**
- * 根据用户id进行删除单条信息
- * @param userId
- * @return
- */
- public boolean delUser(String userId){
- boolean flag = false;
- String sql = "delete from t_user where user_id=?";
- Connection conn = null;
- PreparedStatement pstmt = null;
- try {
- conn = DbUtil.getConnection();
- pstmt = conn.prepareStatement(sql);
- pstmt.setString(1, userId);
- if(pstmt.executeUpdate()>0){
- flag = true;
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }finally {
- DbUtil.close(pstmt);
- DbUtil.close(conn);
- }
- return flag;
- }
- /**
- * 批量删除用户
- *
- * 直接使用for循环进行删除
- *
- * @param userIds
- * @return
- */
- public boolean delUsers(String[] userIds){
- boolean flag = false;
- for (int i = 0; i < userIds.length; i++) {
- flag = flag & delUser(userIds[i]);
- }
- return flag;
- }
方式三:
- * 批量删除用户
- *
- * 采用一条语句完成删除,只提交一次
- *
- * 采用Statement拼串方式
- * delete from t_user where user_id in ('aaaa', 'afff', 'eeee')
- * @param userIds
- *//*
- public void delUsers(String[] userIds) {
- StringBuilder sbStr = new StringBuilder();
- for (int i=0; i<userIds.length; i++) {
- sbStr.append("'")
- .append(userIds[i])
- .append("'")
- .append(",");
- }
- String sql = "delete from t_user where user_id in (" + sbStr.substring(0, sbStr.length() - 1) + ")";
- System.out.println("UserManager.delUser() -->>" + sql);
- Connection conn = null;
- Statement stmt = null;
- try {
- conn = DbUtil.getConnection();
- stmt = conn.createStatement();
- stmt.executeUpdate(sql);
- }catch(SQLException e) {
- e.printStackTrace();
- }finally {
- DbUtil.close(stmt);
- DbUtil.close(conn);
- }
- }
方式四:
- /**
- * 批量删除用户
- *
- * 采用一条语句完成删除,只提交一次
- * 采用PreparedStatement占位符方式
- * @param userIds
- */
- public void delUsers(String[] userIds) {
- StringBuilder sbStr = new StringBuilder();
- for (int i=0; i<userIds.length; i++) {
- sbStr.append("?");
- if (i < (userIds.length - 1)) {
- sbStr.append(",");
- }
- }
- String sql = "delete from t_user where user_id in (" + sbStr.toString() + ")";
- Connection conn = null;
- PreparedStatement pstmt = null;
- try {
- conn = DbUtil.getConnection();
- pstmt = conn.prepareStatement(sql);
- for (int i=0; i<userIds.length; i++) {
- pstmt.setString(i + 1, userIds[i]);
- }
- pstmt.executeUpdate();
- }catch(SQLException e) {
- e.printStackTrace();
- }finally {
- DbUtil.close(pstmt);
- DbUtil.close(conn);
- }
- }
以上提供了四种方式进行批量删除数据库信息,其中第二种方式不推荐使用,其他三种方式个人觉得都很不错,可以借鉴使用。
以上几种方式的实现通过学习动力节点_王勇_Java项目视频_DRP完整版之后的结果,在这儿感谢王勇老师,很喜欢您的视频讲解。