mysql mybatis批量删除_mybatis批量增删改——（十五）

1.首先应该明白，mybatis增删改返回值是int型的影响行数的值

mapper接口

packagecn.xm.mapper;importjava.util.List;importcn.xm.pojo.Questions;/*** 自定义的批量删除与批量增加试题

*@authorliqiang

**/

public interfaceQuestionsCustomMapper {/*** 批量导入试题

*@paramlist 需要倒入的试题集合

*@return*@throwsException*/

public int saveQuestionBatch()throwsException;}

mapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

INSERT INTO `exam`.`questions`

VALUES ('7',

'1',

'测试题目7',

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

"安全知识")

测试代码:

packagecn.xm.test.mybatis;importjava.io.InputStream;importjava.net.URL;importjava.util.Date;importjava.util.List;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importorg.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmxMBean;importorg.junit.Before;importorg.junit.Test;importcn.xm.mapper.EmployeeInMapper;importcn.xm.mapper.QuestionsCustomMapper;importcn.xm.pojo.EmployeeIn;importcn.xm.pojo.EmployeeInExample;public classMybatisTest2 {privateSqlSessionFactory sqlSessionFactory;

@Beforepublic void setUp() throwsException {//将全局配置文件作为一个流

String resource = "sqlMapConfig.xml";

String realPath= this.getClass().getClassLoader().getResource("sqlMapConfig.xml").getPath();

InputStream inputStream=Resources.getResourceAsStream(resource);//建立一个SqlSession工厂

sqlSessionFactory = newSqlSessionFactoryBuilder().build(inputStream);

}//测试增加

@Testpublic void testAdd() throwsException {

SqlSession sqlSession=sqlSessionFactory.openSession();

QuestionsCustomMapper qsm= sqlSession.getMapper(QuestionsCustomMapper.class);int total =qsm.saveQuestionBatch();

System.out.println(total);

sqlSession.commit();

sqlSession.close();

}

}

结果:

2.批量增加

sql语句:　　insert into xxx values ("xx1",'xxx1'),("xx2","xxx2"),("xx3","xxx3")

mapper接口

/*** 批量导入试题

*@paramlist 需要倒入的试题集合

*@return影响的行数

*@throwsException*/

public int saveQuestionBatch(List list)throws Exception;

xml配置

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

INSERT INTO `exam`.`questions`

VALUES(#{question.questionid},#{question.questionbankid},#{question.question},#{question.questionwithtag},#{question.answer},#{question.analysis},#{question.type},#{question.level},#{question.employeeid},#{question.uploadtime},#{question.status},#{question.knowledgetype})

测试代码:

packagecn.xm.test.mybatis;importjava.io.InputStream;importjava.util.ArrayList;importjava.util.List;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importorg.junit.Before;importorg.junit.Test;importcn.xm.bean.basebean.Questions;importcn.xm.mapper.QuestionsCustomMapper;public classMybatisTest2 {privateSqlSessionFactory sqlSessionFactory;

@Beforepublic void setUp() throwsException {//将全局配置文件作为一个流

String resource = "sqlMapConfig.xml";

String realPath= this.getClass().getClassLoader().getResource("sqlMapConfig.xml").getPath();

InputStream inputStream=Resources.getResourceAsStream(resource);//建立一个SqlSession工厂

sqlSessionFactory = newSqlSessionFactoryBuilder().build(inputStream);

}//测试批量增加

@Testpublic void testBatchAdd() throwsException {

SqlSession sqlSession=sqlSessionFactory.openSession();

QuestionsCustomMapper qsm= sqlSession.getMapper(QuestionsCustomMapper.class);

List list = new ArrayList<>();

list.add(new Questions("8", "1", "测试题目8", "", "", "", "", "", "",null, "", ""));

list.add(new Questions("9", "1", "测试题目8", "", "", "", "", "", "",null, "", ""));

list.add(new Questions("10", "1", "测试题目8", "", "", "", "", "", "",null, "", ""));

list.add(new Questions("11", "1", "测试题目8", "", "", "", "", "", "",null, "", ""));int total =qsm.saveQuestionBatch(list);

System.out.println(total);

sqlSession.commit();

sqlSession.close();

}

}

总结:传入单个List上面SQL中collection名字应该是list，输入类型是List

3.批量删除

sql语句:  DELETE FROM `exam`.`questions` WHERE `questionId` IN ('10','11','8','9')

java接口:

/*** 批量删除

*@paramids id集合

*@return删除条数

*@throwsException*/

public int deleteQuestionBatch(List ids)throws Exception;

mapper.xml

DELETE FROM `exam`.`questions` WHERE `questionId` in#{id}

测试代码:

packagecn.xm.test.mybatis;importjava.io.InputStream;importjava.util.ArrayList;importjava.util.List;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importorg.junit.Before;importorg.junit.Test;importcn.xm.bean.basebean.Questions;importcn.xm.mapper.QuestionsCustomMapper;public classMybatisTest2 {privateSqlSessionFactory sqlSessionFactory;

@Beforepublic void setUp() throwsException {//将全局配置文件作为一个流

String resource = "sqlMapConfig.xml";

String realPath= this.getClass().getClassLoader().getResource("sqlMapConfig.xml").getPath();

InputStream inputStream=Resources.getResourceAsStream(resource);//建立一个SqlSession工厂

sqlSessionFactory = newSqlSessionFactoryBuilder().build(inputStream);

}//测试批量删除

@Testpublic void testBatchDelete() throwsException {

SqlSession sqlSession=sqlSessionFactory.openSession();

QuestionsCustomMapper qsm= sqlSession.getMapper(QuestionsCustomMapper.class);

List ids = new ArrayList<>();

ids.add("8");

ids.add("9");

ids.add("10");

ids.add("11");int total =qsm.deleteQuestionBatch(ids);

System.out.println(total);

sqlSession.commit();

sqlSession.close();

}

}

4.  批量更新

需要在db链接url后面带一个参数  &allowMultiQueries=true

spring.datasource.url = jdbc:mysql://localhost:3306/fc?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true

如下:

接口方法:

void updateUsers(List users);

xml:

update user

phone = #{item.phone},

roles = #{item.roles},

id = #{item.id}

生成的SQL如下:

2019-09-12 11:23:32.397 DEBUG 16468 --- [nio-8088-exec-1] c.qs.mapper.user.UserMapper.updateUsers : ==> Preparing: update user set phone = ?, roles = ? WHERE id = ? ; update user set phone = ?, roles = ? WHERE id = ? ; update user set phone = ?, roles = ? WHERE id = ? ; update user set phone = ?, roles = ? WHERE id = ? ; update user set phone = ?, roles = ? WHERE id = ?

2019-09-12 11:23:32.397 DEBUG 16468 --- [nio-8088-exec-1] c.qs.mapper.user.UserMapper.updateUsers : ==> Parameters: 123123(String), 3(String), 1(Integer), 123123(String), 8(String), 2(Integer), 123123(String), 6(String), 3(Integer), 123123(String), 7(String), 4(Integer), 123123(String), 7(String), 5(Integer)

2019-09-12 11:23:32.613 DEBUG 16468 --- [nio-8088-exec-1] c.qs.mapper.user.UserMapper.updateUsers : <== Updates: 1

还有第二种case when 语句的批量更新，这种就不通用了。