MyBatis批量删除Batch delete

1.批量删除Batch deletion

(1)批量删除功能实现示意图

(2)Mybatis批量删除注意事项：

1)Mybatis删除这里主要考虑两种参数类型:数组或者集合.

而这点区别主要体现在UserDaoMapper.xml文件中标签的collection属性 :

当collection=”array “时,表名参数为数组;
    当collection=”list “时,表名参数为集合.

2)UserDaoMapper.xml 配置

无论Mybatis是与mysql数据库结合,还是与Oracle数据库,都同样适合如下设置与操作.

 <!-- 批量删除用户信息 -->
    <delete id="BatchDelete">
        delete from t_user where id
        <foreach collection="list" open="in( " item="id" separator="," close=")">
            #{id}
        </foreach>
    </delete>
     <!--
    t_user : 表名
    id : 字段名
    collection:表示类型,这里参数是数组,就写成array,如果是集合,就写成list  
    item : 是一个变量名,自己随便起名
    -->

2.Mybatis批量删除具体实现案例

(1)题目：

从queryAll.jsp页面中批量删除id为7，8,9的重复用户“周恒君”。

(2)项目结构图

(3)提前准备jar包

(4)提前准备Mybatis工具类

package com.tjcu.util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
/**
 * @author 王恒杰
 * @version 1.0
 * @date 2021/10/12 9:52
 * @email [email protected]
 * @Address 天津
 * @Description:
 */
public class MybatisUtil {
    /**
     * 线程绑定对象，保证是同一个对象
     */
    private static final ThreadLocal<SqlSession> t1 = new ThreadLocal<>();
    private static SqlSessionFactory factory;
    static {
        InputStream is = null;
        try {
            is = Resources.getResourceAsStream("mybatis-config.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        factory = new SqlSessionFactoryBuilder().build(is);
    }
    /**
     * 创建SqlSession
     *
     * @return sqlSession
     */
    private static SqlSession openSession() {
        SqlSession sqlSession = t1.get();
        if (sqlSession == null) {
            sqlSession = factory.openSession();
            t1.set(sqlSession);
        }
        return sqlSession;
    }
    /**
     * 调用功能方法
     *
     * @param clazz
     * @return Object
     */
public  static Object getMapper(Class clazz) {
        return openSession().getMapper(clazz);
    }
    /**
     * 关闭资源
     */
    public static void close() {
        if (openSession() != null) {
            openSession().close();
        }
        t1.remove();
    }
    /**
     * 提交事务,关闭资源
     */
    public static void commit() {
        //1、提交事务
       openSession().commit();
        //2、关闭资源
        close();
        t1.remove();
    }
    /**
     * 回滚事务,关闭资源
     */
    public static void rollback() {
        //1、提交事务
        openSession().rollback();
        //2、关闭资源
        close();
        t1.remove();
    }
}

(5)实体类User

public class User {
    private Integer id;
    private String username;
    private String password;
    private int age;
    private Date birthday;

(6)UserDao接口:

public interface UserDao {
    /**
     * 查询所有
     * @return用户集合
     */
    public List<User> selectUserAll();
    /**
     * 根据id进行批量删除
     * @param ids
     */
    public void BatchDelete(List<Integer> ids);
}

(7)UserDaoMapper.xml实现

<mapper namespace="com.tjcu.dao.UserDao">
    <select id="selectUserAll" resultType="user">
        select *
        from t_user;
    </select>
    <!-- 批量删除用户信息 -->
    <delete id="BatchDelete">
        delete from t_user where id
        <foreach collection="list" open="in( " item="id" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <!-- 参数说明 -->
    <!--
    t_user : 表名
    id : 字段名
    collection:表示类型,这里参数是数组,就写成array,如果是集合,就写成list
    item : 是一个变量名,自己随便起名
    -->
</mapper>

(8)UserService接口

public interface UserService {
    /**
     *  查询所有
     * @return
     */
    public List<User> queryAll();
    /**
     * 批量删除
     * @param ids
     */
    public void BatchDelete(List<Integer> ids);
}

(9)UserServiceImpl实现

public class UserServiceImpl implements UserService{
    @Override
    public List<User> queryAll() {
        //调用DAO
        UserDao mapper = (UserDao) MybatisUtil.getMapper(UserDao.class);
        List<User> users = mapper.selectUserAll();
        MybatisUtil.close();
        return  users;
    }
    @Override
    public void BatchDelete(List<Integer> ids) {
        //调用DAO
        UserDao mapper = null;
            mapper = (UserDao) MybatisUtil.getMapper(UserDao.class);
            mapper.BatchDelete(ids);
            MybatisUtil.commit();
    }
}

(10)Struts2的Action类

public class UserAction extends ActionSupport {
    //接收数据
    /**
     * 替换request作用域传递数据
     */
    private List<User> users;
    private List<Integer> ids;
    public String selectAll() throws Exception {
        //调用业务
        UserServiceImpl userService = new UserServiceImpl();
        users = userService.queryAll();
        System.out.println(users);
        //跳转页面
        return Action.SUCCESS;
    }
    public String BatchDelete(){
        UserServiceImpl userService = new UserServiceImpl();
        userService.BatchDelete(ids);
        return "batachDeleteOK";
    }
    public List<User> getUsers() {
        return users;
    }
    public void setUsers(List<User> users) {
        this.users = users;
    }
    public List<Integer> getIds() {
        return ids;
    }
    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }
}

(11)Struts2的Web.xml过滤配置【WEB-IF下】

 <filter>
        <filter-name>struts</filter-name>
        <filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
    </filter>
    <filter-mapping>
        <filter-name>struts</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>

(12)Struts.xml中的Struts核心配置文件

struts>
  <package name="user" extends="struts-default" namespace="/user">
  <action name="queryAll" class="com.tjcu.action.UserAction" method="selectAll">
    <result name="success" type="dispatcher">/queryAll.jsp</result>
  </action>
    <action name="BatchDelete" class="com.tjcu.action.UserAction" method="BatchDelete">
      <result name="batachDeleteOK" type="redirectAction">
        queryAll
      </result>
    </action>
  </package>
</struts>

(13)Mybatis-config.xml Mybatis核心配置文件

<configuration>
    <!--用于加载小配置文件  resource:小配置文件的文件路径-->
    <properties resource="jdbc.properties"></properties>
    <!--配置别名-->
    <typeAliases>
        <typeAlias type="com.tjcu.entity.User" alias="user"></typeAlias>
    </typeAliases>
    <!--环境设置 mysql   default:框架当前默认使用哪个环境设置  书写的是环境的id属性值-->
    <environments default="mysql">
        <!--单个数据库连接配置  id:名字 随意 唯一-->
        <environment id="mysql">
            <!--设置事务管理方式  JDBC:使用原生JDBC事务控制方式   JPA分布式事务控制-->
            <transactionManager type="JDBC"></transactionManager>
            <!--连接池设置  type：指定使用连接池的类型   POOLED：Mybatis框架默认连接池
               三方连接池：DBCP 、c3p0、druid(阿里旗下)-->
            <dataSource type="POOLED">
                <!--数据库的连接参数-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--  配置mappers-->
    <mappers>
        <mapper resource="com/tjcu/dao/UserDaoImpl.xml"></mapper>
    </mappers>
</configuration>

(14)queryAll.jsp

<%@page contentType="text/html; UTF-8" pageEncoding="utf-8" isELIgnored="false" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<html>
<style>
    td,th {
        /*设置边框*/
        border: 1px solid black;
    }
</style>
<body>
<center>
<table style=" border: 1px solid black;border-collapse: collapse;">
    <%--
       private Integer id;
        private String username;
        private String password;
        private int age;
        private Date birthday;
    --%>
    <tr>
        <td>序号</td>
        <td>id</td>
        <td>姓名</td>
        <td>密码</td>
        <td>年龄</td>
        <td>生日</td>
        <td>删除</td>
        <td>修改</td>
    </tr>
        <form action="${pageContext.request.contextPath}/user/BatchDelete" method="post">
    <c:forEach var="user" items="${requestScope.users}">
        <tr>
            <td><input type="checkbox" name="ids" value="${user.id}"/></td>
            <td>${user.id}</td>
            <td>${user.username}</td>
            <td>${user.password}</td>
            <td>${user.age}</td>
            <td><fmt:formatDate value="${user.birthday}" pattern="yyyy年MM月dd日"></fmt:formatDate></td>
            <td>删除</td>
            <td>修改</td>
        </tr>
    </c:forEach>
            <input type="submit" value="批量删除">
        </form>
</table>
</center>
</body>
</html>