下面我将演示如何使用动态语句批量删除数据库数据
本人在数据库建了一张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.commit(); 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 .comm = 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://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 > <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis --> < dependency > < groupId >org.mybatis</ groupId > < artifactId >mybatis</ artifactId > < version >3.2.3</ version > </ dependency > <!-- https://mvnrepository.com/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