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

2023-03-10 13:59:25

最近工作中用到了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>

在/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>

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>

在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

                + "]";

    }

}

在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注入异常问题处理方案》

码农公寓

1）环境搭建（maven+mybatis+spring整合）

2）查询、批量查询

3）新增、批量新增

4）修改、批量修改

问题解决：

5）删除、批量删除

相关文章