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

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

1）Java API处理一对多、多对一的用法；

2）增、删、改、查的用法；

3）涉及到类型转化的用法；

4）批量修改、批量查询、批量新增的用法(介绍的方案只使用于小批量数据处理，如果大批量数据处理，还需要使用ExecutorType.BATCH)。

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文章如下：

<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>com.dx.test</groupId>
    <artifactId>Learn-Spring-01</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>Learn-Spring-01</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <!--Spring版本号 -->
        <org.springframework.version>5.2.0.RELEASE</org.springframework.version>
        <org.mybatis.version>3.4.6</org.mybatis.version>
        <com.alibaba.version>1.1.21</com.alibaba.version>
        <mysql.version>8.0.11</mysql.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.5.4</version>
        </dependency>
        <dependency>
            <groupId>aspectj</groupId>
            <artifactId>aspectjrt</artifactId>
            <version>1.5.4</version>
        </dependency>
        <!-- Sping Test相关依赖 -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>

        <!--MyBatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${org.mybatis.version}</version>
        </dependency>
        <!-- Mybatis自身实现的Spring整合依赖 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>2.0.3</version>
        </dependency>


        <!--MySql数据库驱动 -->
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${com.alibaba.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>
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

<?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>
    <!--配置全局属性-->
    <settings>
        <!-- 打开延迟加载的开关 -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 将积极加载改为消极加载（即按需加载） -->
        <setting name="aggressiveLazyLoading" value="false"/>
        <!-- 打开全局缓存开关（二级缓存）默认值就是 true -->
        <setting name="cacheEnabled" value="true"/>
        <!--使用jdbc的getGeneratekeys获取自增主键值-->
        <setting name="useGeneratedKeys" value="true"/>
        <!--使用列别名替换别名　　默认true select name as title form table; -->
        <setting name="useColumnLabel" value="true"/>
        <!--开启驼峰命名转换-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--打印sql日志-->
        <setting name="logImpl" value="STDOUT_LOGGING" />
    </settings>
    
    <!-- 引用db.properties配置文件 -->
    <!-- 
    <properties resource="db.properties"/> 
    <typeAliases>
        <package name="com.dx.test.model"/>
    </typeAliases>
    -->
    
    <!-- 元素类型为 "configuration" 的内容必须匹配 "(properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,reflectorFactory?,plugins?,environments?,databaseIdProvider?,mappers?)"。-->
    <typeHandlers>
        <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.dx.test.model.enums.ModuleType"/>
        <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.dx.test.model.enums.OperateType"/>
        <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.dx.test.model.enums.DataStatus"/>
    </typeHandlers>
    
    <!-- 对事务的管理和连接池的配置 -->
    <!-- 
    <environments default="mysql_jdbc">
        <environment id="oracle_jdbc">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>

        <environment id="mysql_jdbc">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    -->
    
    <!-- 当使用了xxxMapper.xml时，需要配置这些配置 -->
    <!--
    <mappers>
        <mapper resource="resources/mapper/TaskAutoExecutePlanMapper.xml"/>
    </mappers>
    -->
    <!-- 因在Mapper类上加入了@Mapper注解，因此这个配置也不需要了。 -->
    <!-- 
    <mappers>
        <mapper class="com.dx.test.mapper.ArticleCategoryMapper"></mapper>
        <mapper class="com.dx.test.mapper.ArticleMapper"></mapper>
    </mappers>
     -->
</configuration>
View Code

spring-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
 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 ">

    <!-- bean annotation driven -->
    <context:annotation-config />
    <context:component-scan base-package="com.dx.test.repository,com.dx.test.mapper,com.dx.test.service" />

    <!-- 配置整合Mybatis过程 -->
    <!-- 配置数据库相关参数 properties的属性：${url} -->
    <context:property-placeholder location="classpath:jdbc.properties" />
    <!-- 
    <bean id="propertyPlaceholderConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> 
        <property name="locations">
         <list>
          <value>classpath:jdbc.properties</value> 
         </list> 
        </property> 
    </bean> 
    -->

    <!--2.配置连接池属性 -->
    <!-- 数据源配置, 使用 Druid 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <!-- 数据源驱动类可不写，Druid默认会自动根据URL识别DriverClass -->
        <property name="driverClassName" value="${jdbc.driver}" />

        <!-- 基本属性 url、user、password -->
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />

        <!-- 配置初始化大小、最小、最大 -->
        <property name="initialSize" value="${jdbc.pool.init}" />
        <property name="minIdle" value="${jdbc.pool.minIdle}" />
        <property name="maxActive" value="${jdbc.pool.maxActive}" />

        <!-- 配置获取连接等待超时的时间 -->
        <property name="maxWait" value="60000" />

        <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />

        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="300000" />

        <property name="validationQuery" value="${jdbc.testSql}" />
        <property name="testWhileIdle" value="true" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />
        <!-- 打开PSCache，并且指定每个连接上PSCache的大小（Oracle使用） -->
        <!-- <property name="poolPreparedStatements" value="true"/> <property name="maxPoolPreparedStatementPerConnectionSize" 
            value="20"/> -->
        <!-- 配置监控统计拦截的filters: 监控统计用的filter:stat 日志用的filter:log4j 防御sql注入的filter:wall -->
        <property name="filters" value="stat,wall" />
    </bean>

    <!--3.配置SqlSessionFactory对象 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--注入数据库连接池 -->
        <property name="dataSource" ref="dataSource" />
        <!--配置mybatis全局配置文件:mybatis-config.xml -->
        <property name="configLocation"
            value="classpath:mybatis-config.xml" />
        <!-- 因为我们这采用的是Mybatis Java API方式，因此不需要配置 -->
        <!--扫描entity包,使用别名,多个用;隔开 -->
        <!-- <property name="typeAliasesPackage" value="com.dx.test.model"/> -->
        <!--扫描sql配置文件:mapper需要的xml文件 -->
        <!-- <property name="mapperLocations" value="classpath:mapper/*.xml"/> -->
    </bean>

    <!-- Mapper接口所在包名，Spring会自动查找其下的类 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.dx.test.mapper" />
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
    </bean>

    <!-- 如果想对单个mapper类关联上sqlSessionFactory，可以这么使用，具体参考：http://mybatis.org/spring/getting-started.html -->
    <!-- <bean id="articlerMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"> 
        <property name="mapperInterface" value="com.dx.test.dao.mapper.ArticleMapper" 
        /> <property name="sqlSessionFactory" ref="sqlSessionFactory" /> </bean> -->

    <!-- (事务管理)transaction manager, use JtaTransactionManager for global tx -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>
</beans>
View Code

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

package com.dx.test.model.enums;

public enum DataStatus {
    Living(0), // 启用 
    UnUsed(1); // 作废

    private int value;

    DataStatus(int value) {
        this.value = value;
    }

    public int getValue() {
        return this.value;
    }
}

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

package com.dx.test.model;

import java.util.Date;

import com.dx.test.model.enums.DataStatus;

/**
 * 文章分类
 * */
public class ArticleCategory {
    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; // 乐观锁版本号
    
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getImgSrc() {
        return imgSrc;
    }
    public void setImgSrc(String imgSrc) {
        this.imgSrc = imgSrc;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    public DataStatus getState() {
        return state;
    }
    public void setState(DataStatus state) {
        this.state = state;
    }
    public String getCreateUser() {
        return createUser;
    }
    public void setCreateUser(String createUser) {
        this.createUser = createUser;
    }
    public String getCreateUserId() {
        return createUserId;
    }
    public void setCreateUserId(String createUserId) {
        this.createUserId = createUserId;
    }
    public Date getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
    public String getUpdateUser() {
        return updateUser;
    }
    public void setUpdateUser(String updateUser) {
        this.updateUser = updateUser;
    }
    public String getUpdateUserId() {
        return updateUserId;
    }
    public void setUpdateUserId(String updateUserId) {
        this.updateUserId = updateUserId;
    }
    public Date getUpdateTime() {
        return updateTime;
    }
    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }
    public Integer getVersion() {
        return version;
    }
    public void setVersion(Integer version) {
        this.version = version;
    }
    @Override
    public String toString() {
        return "ArticleCategory [id=" + id + ", title=" + title + ", imgSrc=" + imgSrc + ", description=" + description
                + "]";
    }
}
View Code

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

package com.dx.test.mapper;
import org.apache.ibatis.annotations.Mapper;

public interface ArticleCategoryMapper{

}

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

package com.dx.test.mapper.sqlprovider;

public class ArticleCategorySqlProvider {

}

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

package com.dx.test;

import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.dx.test.mapper.ArticleCategoryMapper;

/**
 * Unit test for simple App.
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({ "classpath:spring-config.xml" })
public class ArticleCategroyTest {
    @Autowired
    private ArticleCategoryMapper articleCategoryMapper;

}

2）查询、批量查询

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

    /**
     * +根据文章分类id,查询文件分类详情
     * @param id 文章分类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列表，查询多个文章分类，返回文章分类列表
     * 
     * @param list 查询id列表
     * @return returnResult
     * */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @ResultMap(value ="articleCategoryResult")
    @SelectProvider(type = ArticleCategorySqlProvider.class, method = "getByIds")
    List<ArticleCategory> getByIds(@Param("list") List<Integer> list);
    
    /**
     * 根据过滤条件，查询返回满足条件的文章分类id列表
     * 
     * @param articleCategory 查询条件
     * @return returnResult
     * */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @Select(value="select * from `article_category` where state=#{state} and `title` like CONCAT(CONCAT('%', #{title}), '%')")
    List<Integer> getIdsByCondition(ArticleCategory articleCategory);
    
    /**
     * +根据查询条件，查询记录。
     * @param articleCategory 查询条件
     * @return 返回查询结果
     * */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @ResultMap(value ="articleCategoryResult")
    @SelectProvider(type = ArticleCategorySqlProvider.class, method = "queryList")
    List<ArticleCategory> queryList(ArticleCategory articleCategory);
    
    /**
     * +根据查询条件，查询记录。
     * @param articleCategory 查询条件
     * @return 返回查询结果
     * */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @ResultMap(value ="articleCategoryResult")
    @Select(value="select * from `article_category` where state=#{state}")
    List<ArticleCategory> queryListByState(DataStatus state);

查询返回Map的用法：

    /**
     * +根据查询条件，查询记录。
     * 
     * @param articleCategory 查询条件
     * @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}")
    Map<Integer, ArticleCategory> getIdVsModelMap(DataStatus state);

    /**
     * +根据查询条件，查询记录。
     * 
     * @param articleCategory 查询条件
     * @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}")
    void getIdVsTitleMap(DataStatus state);
    
    /**
     * 根据条件查询记录结果，返回Map{key:id,value:title}
     * @param state 文章分类记录状态
     * @return returnResult
     * */
    @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<Integer,String> getIdVsTitleMapWithResultHandler(@Param("state") DataStatus state, ResultHandler<?> resultHandler);

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

     /**
     * +根据id集合，获取文章分类列表 SQL
     *
     * @param map map
     * @return returnResult
     */
    public String getByIds(final Map<String, List<Integer>> map) {
        List<Integer> 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(";");

        return sql.toString();
    }

    /**
     * 根据查询条件，查询记录
     * 
     * @param articleCategory 查询条件
     * @return 返回查询结果
     * */
    public String queryList(ArticleCategory articleCategory) {
        StringBuilder sql = new StringBuilder();
        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");

        return sql.toString();
    }

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

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

    @Test
    public void testGetById() {
        ArticleCategory articleCategory = this.articleCategoryMapper.getById(this.articleCategory.getId());
        Assert.assertEquals(articleCategory.getId(), this.articleCategory.getId());
        Assert.assertEquals(articleCategory.getTitle(), this.title);
    }

    @Test
    public void testGetByIds() {
        List<Integer> idList = new ArrayList<Integer>(Arrays.asList(5, 6));
        List<ArticleCategory> queryList = this.articleCategoryMapper.getByIds(idList);
        Assert.assertEquals(queryList.size(), idList.size());
    }

    @Test
    public void testGetIdsByCondition() {
        ArticleCategory articleCategory = new ArticleCategory();
        articleCategory.setState(DataStatus.Living);
        articleCategory.setTitle("test");

        List<Integer> idList = this.articleCategoryMapper.getIdsByCondition(articleCategory);
        Assert.assertTrue(idList.isEmpty() == false);
    }
    
    @Test
    public void testGetLivingIds() {
        List<ArticleCategory> articleCategoryList=this.articleCategoryMapper.queryListByState(DataStatus.Living);
        Assert.assertTrue(articleCategoryList.isEmpty() == false);
    }

    @Test
    public void testQueryList() {
        ArticleCategory queryArticleCategory = new ArticleCategory();
        queryArticleCategory.setTitle("test");

        List<ArticleCategory> queryResultList = this.articleCategoryMapper.queryList(queryArticleCategory);
        Assert.assertFalse(queryResultList.size() == 0);
    }

map测试代码：

    @Test
    public void testGetIdVsModelMap() {
        Map<Integer,ArticleCategory> queryItems=this.articleCategoryMapper.getIdVsModelMap(DataStatus.Living);
        Assert.assertTrue(queryItems.size()>0);
    }
    
    @Autowired
    private SqlSessionFactory sqlSessionFactory;
    
    @Test
    public void testGetIdVsTitleMap() {
        MapResultHandler mapResultHandler=new MapResultHandler();
        this.sqlSessionFactory.openSession().select("com.dx.test.mapper.ArticleCategoryMapper.getIdVsTitleMap", DataStatus.Living,mapResultHandler);
        @SuppressWarnings("unchecked")
        Map<Integer, String> queryMap=mapResultHandler.getMappedResults();
        
        Assert.assertTrue(queryMap.size()>0);
    }

    @Test
    public void testGetIdVsTitleMapWithResultHandler() {
        MapResultHandler mapResultHandler=new MapResultHandler();
        this.articleCategoryMapper.getIdVsTitleMapWithResultHandler(DataStatus.Living, mapResultHandler);
        Assert.assertTrue(mapResultHandler.getMappedResults().size()>0);
    }

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

package com.dx.test.mapper.resulthandlers;

import java.util.HashMap;
import java.util.Map;

import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;

public class MapResultHandler implements ResultHandler {
    private final Map mappedResults = new HashMap();

    @Override
    public void handleResult(ResultContext context) {
        @SuppressWarnings("rawtypes")
        Map map = (Map) context.getResultObject();
        mappedResults.put(map.get("key"), map.get("value"));
    }

    public Map getMappedResults() {
        return mappedResults;
    }
}

3）新增、批量新增

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

    /**
     * +入库文章分类
     * 
     * @param articleCategory 待入库实体
     * @return 影响条数
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE, useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    @InsertProvider(type = ArticleCategorySqlProvider.class, method = "insert")
    int insert(ArticleCategory articleCategory);
     /**
     * +批量添加记录
     *
     * @param articleCategoryList 文章分类列表 
     * @return returnResult
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE, useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    @InsertProvider(type = ArticleCategorySqlProvider.class, method = "batchInsert")
    int batchInserts(@Param("list") final List<ArticleCategory> articleCategoryList);
    
    /**
     * +批量添加记录
     *
     * @param articleCategoryList 文章分类列表 
     * @return returnResult
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE, useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    @Insert(value = "<script>"
            + "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"
            + "<foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\">"
            + "     (#{item.title},#{item.imgSrc},#{item.description},#{item.state},now(),#{item.createUser},#{item.createUserId},now(),#{item.updateUser},#{item.updateUserId},0)"
            + "</foreach>"
            + "ON DUPLICATE KEY UPDATE `update_time` = now()"
            + "</script>")
    int batchInsertsWithScript(@Param("list") final List<ArticleCategory> articleCategoryList);

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

    /**
     * 生成插入文章分类的SQL
     * 
     * @param articleCategory文章分类
     * @return 返回插入文章SQL
     */
    public String insert(ArticleCategory articleCategory) {
        return new SQL() {
            {
                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
     * 
     * @param map 参数信息
     * @return 生成批量插入SQL语句
     * */
    public String batchInsert(Map<String, List<ArticleCategory>> map) {
        List<ArticleCategory> 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(";");

        return sql.toString();
    }

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

    @Test
    public void testInsert() {
        ArticleCategory articleCategory = new ArticleCategory();
        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(new Date());
        articleCategory.setCreateUser("create user");
        articleCategory.setCreateUserId("user-" + new Random().nextInt(1000));

        int result = this.articleCategoryMapper.insert(articleCategory);
        this.articleCategory = articleCategory;
        Assert.assertEquals(result, 1);
    }

    @Test
    public void testBatchInsert() {
        List<ArticleCategory> articleCategoryList = new ArrayList<ArticleCategory>();
        for (int i = 0; i < 10; i++) {
            ArticleCategory articleCategory = new ArticleCategory();
            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(new Date());
            articleCategory.setCreateUser("create user");
            articleCategory.setCreateUserId("user-" + new Random().nextInt(1000));

            articleCategoryList.add(articleCategory);
        }

        int result = this.articleCategoryMapper.batchInserts(articleCategoryList);
        Assert.assertEquals(result, 10);
    }

    @Test
    public void testBatchInsertWithScript() {
        List<ArticleCategory> articleCategoryList = new ArrayList<ArticleCategory>();
        for (int i = 0; i < 10; i++) {
            ArticleCategory articleCategory = new ArticleCategory();
            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(new Date());
            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，删除文章
     * 
     * @param id 文章id
     * @return 影响条数
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @UpdateProvider(type = ArticleCategorySqlProvider.class, method = "update")
    int update(ArticleCategory articleCategory);

    /**
     * +批量新增
     * @param articleCategoryList 待修改对象
     * @return 影响条数
     * */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @UpdateProvider(type = ArticleCategorySqlProvider.class, method = "batchUpdate")
    int batchUpdate(List<ArticleCategory> articleCategoryList);
    
    /**
     * +批量新增
     * @param articleCategoryList 待修改对象
     * @return 影响条数
     * */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @Update(value="<script>"
            + "<foreach item=\"item\" collection=\"list\" open=\"\" separator=\";\" close=\"\">"
            + "update `article_category` "
            + "<set>" +
            " `id`=#{item.id} "
            + " <if test='item.title !=null and item.title != \"\"'>" +
            "       ,`title` = #{item.title} " +
            "  </if> " +
            " <if test='item.imgSrc !=null'>" +
            "       ,`img_src` = #{item.imgSrc} " +
            "  </if> " +
            " <if test='item.description !=null and item.description != \"\"'>" +
            "       ,`description` = #{item.description} " +
            "  </if> " +
            " <if test='item.state !=null'>" +
            "       ,`state` = #{item.state} " +
            "  </if> " +
            ",`update_time` = now(), `update_user` = #{item.updateUser}, `update_user_id` = #{item.updateUserId}, `version` = `version` + 1 "
            + "</set>"
            + "where `id` = #{item.id} and `version`=#{item.version}"
            + "</foreach>"
            + "</script>")
    int batchUpdateWithScript(List<ArticleCategory> articleCategoryList);

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

    /**
     * 生成修改文章分类SQL
     * 
     * @param articleCategory 文章分类实体
     * @return 返回修改文章分类SQL
     */
    public String update(ArticleCategory articleCategory) {
        StringBuilder sql = new StringBuilder();

        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}");

        return sql.toString();
    }

    /**
     * 生成批量更新SQL
     * 
     * @param map 查询参数
     * @return 返回生成的批量更新语句
     * */
    public String batchUpdate(Map<String, List<ArticleCategory>> map) {
        List<ArticleCategory> 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(";");
        }

        return sql.toString();
    }

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

    @Test
    public void testUpdate() {
        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);
    }
    
    @Test
    public void testBatchUpdate() {
        ArticleCategory queryArticleCategory = new ArticleCategory();
        queryArticleCategory.setTitle("ccccc");
        List<ArticleCategory> queryItems = this.articleCategoryMapper.queryList(queryArticleCategory);
        for (ArticleCategory articleCategory : queryItems) {
            articleCategory.setTitle("DDD");
        }

        int result = this.articleCategoryMapper.batchUpdate(queryItems);
        Assert.assertEquals(result, 1);
    }

    @Test
    public void testBatchUpdateWithScript() {
        ArticleCategory queryArticleCategory = new ArticleCategory();
        queryArticleCategory.setTitle("DDD");
        List<ArticleCategory> 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=1
jdbc.pool.minIdle=3
jdbc.pool.maxActive=20

#jdbc.testSql=SELECT 'x'
jdbc.testSql=SELECT 'x' FROM DUAL

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

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
 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 ">

    <!-- bean annotation driven -->
    <context:annotation-config />
    <context:component-scan base-package="com.dx.test.repository,com.dx.test.mapper,com.dx.test.service" />

    <!-- 配置整合Mybatis过程 -->
    <!-- 配置数据库相关参数 properties的属性：${url} -->
    <context:property-placeholder location="classpath:jdbc.properties" />
    <!-- 
    <bean id="propertyPlaceholderConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> 
        <property name="locations">
         <list>
          <value>classpath:jdbc.properties</value> 
         </list> 
        </property> 
    </bean> 
    -->

    <!--2.配置连接池属性 -->
    <!-- 数据源配置, 使用 Druid 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <!-- 数据源驱动类可不写，Druid默认会自动根据URL识别DriverClass -->
        <property name="driverClassName" value="${jdbc.driver}" />

        <!-- 基本属性 url、user、password -->
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />

        <!-- 配置初始化大小、最小、最大 -->
        <property name="initialSize" value="${jdbc.pool.init}" />
        <property name="minIdle" value="${jdbc.pool.minIdle}" />
        <property name="maxActive" value="${jdbc.pool.maxActive}" />

        <!-- 配置获取连接等待超时的时间 -->
        <property name="maxWait" value="60000" />

        <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />

        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="300000" />

        <property name="validationQuery" value="${jdbc.testSql}" />
        <property name="testWhileIdle" value="true" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />
        <!-- 打开PSCache，并且指定每个连接上PSCache的大小（Oracle使用） -->
        <!-- <property name="poolPreparedStatements" value="true"/> <property name="maxPoolPreparedStatementPerConnectionSize" 
            value="20"/> -->
        <property name="proxyFilters">
            <list>
                <ref bean="stat-filter" />
                <ref bean="wall-filter" />
            </list>
        </property>
        <!-- 配置监控统计拦截的filters: 监控统计用的filter:stat 日志用的filter:log4j 防御sql注入的filter:wall -->
        <property name="filters" value="stat,wall" />
    </bean>
    <bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter">
        <property name="slowSqlMillis" value="30000" />
        <property name="logSlowSql" value="true" />
        <property name="mergeSql" value="true" />
    </bean>

    <bean id="wall-filter" class="com.alibaba.druid.wall.WallFilter">
        <property name="dbType" value="mysql" />
        <property name="config" ref="wall-config" />
    </bean>

    <bean id="wall-config" class="com.alibaba.druid.wall.WallConfig">
        <!-- 批量sql -->
        <property name="multiStatementAllow" value="true" />
    </bean>

    <!--3.配置SqlSessionFactory对象 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--注入数据库连接池 -->
        <property name="dataSource" ref="dataSource" />
        <!--配置mybatis全局配置文件:mybatis-config.xml -->
        <property name="configLocation"
            value="classpath:mybatis-config.xml" />
        <!-- 因为我们这采用的是Mybatis Java API方式，因此不需要配置 -->
        <!--扫描entity包,使用别名,多个用;隔开 -->
        <!-- <property name="typeAliasesPackage" value="com.dx.test.model"/> -->
        <!--扫描sql配置文件:mapper需要的xml文件 -->
        <!-- <property name="mapperLocations" value="classpath:mapper/*.xml"/> -->
    </bean>

    <!-- Mapper接口所在包名，Spring会自动查找其下的类 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.dx.test.mapper" />
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
    </bean>

    <!-- 如果想对单个mapper类关联上sqlSessionFactory，可以这么使用，具体参考：http://mybatis.org/spring/getting-started.html -->
    <!-- <bean id="articlerMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"> 
        <property name="mapperInterface" value="com.dx.test.dao.mapper.ArticleMapper" 
        /> <property name="sqlSessionFactory" ref="sqlSessionFactory" /> </bean> -->

    <!-- (事务管理)transaction manager, use JtaTransactionManager for global tx -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>
</beans>

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

5）删除、批量删除

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

    /**
     * +根据文章分类id，删除文章分类
     * 
     * @param id 文章分类id
     * @return 影响条数
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @Delete("delete from article_category where id=#{id}")
    int delete(Integer id);
    
    /**
     * +根据文章分类id集合，删除文章分类
     * 
     * @param list 文章分类id集合
     * @return 影响条数
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @DeleteProvider(type = ArticleCategorySqlProvider.class, method = "batchDelete")
    int batchDelete(@Param("list") List<Integer> list);
    
    /**
     * +批量删除记录
     *
     * @param list 待删除id集合
     * @return returnResult
     */
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.TRUE)
    @Delete("<script>" +
            "delete from `article_category` where `id` in " +
            "<foreach collection=\"list\" index=\"i\" open=\"(\" separator=\",\" close=\")\" item=\"item\"  >" +
            "#{item}" +
            "</foreach>" +
            "</script>")
    int batchDeleteWithScript(@Param("list") List<Integer> list);

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

   /**
    * +根据id集合，获取文章分类列表 SQL
    *
    * @param map map
    * @return returnResult
    */
   public String batchDelete(final Map<String, List<Integer>> map) {
       List<Integer> 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(";");

       return sql.toString();
   }

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

    @Test
    public void testDelete() {
        int result = this.articleCategoryMapper.delete(this.articleCategory.getId());
        Assert.assertEquals(result, 1);
    }

    @Test
    public void testBatchDelete() {
        List<Integer> idList = new ArrayList<Integer>(Arrays.asList(1, 2));
        int result = this.articleCategoryMapper.batchDelete(idList);
        Assert.assertEquals(result, idList.size());
    }

    @Test
    public void testBatchDeleteWithScript() {
        List<Integer> idList = new ArrayList<Integer>(Arrays.asList(3, 4));
        int result = this.articleCategoryMapper.batchDeleteWithScript(idList);
        Assert.assertEquals(result, idList.size());
    }

 

参考《MyBatis与Druid数据库连接池集成监控统计后WallFilterSQL注入异常问题处理方案》

posted @ 2019-11-19 21:36  cctext  阅读(2799)  评论(0)  编辑  收藏  举报