mybatis批量删除Batch deletion
一、批量删除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 : 是一个变量名,自己随便起名 -->
二、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 1078993387@qq.com * @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>
