下面我将演示如何使用动态语句批量删除数据库数据
本人在数据库建了一张emp员工表（表的数据自己填充），表的结构如下：
批量删除的核心代码为，在实体映射文件中配置如下的代码：
|
1 2 3 4 5 6 7 8 9 10 11 |
<!-- 批量删除 --> < delete id = "deleteMoreEmp" parameterType = "int[]" > <!-- delete from emp where empno in(7789,7790) --> <!-- forEach : 用来循环 collection : 用来指定循环的数据的类型 可以填的值有：array,list,map item : 循环中为每个循环的数据指定一个别名 index : 循环中循环的下标 open : 开始 close : 结束 separator : 数组中元素之间的分隔符 --> delete from emp where empno in < foreach collection = "array" item = "arr" index = "no" open = "(" separator = "," close = ")" > #{arr} </ foreach > </ delete > |
下面是项目的结构
本人使用的是maven搭建的web项目
IEmpDAO.java为接口提供批量删除数据方法，EmpDAOImpl.java为接口的实现类，MybatisSqlSessionFactory.java为本人创建的获取sqlSession的工具类，Emp.java为实体类，Emp.xml为映射文件，mybatis_cfg.xml为mybatis主配置文件，Test.java为测试类，pom.xml为maven引入依赖的文件。
1、IEmpDAO.java为接口提供批量删除数据方法
|
1 2 3 4 5 6 7 |
/** * 批量删除 * * @param arr * @return */ public boolean doRemoveeMore( int [] arr); |
2、EmpDAOImpl.java为接口的实现类
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
public boolean doRemoveeMore( int [] arr) { SqlSession sqlSession = null ; try { sqlSession = MybatisSqlSessionFactory.getMySqlSession(); int result = sqlSession.delete( "cn.sz.hcq.pojo.Emp.deleteMoreEmp" , arr); sqlSession测试数据mit(); return result > 0 ? true : false ; } catch (Exception e) { e.printStackTrace(); sqlSession.rollback(); } finally { MybatisSqlSessionFactory.closeSqlSession(); } return false ; } |
3、MybatisSqlSessionFactory.java
为本人创建的获取sqlSession的工具类
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
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为实体类
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
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 测试数据m = comm; }
public Integer getDeptno() { return deptno; }
public void setDeptno(Integer deptno) { this .deptno = deptno; } } |
5、Emp.xml为映射文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<? 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(7789,7790) --> <!-- forEach : 用来循环 collection : 用来指定循环的数据的类型 可以填的值有：array,list,map item : 循环中为每个循环的数据指定一个别名 index : 循环中循环的下标 open : 开始 close : 结束 separator : 数组中元素之间的分隔符 --> 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主配置文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
<? 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" > <!-- type:数据源连接的方式 ，POOLED:连接池方式， UNPOOLED: 非连接池的方式 ，JNDI：java命名与目录接口方式 --> < 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引入依赖的文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
< project xmlns = "http://maven.apache.org/POM/4.0.0" xmlns:xsi = "http://HdhCmsTestw3.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 > <!-- https://mvnrepository测试数据/artifact/org.mybatis/mybatis --> < dependency > < groupId >org.mybatis</ groupId > < artifactId >mybatis</ artifactId > < version >3.2.3</ version > </ dependency > <!-- https://mvnrepository测试数据/artifact/mysql/mysql-connector-java --> < dependency > < groupId >mysql</ groupId > < artifactId >mysql-connector-java</ artifactId > < version >5.1.39</ version > </ dependency > </ dependencies > </ project > |
8、Test.java为测试类
|
1 2 3 4 5 6 7 8 9 |
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); } } |
完成代码后，运行测试类就可以完成批量删除。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
原文链接：https://blog.csdn.net/benxiaohai888/article/details/78564751
查看更多关于Mybatis如何使用动态语句实现批量删除(delete结合foreach)的详细内容...