mybatis批量删除 java_MyBatis(九)：Mybatis Java API批量操作（增、删、改、查）

最近工作中用到了mybatis的Java API方式进行开发，顺便也整理下该功能的用法，接下来会针对基本部分进行学习：

Mybatis官网给了具体的文档，但是并没有对以上用法具体介绍，因此在这里整理下，以便以后工作用到时，可以参考。

本章主要使用Mybatis中使用typeHandlers进行对Enum进行转化的用法(本章将结合Spring自动注入《Spring(二十三)：Spring自动注入的实现方式》)

本章将分为以下几部分：

1)环境搭建(maven+mybatis+spring整合)；

2)查询、批量查询；

3)新增、批量新增；

4)修改、批量修改；

5)删除、批量删除。

下面我们针对每个步骤进行详细讲解。

1)环境搭建(maven+mybatis+spring整合)

新建maven项目 Learn-Spring-01，并在pom.xml导入mybatis/spring/mysql/druid/junit包，完整的pom.xml文章如下：

4.0.0

com.dx.test

Learn-Spring-01

0.0.1-SNAPSHOT

jar

Learn-Spring-01

http://maven.apache.org

UTF-8

1.8

1.8

5.2.0.RELEASE

3.4.6

1.1.21

8.0.11

org.springframework

spring-webmvc

${org.springframework.version}

org.springframework

spring-tx

${org.springframework.version}

org.springframework

spring-jdbc

${org.springframework.version}

org.springframework

spring-core

${org.springframework.version}

org.springframework

spring-beans

${org.springframework.version}

org.springframework

spring-context

${org.springframework.version}

org.springframework

spring-context-support

${org.springframework.version}

org.springframework

spring-aop

${org.springframework.version}

aspectj

aspectjweaver

1.5.4

aspectj

aspectjrt

1.5.4

org.springframework

spring-test

${org.springframework.version}

org.mybatis

mybatis

${org.mybatis.version}

org.mybatis

mybatis-spring

2.0.3

com.alibaba

druid

${com.alibaba.version}

mysql

mysql-connector-java

${mysql.version}

junit

junit

4.12

test

View Code

在/src/main/resources下新建jdbc.properties/mybatis-config.xml/spring-config.xml配置文件：

jdbc.properties

#jdbc settings

jdbc.driver=com.mysql.cj.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false

jdbc.username=root

jdbc.password=123456

#pool settings

jdbc.pool.init=1

jdbc.pool.minIdle=3

jdbc.pool.maxActive=20

#jdbc.testSql=SELECT 'x'

jdbc.testSql=SELECT 'x' FROM DUAL

mybatis-config.xml

/p>

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

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

View Code

spring-config.xml

http://www.springframework.org/schema/beans/spring-beans-4.0.xsd

http://www.springframework.org/schema/context

http://www.springframework.org/schema/context/spring-context-4.0.xsd ">

View Code

在src/main/java/com.dx.test.module.enum包下新建DataStatus枚举类：

packagecom.dx.test.model.enums;public enumDataStatus {

Living(0), //启用

UnUsed(1); //作废

private intvalue;

DataStatus(intvalue) {this.value =value;

}public intgetValue() {return this.value;

}

}

在src/main/java/com.dx.test.model包下创建Log.java实体类：

packagecom.dx.test.model;importjava.util.Date;importcom.dx.test.model.enums.DataStatus;/*** 文章分类

**/

public classArticleCategory {private Integer id; //文章分类id

private String title; //文章分类名称

private String imgSrc; //文章分类banner图片

private String description; //文章分类描述

private DataStatus state; //记录状态

private String createUser; //新建用户

private String createUserId;//新建用户id

private Date createTime; //新建时间

private String updateUser; //修改用户

private String updateUserId;//修改用户id

private Date updateTime; //修改时间

private Integer version; //乐观锁版本号

publicInteger getId() {returnid;

}public voidsetId(Integer id) {this.id =id;

}publicString getTitle() {returntitle;

}public voidsetTitle(String title) {this.title =title;

}publicString getImgSrc() {returnimgSrc;

}public voidsetImgSrc(String imgSrc) {this.imgSrc =imgSrc;

}publicString getDescription() {returndescription;

}public voidsetDescription(String description) {this.description =description;

}publicDataStatus getState() {returnstate;

}public voidsetState(DataStatus state) {this.state =state;

}publicString getCreateUser() {returncreateUser;

}public voidsetCreateUser(String createUser) {this.createUser =createUser;

}publicString getCreateUserId() {returncreateUserId;

}public voidsetCreateUserId(String createUserId) {this.createUserId =createUserId;

}publicDate getCreateTime() {returncreateTime;

}public voidsetCreateTime(Date createTime) {this.createTime =createTime;

}publicString getUpdateUser() {returnupdateUser;

}public voidsetUpdateUser(String updateUser) {this.updateUser =updateUser;

}publicString getUpdateUserId() {returnupdateUserId;

}public voidsetUpdateUserId(String updateUserId) {this.updateUserId =updateUserId;

}publicDate getUpdateTime() {returnupdateTime;

}public voidsetUpdateTime(Date updateTime) {this.updateTime =updateTime;

}publicInteger getVersion() {returnversion;

}public voidsetVersion(Integer version) {this.version =version;

}

@OverridepublicString toString() {return "ArticleCategory [id=" + id + ", title=" + title + ", imgSrc=" + imgSrc + ", description=" +description+ "]";

}

}

View Code

在src/main/java/com.dx.test.mapper包下新建ArticleCategoryMapper.java mapper接口，目前该接口内容为空，在接口上添加上@Mapper注解。

packagecom.dx.test.mapper;importorg.apache.ibatis.annotations.Mapper;public interfaceArticleCategoryMapper{

}

在src/main/java/com.dx.test.mapper.sqlprovider下新建ArticleCategorySqlProvider.java mapper sql生成类：

packagecom.dx.test.mapper.sqlprovider;public classArticleCategorySqlProvider {

}

在src/main/test/com.dx.test下新建测试类ArticleCategoryTest.java

packagecom.dx.test;importorg.junit.runner.RunWith;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.test.context.ContextConfiguration;importorg.springframework.test.context.junit4.SpringJUnit4ClassRunner;importcom.dx.test.mapper.ArticleCategoryMapper;/*** Unit test for simple App.*/@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration({"classpath:spring-config.xml"})public classArticleCategroyTest {

@AutowiredprivateArticleCategoryMapper articleCategoryMapper;

}

2)查询、批量查询

在mapper类中新建查询、批量查询接口：

/*** +根据文章分类id,查询文件分类详情

*@paramid 文章分类id

*@return查询到的文章分类详情

**/@Options(useCache= true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)

@Results(id="articleCategoryResult",value ={

@Result(property= "id",column = "id",id = true),

@Result(property= "title",column = "title"),

@Result(property= "imgSrc",column = "img_src"),

@Result(property= "status",column = "status",javaType = DataStatus.class),

@Result(property= "description",column = "description"),

@Result(property= "createUser",column = "create_user"),

@Result(property= "createUserId",column = "create_user_id"),

@Result(property= "createTime",column = "create_time"),

@Result(property= "updateUser",column = "update_user"),

@Result(property= "updateUserId",column = "update_user_id"),

@Result(property= "updateTime",column = "update_time"),

@Result(property= "version",column = "version")

})

@Select("select * from article_category where id=#{id}")

ArticleCategory getById(Integer id);/*** 根据id列表，查询多个文章分类，返回文章分类列表

*

*@paramlist 查询id列表

*@returnreturnResult

**/@Options(useCache= true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)

@ResultMap(value="articleCategoryResult")

@SelectProvider(type= ArticleCategorySqlProvider.class, method = "getByIds")

List getByIds(@Param("list") Listlist);/*** 根据过滤条件，查询返回满足条件的文章分类id列表

*

*@paramarticleCategory 查询条件

*@returnreturnResult

**/@Options(useCache= true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)

@Select(value="select * from `article_category` where state=#{state} and `title` like CONCAT(CONCAT('%', #{title}), '%')")

ListgetIdsByCondition(ArticleCategory articleCategory);/*** +根据查询条件，查询记录。

*@paramarticleCategory 查询条件

*@return返回查询结果

**/@Options(useCache= true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)

@ResultMap(value="articleCategoryResult")

@SelectProvider(type= ArticleCategorySqlProvider.class, method = "queryList")

ListqueryList(ArticleCategory articleCategory);/*** +根据查询条件，查询记录。

*@paramarticleCategory 查询条件

*@return返回查询结果

**/@Options(useCache= true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)

@ResultMap(value="articleCategoryResult")

@Select(value="select * from `article_category` where state=#{state}")

List queryListByState(DataStatus state);

查询返回Map的用法：

/*** +根据查询条件，查询记录。

*

*@paramarticleCategory 查询条件

*@return返回查询结果*/@Options(useCache= true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)

@ResultType(value= HashMap.class)

@MapKey(value="id")

@Select(value= "select * from `article_category` where state=#{state}")

MapgetIdVsModelMap(DataStatus state);/*** +根据查询条件，查询记录。

*

*@paramarticleCategory 查询条件

*@return返回查询结果*/@Options(useCache= true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)

@ResultType(value= HashMap.class)//@Results(id="idVsTitleMapResult",//value= {//@Result(property = "key",column = "id"),//@Result(property = "value",column = "title")//}//)

@Select(value = "select id,title from `article_category` where state=#{state}")voidgetIdVsTitleMap(DataStatus state);/*** 根据条件查询记录结果，返回Map{key:id,value:title}

*@paramstate 文章分类记录状态

*@returnreturnResult

**/@Options(useCache= true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)

@ResultType(value= HashMap.class)

@Select(value= "select id,title from `article_category` where state=#{state}")

Map getIdVsTitleMapWithResultHandler(@Param("state") DataStatus state, ResultHandler> resultHandler);

在mapper sql provider类中新建sql帮助方法：

/*** +根据id集合，获取文章分类列表 SQL

*

*@parammap map

*@returnreturnResult*/

public String getByIds(final Map>map) {

List tmpList = map.get("list");if (tmpList == null ||tmpList.isEmpty()) {return null;

}

StringBuilder sql= new StringBuilder("SELECT * FROM `article_category` WHERE `id` in (");for (int i = 0; i < tmpList.size(); i++) {

sql.append("#{list[" + i + "]},");

}

sql.deleteCharAt(sql.length()- 1);

sql.append(")");

sql.append(";");returnsql.toString();

}/*** 根据查询条件，查询记录

*

*@paramarticleCategory 查询条件

*@return返回查询结果

**/

publicString queryList(ArticleCategory articleCategory) {

StringBuilder sql= newStringBuilder();

sql.append("select * from `article_category` where 1=1 ");if (articleCategory.getId() != null) {

sql.append(" AND `id`=#{id}");

}if (articleCategory.getTitle() != null) {

sql.append(" AND `title` like CONCAT(CONCAT('%', #{title}), '%')");

}if(articleCategory.getState()!=null) {

sql.append(" AND `state` = #{state}");

}if (articleCategory.getCreateTime() != null) {

sql.append(" AND `create_time` = #{createTime}");

}if (articleCategory.getCreateUser() != null) {

sql.append(" AND `create_user` = #{createUser}");

}if (articleCategory.getCreateUserId() != null) {

sql.append(" AND `create_user_id` = #{createUserId}");

}if (articleCategory.getUpdateTime() != null) {

sql.append(" AND `update_time` = #{updateTime}");

}if (articleCategory.getUpdateUser() != null) {

sql.append(" AND `update_user` = #{updateUser}");

}if (articleCategory.getUpdateUserId() != null) {

sql.append(" AND `update_user_id` = #{updateUserId}");

}if (articleCategory.getVersion() != null) {

sql.append(" AND `version` = #{version}");

}

sql.append(" ORDER BY `id` DESC");returnsql.toString();

}

在测试类中新增测试方法：

privateArticleCategory articleCategory;private final String title = "category test title";private final String titleNew = "category test title new";

@Testpublic voidtestGetById() {

ArticleCategory articleCategory= this.articleCategoryMapper.getById(this.articleCategory.getId());

Assert.assertEquals(articleCategory.getId(),this.articleCategory.getId());

Assert.assertEquals(articleCategory.getTitle(),this.title);

}

@Testpublic voidtestGetByIds() {

List idList = new ArrayList(Arrays.asList(5, 6));

List queryList = this.articleCategoryMapper.getByIds(idList);

Assert.assertEquals(queryList.size(), idList.size());

}

@Testpublic voidtestGetIdsByCondition() {

ArticleCategory articleCategory= newArticleCategory();

articleCategory.setState(DataStatus.Living);

articleCategory.setTitle("test");

List idList = this.articleCategoryMapper.getIdsByCondition(articleCategory);

Assert.assertTrue(idList.isEmpty()== false);

}

@Testpublic voidtestGetLivingIds() {

List articleCategoryList=this.articleCategoryMapper.queryListByState(DataStatus.Living);

Assert.assertTrue(articleCategoryList.isEmpty()== false);

}

@Testpublic voidtestQueryList() {

ArticleCategory queryArticleCategory= newArticleCategory();

queryArticleCategory.setTitle("test");

List queryResultList = this.articleCategoryMapper.queryList(queryArticleCategory);

Assert.assertFalse(queryResultList.size()== 0);

}

map测试代码：

@Testpublic voidtestGetIdVsModelMap() {

Map queryItems=this.articleCategoryMapper.getIdVsModelMap(DataStatus.Living);

Assert.assertTrue(queryItems.size()>0);

}

@AutowiredprivateSqlSessionFactory sqlSessionFactory;

@Testpublic voidtestGetIdVsTitleMap() {

MapResultHandler mapResultHandler=newMapResultHandler();this.sqlSessionFactory.openSession().select("com.dx.test.mapper.ArticleCategoryMapper.getIdVsTitleMap", DataStatus.Living,mapResultHandler);

@SuppressWarnings("unchecked")

Map queryMap=mapResultHandler.getMappedResults();

Assert.assertTrue(queryMap.size()>0);

}

@Testpublic voidtestGetIdVsTitleMapWithResultHandler() {

MapResultHandler mapResultHandler=newMapResultHandler();this.articleCategoryMapper.getIdVsTitleMapWithResultHandler(DataStatus.Living, mapResultHandler);

Assert.assertTrue(mapResultHandler.getMappedResults().size()>0);

}

注意：上边返回map的用法中，如果是非Map的模式时，需要使用ResultHandler来辅助实现：

packagecom.dx.test.mapper.resulthandlers;importjava.util.HashMap;importjava.util.Map;importorg.apache.ibatis.session.ResultContext;importorg.apache.ibatis.session.ResultHandler;public class MapResultHandler implementsResultHandler {private final Map mappedResults = newHashMap();

@Overridepublic voidhandleResult(ResultContext context) {

@SuppressWarnings("rawtypes")

Map map=(Map) context.getResultObject();

mappedResults.put(map.get("key"), map.get("value"));

}publicMap getMappedResults() {returnmappedResults;

}

}

3)新增、批量新增

在mapper类中新建查询、批量查询接口：

/*** +入库文章分类

*

*@paramarticleCategory 待入库实体

*@return影响条数*/@Options(useCache= true, flushCache = Options.FlushCachePolicy.TRUE, useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")

@InsertProvider(type= ArticleCategorySqlProvider.class, method = "insert")intinsert(ArticleCategory articleCategory);/*** +批量添加记录

*

*@paramarticleCategoryList 文章分类列表

*@returnreturnResult*/@Options(useCache= true, flushCache = Options.FlushCachePolicy.TRUE, useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")

@InsertProvider(type= ArticleCategorySqlProvider.class, method = "batchInsert")int batchInserts(@Param("list") final ListarticleCategoryList);/*** +批量添加记录

*

*@paramarticleCategoryList 文章分类列表

*@returnreturnResult*/@Options(useCache= true, flushCache = Options.FlushCachePolicy.TRUE, useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")

@Insert(value= "

+ "INSERT INTO `article_category`"

+ "(`title`,`img_src`,`description`,`state`,`create_time`,`create_user`,`create_user_id`,`update_time`,`update_user`,`update_user_id`,`version`)"

+ "VALUES"

+ ""

+ " (#{item.title},#{item.imgSrc},#{item.description},#{item.state},now(),#{item.createUser},#{item.createUserId},now(),#{item.updateUser},#{item.updateUserId},0)"

+ ""

+ "ON DUPLICATE KEY UPDATE `update_time` = now()"

+ "")int batchInsertsWithScript(@Param("list") final List articleCategoryList);

在mapper sql provider类中新建sql帮助方法：

/*** 生成插入文章分类的SQL

*

*@paramarticleCategory文章分类

*@return返回插入文章SQL*/

publicString insert(ArticleCategory articleCategory) {return newSQL() {

{

INSERT_INTO("article_category");

INTO_COLUMNS("title", "img_src", "description","state", "create_user", "create_user_id", "create_time","update_user", "update_user_id", "update_time", "version");

INTO_VALUES("#{title}", "#{imgSrc}", "#{description}", "#{state}", "#{createUser}", "#{createUserId}", "now()","#{updateUser}", "#{updateUserId}", "now()", "0");

}

}.toString();

}/*** 生成批量新增SQL

*

*@parammap 参数信息

*@return生成批量插入SQL语句

**/

public String batchInsert(Map>map) {

List tmpList = map.get("list");if (tmpList == null ||tmpList.isEmpty()) {return null;

}

StringBuilder sql= new StringBuilder("");

sql.append("INSERT INTO `article_category`");

sql.append("(`title`,`img_src`,`description`,`state`,`create_time`,`create_user`,`create_user_id`,`update_time`,`update_user`,`update_user_id`,`version`)");

sql.append("VALUES");for (int i = 0; i < tmpList.size(); i++) {

sql.append("(#{list[" + i + "].title},#{list[" + i + "].imgSrc},#{list[" + i + "].description},#{list["+i+"].state},now(),#{list[" + i + "].createUser},#{list[" + i + "].createUserId},now(),#{list[" + i + "].updateUser},#{list[" + i + "].updateUserId},0),");

}

sql.deleteCharAt(sql.length()- 1);

sql.append(" ON DUPLICATE KEY UPDATE `update_time` = now()");

sql.append(";");returnsql.toString();

}

在测试类中新增测试方法：

@Testpublic voidtestInsert() {

ArticleCategory articleCategory= newArticleCategory();

articleCategory.setTitle(title);

articleCategory.setDescription("category description");

articleCategory.setImgSrc("http://www.test.com/img/category/img-" + new Random().nextInt(1000) + ".gif");

articleCategory.setState(DataStatus.Living);

articleCategory.setCreateTime(newDate());

articleCategory.setCreateUser("create user");

articleCategory.setCreateUserId("user-" + new Random().nextInt(1000));int result = this.articleCategoryMapper.insert(articleCategory);this.articleCategory =articleCategory;

Assert.assertEquals(result,1);

}

@Testpublic voidtestBatchInsert() {

List articleCategoryList = new ArrayList();for (int i = 0; i < 10; i++) {

ArticleCategory articleCategory= newArticleCategory();

articleCategory.setTitle(title+i);

articleCategory.setState(DataStatus.Living);

articleCategory.setDescription("category description");

articleCategory.setImgSrc("http://www.test.com/img/category/img-" + new Random().nextInt(1000) + ".gif");

articleCategory.setCreateTime(newDate());

articleCategory.setCreateUser("create user");

articleCategory.setCreateUserId("user-" + new Random().nextInt(1000));

articleCategoryList.add(articleCategory);

}int result = this.articleCategoryMapper.batchInserts(articleCategoryList);

Assert.assertEquals(result,10);

}

@Testpublic voidtestBatchInsertWithScript() {

List articleCategoryList = new ArrayList();for (int i = 0; i < 10; i++) {

ArticleCategory articleCategory= newArticleCategory();

articleCategory.setTitle(title+i);

articleCategory.setState(DataStatus.Living);

articleCategory.setDescription("category description");

articleCategory.setImgSrc("http://www.test.com/img/category/img-" + new Random().nextInt(1000) + ".gif");

articleCategory.setCreateTime(newDate());

articleCategory.setCreateUser("create user");

articleCategory.setCreateUserId("user-" + new Random().nextInt(1000));

articleCategoryList.add(articleCategory);

}int result = this.articleCategoryMapper.batchInsertsWithScript(articleCategoryList);

Assert.assertEquals(result,10);

}

4)修改、批量修改

在mapper类中新建查询、批量查询接口：

/*** +根据文章id，删除文章

*

*@paramid 文章id

*@return影响条数*/@Options(useCache= true, flushCache =Options.FlushCachePolicy.TRUE)

@UpdateProvider(type= ArticleCategorySqlProvider.class, method = "update")intupdate(ArticleCategory articleCategory);/*** +批量新增

*@paramarticleCategoryList 待修改对象

*@return影响条数

**/@Options(useCache= true, flushCache =Options.FlushCachePolicy.TRUE)

@UpdateProvider(type= ArticleCategorySqlProvider.class, method = "batchUpdate")int batchUpdate(ListarticleCategoryList);/*** +批量新增

*@paramarticleCategoryList 待修改对象

*@return影响条数

**/@Options(useCache= true, flushCache =Options.FlushCachePolicy.TRUE)

@Update(value="

+ ""

+ "update `article_category` "

+ "" +

" `id`=#{item.id} "

+ " " +

" ,`title` = #{item.title} " +

" " +

" " +

" ,`img_src` = #{item.imgSrc} " +

" " +

" " +

" ,`description` = #{item.description} " +

" " +

" " +

" ,`state` = #{item.state} " +

" " +

",`update_time` = now(), `update_user` = #{item.updateUser}, `update_user_id` = #{item.updateUserId}, `version` = `version` + 1 "

+ ""

+ "where `id` = #{item.id} and `version`=#{item.version}"

+ ""

+ "")int batchUpdateWithScript(List articleCategoryList);

在mapper sql provider类中新建sql帮助方法：

/*** 生成修改文章分类SQL

*

*@paramarticleCategory 文章分类实体

*@return返回修改文章分类SQL*/

publicString update(ArticleCategory articleCategory) {

StringBuilder sql= newStringBuilder();

sql.append("update `article_category` set id=#{id}");if (articleCategory.getTitle() != null) {

sql.append(", `title`=#{title}");

}if (articleCategory.getImgSrc() != null) {

sql.append(", `img_src`=#{imgSrc}");

}if (articleCategory.getDescription() != null) {

sql.append(", `description`=#{description}");

}if(articleCategory.getState()!=null) {

sql.append(", `state` = #{state}");

}if (articleCategory.getCreateUser() != null) {

sql.append(", `create_user` = #{createUser}");

}if (articleCategory.getCreateUserId() != null) {

sql.append(", `create_user_id` = #{createUserId}");

}

sql.append(", `update_time` = now()");if (articleCategory.getUpdateUser() != null) {

sql.append(", `update_user` = #{updateUser}");

}if (articleCategory.getUpdateUserId() != null) {

sql.append(", `update_user_id` = #{updateUserId}");

}

sql.append(", `version` = `version` + 1");

sql.append(" WHERE `id` = #{id} AND `version` = #{version}");returnsql.toString();

}/*** 生成批量更新SQL

*

*@parammap 查询参数

*@return返回生成的批量更新语句

**/

public String batchUpdate(Map>map) {

List tmpList = map.get("list");if (tmpList == null ||tmpList.isEmpty()) {return null;

}

StringBuilder sql= new StringBuilder("");for (int i = 0; i < tmpList.size(); i++) {

ArticleCategory articleCategory=tmpList.get(i);

sql.append("update `article_category` set id=#{list["+i+"].id}");if (articleCategory.getTitle() != null) {

sql.append(", `title`=#{list["+i+"].title}");

}if (articleCategory.getImgSrc() != null) {

sql.append(", `img_src`=#{list["+i+"].imgSrc}");

}if (articleCategory.getDescription() != null) {

sql.append(", `description`=#{list["+i+"].description}");

}if(articleCategory.getState()!=null) {

sql.append(", `state` = #{list["+i+"].state}");

}if (articleCategory.getCreateUser() != null) {

sql.append(", `create_user` = #{list["+i+"].createUser}");

}if (articleCategory.getCreateUserId() != null) {

sql.append(", `create_user_id` = #{list["+i+"].createUserId}");

}

sql.append(", `update_time` = now()");if (articleCategory.getUpdateUser() != null) {

sql.append(", `update_user` = #{list["+i+"].updateUser}");

}if (articleCategory.getUpdateUserId() != null) {

sql.append(", `update_user_id` = #{list["+i+"].updateUserId}");

}

sql.append(", `version` = `version` + 1");

sql.append(" WHERE `id` = #{list["+i+"].id} AND `version` = #{list["+i+"].version}");

sql.append(";");

}returnsql.toString();

}

在测试类中新增测试方法：

@Testpublic voidtestUpdate() {

ArticleCategory articleCategory= this.articleCategoryMapper.getById(this.articleCategory.getId());

Assert.assertEquals(articleCategory.getId(),this.articleCategory.getId());

Assert.assertEquals(articleCategory.getTitle(),this.title);

articleCategory.setTitle(this.titleNew);int result = this.articleCategoryMapper.update(articleCategory);

Assert.assertEquals(result,1);

articleCategory= this.articleCategoryMapper.getById(this.articleCategory.getId());

Assert.assertEquals(articleCategory.getId(),this.articleCategory.getId());

Assert.assertEquals(articleCategory.getTitle(),this.title);

}

@Testpublic voidtestBatchUpdate() {

ArticleCategory queryArticleCategory= newArticleCategory();

queryArticleCategory.setTitle("ccccc");

List queryItems = this.articleCategoryMapper.queryList(queryArticleCategory);for(ArticleCategory articleCategory : queryItems) {

articleCategory.setTitle("DDD");

}int result = this.articleCategoryMapper.batchUpdate(queryItems);

Assert.assertEquals(result,1);

}

@Testpublic voidtestBatchUpdateWithScript() {

ArticleCategory queryArticleCategory= newArticleCategory();

queryArticleCategory.setTitle("DDD");

List queryItems = this.articleCategoryMapper.queryList(queryArticleCategory);for(ArticleCategory articleCategory : queryItems) {

articleCategory.setTitle("ccccc");

}int result = this.articleCategoryMapper.batchUpdateWithScript(queryItems);

Assert.assertEquals(result,1);

}

测试执行批量插入，按照上边jdbc.properties和spring-config.xml配置中会抛出以下异常：

Caused by: java.sql.SQLException: sql injection violation, multi-statement not allow : update `article_category` set id=?, `title`=?, `img_src`=?, `description`=?, `state` = ?, `create_user` = ?, `create_user_id` = ?, `update_time` = now(), `version` = `version` + 1 WHERE `id` = ? AND `version` = ?;update `article_category` set id=?, `title`=?, `img_src`=?, `description`=?, `state` = ?, `create_user` = ?, `create_user_id` = ?, `update_time` = now(), `version` = `version` + 1 WHERE `id` = ? AND `version` = ?;update `article_category` set id=?, `title`=?, `img_src`=?, `description`=?, `state` = ?, `create_user` = ?, `create_user_id` = ?, `update_time` = now(), `version` = `version` + 1 WHERE `id` = ? AND `version` = ?;update `article_category` set id=?, `title`=?, `img_src`=?, `description`=?, `state` = ?, `create_user` = ?, `create_user_id` = ?, `update_time` = now(), `version` = `version` + 1 WHERE `id` = ? AND `version` = ?;

at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:808)

at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:294)

at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:610)

at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:943)

at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:143)

at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:610)

at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:374)

at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:388)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)

at com.sun.proxy.$Proxy38.prepareStatement(Unknown Source)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:85)

at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)

at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85)

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)

...37 more

此时出现错误原因，多个sql语句执行被过滤器过滤掉了。

问题解决：

1)需要在jdbc.properties中url中添加allowMultiQueries=true

#jdbc settings

jdbc.driver=com.mysql.cj.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/mydb?allowMultiQueries=true&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false

jdbc.username=root

jdbc.password=123456#pool settings

jdbc.pool.init=1jdbc.pool.minIdle=3jdbc.pool.maxActive=20#jdbc.testSql=SELECT 'x'jdbc.testSql=SELECT 'x' FROM DUAL

2)在spring-config.xml中修改，添加wall-filter/stat-filter，并引入dataSource下。

http://www.springframework.org/schema/beans/spring-beans-4.0.xsd

http://www.springframework.org/schema/context

http://www.springframework.org/schema/context/spring-context-4.0.xsd ">

上边加粗本分是要修改的部分。

5)删除、批量删除

在mapper类中新建查询、批量查询接口：

/*** +根据文章分类id，删除文章分类

*

*@paramid 文章分类id

*@return影响条数*/@Options(useCache= true, flushCache =Options.FlushCachePolicy.TRUE)

@Delete("delete from article_category where id=#{id}")intdelete(Integer id);/*** +根据文章分类id集合，删除文章分类

*

*@paramlist 文章分类id集合

*@return影响条数*/@Options(useCache= true, flushCache =Options.FlushCachePolicy.TRUE)

@DeleteProvider(type= ArticleCategorySqlProvider.class, method = "batchDelete")int batchDelete(@Param("list") Listlist);/*** +批量删除记录

*

*@paramlist 待删除id集合

*@returnreturnResult*/@Options(useCache= true, flushCache =Options.FlushCachePolicy.TRUE)

@Delete("

"delete from `article_category` where `id` in " +

"" +

"#{item}" +

"" +

"")int batchDeleteWithScript(@Param("list") List list);

在mapper sql provider类中新建sql帮助方法：

/*** +根据id集合，获取文章分类列表 SQL

*

*@parammap map

*@returnreturnResult*/

public String batchDelete(final Map>map) {

List tmpList = map.get("list");if (tmpList == null ||tmpList.isEmpty()) {return null;

}

StringBuilder sql= new StringBuilder("DELETE FROM `article_category` WHERE `id` in (");for (int i = 0; i < tmpList.size(); i++) {

sql.append("#{list[" + i + "]},");

}

sql.deleteCharAt(sql.length()- 1);

sql.append(")");

sql.append(";");returnsql.toString();

}

在测试类中新增测试方法：

@Testpublic voidtestDelete() {int result = this.articleCategoryMapper.delete(this.articleCategory.getId());

Assert.assertEquals(result,1);

}

@Testpublic voidtestBatchDelete() {

List idList = new ArrayList(Arrays.asList(1, 2));int result = this.articleCategoryMapper.batchDelete(idList);

Assert.assertEquals(result, idList.size());

}

@Testpublic voidtestBatchDeleteWithScript() {

List idList = new ArrayList(Arrays.asList(3, 4));int result = this.articleCategoryMapper.batchDeleteWithScript(idList);

Assert.assertEquals(result, idList.size());

}