MyBatis 使用总结

1. 基础

1.1 定义

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。

ORM（Object Relational Mapping） 框架

1.2 基础使用

1.2.1 引入 maven 依赖

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.6</version>
</dependency>

1.2.2 增加配置信息

<?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>
    <properties>
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url"
                  value="jdbc:mysql://127.0.01:3308/docloud?serverTimezone=Asia/Shanghai&amp;useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value="1213456"/>
    </properties>

    <typeAliases>
    </typeAliases>
    
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>
    
  <mappers>
    <mapper resource="org/mybatis/example/BlogMapper.xml"/>
  </mappers>
</configuration>

1.2.3 使用配置信息创建 SqlSessionFactory

String config = "mybatis-config.xml";
try {
    Reader reader = Resources.getResourceAsReader(config);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);

    SqlSession session = sqlSessionFactory.openSession();
}cache(Exception ex){

}

1.2.4 使用 SqlSession 获取 mapper

AreaMapper areaMapper = session.getMapper(AreaMapper.class);
List<Area> areaList = areaMapper.list();
Integer count = areaMapper.recordCount();
System.out.println(count);

2. XML 配置文件

https://mybatis.org/mybatis-3/zh/configuration.html#mappers

3. XML SQL 配置

3.1 特殊符号

第一种写法： 
原符号  替换符号 
<       &lt; 

<=    	&lt;=

>       &gt; 

>=      &gt;=

&       &amp;

'       &apos;

"       &quot;

第二种写法

大于等于    <![CDATA[ >= ]]>

小于等于    <![CDATA[ <= ]]>

3.2 动态SQL

mybatis 动态解析使用 ognl 表达式判断 http://commons.apache.org/proper/commons-ognl/language-guide.html

3.2.1 if 条件判断

语法:

<if test="boolean 判断">
</if>

字符串判断
<if test="name !=null and name!=''">
</if>

数字判断
<if test="age >0">
</if>


集合对象判断
<if test="list!=null and list.size >0">
</if>

可以执行集合中的方法
<if test="list != null and list.size() >0 and list.contains('31179d6a7eb3440ab095fe3cef571acd')">
    and id in
    <foreach collection="list" open="(" close=")" separator="," item="item">
        #{item}
    </foreach>
</if>

3.2.2 choose

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

从上往下进行条件判断，选择一个满足的条件执行

<select id="selectByChoose" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"></include>
    from auth_user
    <where>
        <choose>
            <when test="name != null and name !=''">
                and user_name =#{name}
            </when>
            <when test="mobile != null and mobile != ''">
                and mobile=#{mobile}
            </when>
            <otherwise>
                and email='dataojo0074@dataojo.com'
            </otherwise>
        </choose>
    </where>
</select>

3.2.3 trim where set

where 标签可以动态的清除 sql 查询条件中多余的 and or 内容，如果过滤内容为空，则不会添加 where 关键字
<where> </where>


set 标签可以动态的设置 update 语句中的 字段赋值
<set> </set>

trim 标签可以进行字段前缀或者后缀的过滤，进行动态清除
<trim></trim>

以下是trim标签中涉及到的属性：

属性	描述
prefix	给sql语句拼接的前缀
suffix	给sql语句拼接的后缀
prefixOverrides	去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"
suffixOverrides	去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定

单条记录数据插入

<insert id="insert" parameterType="com.xskj.manage.dataexchange.common.entity.DataDeleteLog">
    insert into data_delete_log
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">
            id,
        </if>
        <if test="catalog != null">
            catalog,
        </if>
        <if test="feature != null">
            feature,
        </if>
        <if test="userCode != null">
            user_code,
        </if>
        <if test="status != null">
            status,
        </if>
        <if test="createTime != null">
            create_time,
        </if>
        <if test="updateTime != null">
            update_time,
        </if>
        <if test="type != null">
            type,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="id != null">
            #{id,jdbcType=INTEGER},
        </if>
        <if test="catalog != null">
            #{catalog,jdbcType=VARCHAR},
        </if>
        <if test="feature != null">
            #{feature,jdbcType=VARCHAR},
        </if>
        <if test="userCode != null">
            #{userCode,jdbcType=VARCHAR},
        </if>
        <if test="status != null">
            #{status,jdbcType=INTEGER},
        </if>
        <if test="createTime != null">
            #{createTime,jdbcType=VARCHAR},
        </if>
        <if test="updateTime != null">
            #{updateTime,jdbcType=VARCHAR},
        </if>
        <if test="type != null">
            #{type,jdbcType=INTEGER},
        </if>
    </trim>
</insert>

3.2.4 foreach

循环简单类型

<select id="selectList" parameterType="java.util.List" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"></include>
    from auth_user
    <where>
        <if test="list != null and list.size() >0 and list.contains('31179d6a7eb3440ab095fe3cef571acd')">
            and id in
            <foreach collection="list" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>
    </where>
</select>

<select id="selectListWithArray" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"></include>
    from auth_user
    <where>
        and id in
        <foreach collection="array" index="key" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </where>
</select>

循环对象类型

<select id="selectWithListObject" parameterType="java.util.List" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"></include>
    from auth_user
    <where>
        <if test="list != null and list.size() >0">
            and id in
            <foreach collection="list" open="(" close=")" separator="," item="item">
                #{item.id}
            </foreach>
        </if>
    </where>
</select>

循环 map对象，需要指定 map 参数名称，否则，名称会被当作 map 中的 key

List<AuthUser> selectWithMap(@Param("params") Map<String,AuthUser> params);

<select id="selectWithMap" parameterType="java.util.Map" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"></include>
    from auth_user
    <where>
        and id in
        <foreach collection="params" index="key" item="item" open="(" close=")" separator=",">
            #{key}
        </foreach>
    </where>
</select>

注： foreach collection ：

​ 参数类型为 List，则该值为 list

​ 参数类型为数组，则该值为 array

​ 参数类型为 Map，需要指定参数名称，否则为 Map 的 Key

4. 常用插件

4.1 mybatis-generator 插件

4.1.1 generatorConfig.xml

在 resources 目录下增加 mybatis-generator 配置文件 generatorConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <!-- 配置文件路径 -->
    <!--    <properties resource="application.yaml"/>-->

    <context id="iokindle" targetRuntime="MyBatis3">
        <!-- 生成的Java文件的编码-->
        <property name="javaFileEncoding" value="UTF-8"/>
        <!-- 格式化java代码-->
        <property name="javaFormatter" value="org.mybatis.generator.api.dom.DefaultJavaFormatter"/>
        <!-- 格式化XML代码-->
        <property name="xmlFormatter" value="org.mybatis.generator.api.dom.DefaultXmlFormatter"/>

        <plugin type="org.mybatis.generator.plugins.EqualsHashCodePlugin"/>
        <plugin type="org.mybatis.generator.plugins.SerializablePlugin"/>
        <plugin type="org.mybatis.generator.plugins.CaseInsensitiveLikePlugin"/>

        <!-- <plugin type="org.mybatis.generator.plugins.ToStringPlugin"></plugin> -->
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>

        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://127.0.0.1:3308/docloud?useSSL=false"
                        userId="root"
                        password="123456">
        </jdbcConnection>

        <!--<jdbcConnection driverClass="${spring.datasource.write.driver-class-name}" connectionURL="${spring.datasource.write.url}"-->
        <!--userId="${spring.datasource.write.username}" password="${spring.datasource.write.password}">-->
        <!--</jdbcConnection>-->

        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>

        <javaModelGenerator targetPackage="com.ming.mybatis.demo.bean"
                            targetProject="src/main/java">
            <property name="constructorBased" value="true"/>
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>

       <sqlMapGenerator targetPackage="mybatis"
            targetProject="src/main/resources">
        <property name="enableSubPackages" value="true"/>
       </sqlMapGenerator>

        <javaClientGenerator type="XMLMAPPER"
                             targetPackage="com.ming.mybatis.demo.mapper"
                             targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>

        <table tableName="auth_user_role_extra" domainObjectName="AuthUserRoleExtra"
               enableCountByExample="false"
               enableUpdateByExample="false"
               enableDeleteByExample="false"
               enableSelectByExample="false"
               selectByExampleQueryId="false">
        </table>
        

    </context>
</generatorConfiguration>

4.1.2 maven 插件

<plugin>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-maven-plugin</artifactId>
    <version>1.3.7</version>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>
    </dependencies>
</plugin>

4.2 PageHelper 分页插件

4.2.1 增加 pom 依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.11</version>
</dependency>

4.2.2 配置 mybatis 拦截器

<plugins>
    <!-- com.github.pagehelper为PageHelper类所在包名 -->
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
    </plugin>
</plugins>

4.2.3 查询示例

public void testPageHelper() {
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
        AuthUserMapper mapper = sqlSession.getMapper(AuthUserMapper.class);
        PageHelper.startPage(1, 10);
        List<AuthUser> authUsers = mapper.selectAllUser();
        PageInfo pageInfo = new PageInfo(authUsers);

        //System.out.println(authUsers);
        System.out.println(pageInfo);

    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

5. 使用总结

5.1 mapper 多参数传递

常见的往 mapper 中传递多个参数，可以通过 Map 对象或者定义专门的查询对象来实现，也可以通过一个简单的注解方式 @Param 来实现。

List<AuthUser> selectByChoose(@Param("name") String name, @Param("mobile") String mobile);

mapper 对应的 xml 文件中，不需要写参数类型，会自动匹配。

<select id="selectByChoose" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"></include>
    from auth_user
    <where>
        <choose>
            <when test="name != null and name !=''">
                and user_name =#{name}
            </when>
            <when test="mobile != null and mobile != ''">
                and mobile=#{mobile}
            </when>
            <otherwise>
                and email='dataojo0074@dataojo.com'
            </otherwise>
        </choose>
    </where>
</select>

使用 arg0, arg1 也可以达到同样的效果

List<AuthUser> selectByMultiParam(String name, String mobile);

<select id="selectByMultiParam" resultType="com.ming.mybatis.demo.bean.AuthUser">
    select
    <include refid="Base_Column_List"></include>
    from auth_user
    <where>
        <choose>
            <when test=" arg0 != null and arg0 !='' ">
                and user_name=#{arg0}
            </when>
            <when test="arg1 != null and arg1 != '' ">
                and mobile=#{arg1}
            </when>
            <otherwise>
            </otherwise>
        </choose>
    </where>
</select>

或者使用 param1, param2 也可以传递多个参数

List<AuthUser> selectByMultiParam2(String name, String mobile);

<select id="selectByMultiParam2" resultType="com.ming.mybatis.demo.bean.AuthUser">
    select
    <include refid="Base_Column_List"></include>
    from auth_user
    <where>
        <choose>
            <when test=" param1 != null and param1 !='' ">
                and user_name=#{param1}
            </when>
            <when test=" param2 != null and param2 != '' ">
                and mobile=#{param2}
            </when>
            <otherwise>
            </otherwise>
        </choose>
    </where>
</select>

5.2 模糊查询

在 mybatis xml 使用模糊查询，可以使用 CONCAT 函数，将 % 与输入数据关联起来，或者在 java 代码中拼接好再传递到 mapper 中

SELECT  
*  
FROM  
user  
WHERE  
name like CONCAT('%',#{name},'%')  

6. 常见错误

6.1 Invalid bound statement

当使用 package 或者 class 配置 mapper 时，会出现以下异常问题，原因是编译后的目录中没有找到 xml 文件。

解决办法：

IDEA maven项目默认不会把src下除java文件外的文件打包到classes文件夹下，需要在pom.xml中增加配置

<resources>
    <resource>
        <directory>src/main/java</directory>
        <includes>
            <include>**/*.xml</include>
        </includes>
    </resource>
</resources>

7. SpringBoot 集成 MyBatis

增加 maven 依赖

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.4</version>
</dependency>

分页配置

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.3.0</version>
</dependency>