Mybatis 使用动态语句实现批量删除（delete结合foreach）

港控/mmm° 2022-06-05 04:53 544阅读 0赞

**Mybatis 使用动态语句实现批量删除（delete结合foreach）**

在下面的案例本人将演示如何使用动态语句批量删除数据库数据，本人在数据库建了一张emp员工表（表的数据自己填充），表的结构如下：

![Center][]

批量删除的核心代码为，在实体映射文件中配置如下的代码：

<delete id="deleteMoreEmp" parameterType="int[]">
    		
    		
    		delete from emp where empno in
    		<foreach collection="array" item="arr" index="no" open="("
    			separator="," close=")">
    			#{arr}
    		</foreach>
    	</delete>

下面是项目的结构（本人使用的是maven搭建的web项目）：

![Center 1][]

IEmpDAO.java为接口提供批量删除数据方法，EmpDAOImpl.java为接口的实现类，MybatisSqlSessionFactory.java为本人创建的获取sqlSession的工具类，Emp.java为实体类，Emp.xml为映射文件，mybatis\_cfg.xml为mybatis主配置文件，Test.java为测试类，pom.xml为maven引入依赖的文件。

1、IEmpDAO.java为接口提供批量删除数据方法

/**
    	 * 批量删除
    	 * 
    	 * @param arr
    	 * @return
    	 */
    	public boolean doRemoveeMore(int[] arr);

传入的参数为emp的主键数组。

2、EmpDAOImpl.java为接口的实现类

public boolean doRemoveeMore(int[] arr) {
    		SqlSession sqlSession = null;
    		try {
    			sqlSession = MybatisSqlSessionFactory.getMySqlSession();
    			int result = sqlSession.delete("cn.sz.hcq.pojo.Emp.deleteMoreEmp",
    					arr);
    			sqlSession.commit();
    			return result > 0 ? true : false;
    		} catch (Exception e) {
    			e.printStackTrace();
    			sqlSession.rollback();
    		} finally {
    			MybatisSqlSessionFactory.closeSqlSession();
    		}
    		return false;
    	}

3、MybatisSqlSessionFactory.java为本人创建的获取sqlSession的工具类

package cn.sz.hcq.factory;
    
    import java.io.IOException;
    import java.io.Reader;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    public class MybatisSqlSessionFactory {
    	// 配置文件
    	private static final String RESOURCE = "mybatis_cfg.xml";
    	private static Reader reader = null;
    	private static SqlSessionFactoryBuilder builder = null;
    	private static SqlSessionFactory factory = null;
    	// 可以在同一个线程范围内，共享一个对象
    	private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
    
    	// 静态代码块（类加载的时候执行一次）
    	static {
    		try {
    			reader = Resources.getResourceAsReader(RESOURCE);
    			builder = new SqlSessionFactoryBuilder();
    			factory = builder.build(reader);
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	}
    
    	public static SqlSession getMySqlSession() {
    		// 从本地线程中获取session连接
    		SqlSession sqlSession = threadLocal.get();
    		// 连接为空则创建连接，并将该连接添加到本地线程中去
    		if (sqlSession == null) {
    			if (factory == null) {
    				rebuildFactory();
    			}
    			sqlSession = factory.openSession();
    		}
    		threadLocal.set(sqlSession);
    		return sqlSession;
    	}
    
    	// 创建工厂
    	public static void rebuildFactory() {
    		try {
    			reader = Resources.getResourceAsReader(RESOURCE);
    			builder = new SqlSessionFactoryBuilder();
    			factory = builder.build(reader);
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	}
    
    	// 关闭连接
    	public static void closeSqlSession() {
    		SqlSession sqlSession = threadLocal.get();
    		if (sqlSession != null) {
    			// 关闭session
    			sqlSession.close();
    		}
    		// 同时将本地线程中置为null（防止用户再次调用时出现空的session）
    		threadLocal.set(null);
    	}
    
    }

4、Emp.java为实体类

public class Emp implements Serializable {
    	private Integer empno;
    	private String ename;
    	private String job;
    	private Integer mgr;
    	private Date hiredate;
    	private Double sal;
    	private Double comm;
    	private Integer deptno;
    
    	public Integer getEmpno() {
    		return empno;
    	}
    
    	public void setEmpno(Integer empno) {
    		this.empno = empno;
    	}
    
    	public String getEname() {
    		return ename;
    	}
    
    	public void setEname(String ename) {
    		this.ename = ename;
    	}
    
    	public String getJob() {
    		return job;
    	}
    
    	public void setJob(String job) {
    		this.job = job;
    	}
    
    	public Integer getMgr() {
    		return mgr;
    	}
    
    	public void setMgr(Integer mgr) {
    		this.mgr = mgr;
    	}
    
    	public Date getHiredate() {
    		return hiredate;
    	}
    
    	public void setHiredate(Date hiredate) {
    		this.hiredate = hiredate;
    	}
    
    	public Double getSal() {
    		return sal;
    	}
    
    	public void setSal(Double sal) {
    		this.sal = sal;
    	}
    
    	public Double getComm() {
    		return comm;
    	}
    
    	public void setComm(Double comm) {
    		this.comm = comm;
    	}
    
    	public Integer getDeptno() {
    		return deptno;
    	}
    
    	public void setDeptno(Integer deptno) {
    		this.deptno = deptno;
    	}
    
    }

5、Emp.xml为映射文件

<?xml version="1.0" encoding="UTF-8"?>
     <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
    		"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="cn.sz.hcq.pojo.Emp">
    	
    	<delete id="deleteMoreEmp" parameterType="int[]">
    		
    		
    		delete from emp where empno in
    		<foreach collection="array" item="arr" index="no" open="("
    			separator="," close=")">
    			#{arr}
    		</foreach>
    	</delete>
    </mapper>

6、mybatis\_cfg.xml为mybatis主配置文件

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    		"http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
    	
    	<environments default="myconn">
    		<environment id="myconn">
    			
    			<transactionManager type="JDBC"></transactionManager>
    			
    			<dataSource type="POOLED">
    				
    				<property name="driver" value="org.gjt.mm.mysql.Driver"></property>
    				<property name="url" value="jdbc:mysql://localhost:3306/db"></property>
    				<property name="username" value="root"></property>
    				<property name="password" value="root"></property>
    			</dataSource>
    		</environment>
    	</environments>
    
    	
    	<mappers>
    		<mapper resource="cn/sz/hcq/pojo/Emp.xml" />
    	</mappers>
    	
    </configuration>

7、pom.xml为maven引入依赖的文件

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    	<groupId>cn.sz.hcq.pro</groupId>
    	<artifactId>Mybatis_04</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<packaging>war</packaging>
    	<dependencies>
    		
    		<dependency>
    			<groupId>org.mybatis</groupId>
    			<artifactId>mybatis</artifactId>
    			<version>3.2.3</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<version>5.1.39</version>
    		</dependency>
    	</dependencies>
    </project>

8、Test.java为测试类

public class Test {
    	public static void main(String[] args) {
    		IEmpDAO empDAO = new EmpDAOImpl();
    		System.out.println("----------批量删除----------");
    		int[] arr = { 7791, 7792 };//删除的主键
    		boolean removeeMore = empDAO.doRemoveeMore(arr);
    		System.out.println("批量删除的结果：" + removeeMore);
    	}
    }

完成代码后，运行测试类就可以完成批量删除。

[Center]: /images/20220605/e8bf96f10ecf4967b81cbde995ecdcc3.png
[Center 1]: /images/20220605/5534333270974a70a5dd9b10efe1cd80.png