MyBatis动态SQL

2018-09-01 本文已影响100人 tuacy

在讲MyBatis动态SQL之前，先教大家怎么通过MyBatis插件自动生成mapper对应的文件和对应的bean文件。使用起来非常简单。

第一步，在pom.xml文件里面添加plugin。这里要注意configurationFile部分这个字段指明generatorConfig.xml文件的位置。generatorConfig.xml在第二步会教大家怎么配置。这里我们指定的目录是resources/generator文件夹下面，大家根据自己的实际情况来指定。

            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.2</version>
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>5.1.35</version>
                    </dependency>
                </dependencies>
                <configuration>
                    <!--配置文件的路径-->
                    <configurationFile>${basedir}/src/main/resources/generatorConfig.xml</configurationFile>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>

第二步，generatorConfig.xml文件的配置，主要改的地方就是jdbcConnection字段里面数据库相关的信息。以及javaModelGenerator，sqlMapGenerator，javaClientGenerator字段目标文件的位置。和table字段里面改成自己的表格。关于这些东西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>
    <!--执行命令 mvn mybatis-generator:generate-->
    <context id="test" targetRuntime="MyBatis3">
        <plugin type="org.mybatis.generator.plugins.EqualsHashCodePlugin"></plugin>
        <plugin type="org.mybatis.generator.plugins.SerializablePlugin"></plugin>
        <plugin type="org.mybatis.generator.plugins.ToStringPlugin"></plugin>
        <commentGenerator>
            <!-- 这个元素用来去除指定生成的注释中是否包含生成的日期 false:表示保护 -->
            <!-- 如果生成日期，会造成即使修改一个字段，整个实体类所有属性都会发生变化，不利于版本控制，所以设置为true -->
            <property name="suppressDate" value="true"/>
            <!-- 是否去除自动生成的注释 true：是 ： false:否 -->
            <property name="suppressAllComments" value="false"/>
        </commentGenerator>
        <!--数据库链接URL，用户名、密码 -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://192.168.13.98/dcim" userId="root" password="123456">
        </jdbcConnection>
        <javaTypeResolver>
            <!-- This property is used to specify whether MyBatis Generator should
                force the use of java.math.BigDecimal for DECIMAL and NUMERIC fields, -->
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>
        <!-- 生成模型的包名和位置 -->
        <javaModelGenerator targetPackage="com.pilot.dcim.alarmmanage.entity.model"
                            targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!-- 生成映射文件的包名和位置 -->
        <sqlMapGenerator targetPackage="com.pilot.dcim.alarmmanage.mapper"
                         targetProject="src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!-- 生成DAO的包名和位置 -->
        <javaClientGenerator type="XMLMAPPER"
                             targetPackage="com.pilot.dcim.alarmmanage.mapper"
                             implementationPackage="com.pilot.dcim.alarmmanage.service.impl"
                             targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>

        <!-- 要生成哪些表 -->
        <table tableName="alarmgroup" domainObjectName="AlarmGroup"
               enableCountByExample="false" enableUpdateByExample="false"
               enableDeleteByExample="false" enableSelectByExample="false"
               selectByExampleQueryId="false"></table>
        <table tableName="alarmparam" domainObjectName="AlarmParam"
               enableCountByExample="false" enableUpdateByExample="false"
               enableDeleteByExample="false" enableSelectByExample="false"
               selectByExampleQueryId="false"></table>
        <table tableName="alarmsetting" domainObjectName="AlarmSetting"
               enableCountByExample="false" enableUpdateByExample="false"
               enableDeleteByExample="false" enableSelectByExample="false"
               selectByExampleQueryId="false"></table>
        <table tableName="faultevent" domainObjectName="FaultEvent"
               enableCountByExample="false" enableUpdateByExample="false"
               enableDeleteByExample="false" enableSelectByExample="false"
               selectByExampleQueryId="false"></table>
    </context>
</generatorConfiguration>

第三步，上面两部都配置好之后，执行命令 mvn mybatis-generator:generate 就会在对应的目录下生成我们想要的文件。

MyBatis的动态SQL是基于OGNL的表达式的。它对SQL语句进行灵活的操作。通过表达式判断来实现对SQL的灵活拼接、组装。我们着重讲说MyBatis动态SQL里面一些元素的使用。

一、MyBatis动态SQL标签的学习

1.1、if 条件判断标签

if标签通用用于通过判断参数值来决定是否使用某个查询条件。

1.1.1、if 判断数字

if 判断数字，大部分都是判断等于或者不等于某个数字。

<if test="filter == 1">
    ...
</if>

1.1.2、if 判断字符串

大部分情况下都是去判断传递过来的字符串是否为空。当然咱们也是可以添加别的条件限制的，比如以啥字符串开头，以啥字符串结尾。

1.1.2.1、判断字符串是否为空,或者字符串等于某个值

    <select id="selectUser" resultType="com.tuacy.mybatisdemo.model.User">
        select * from user
        <where>
            <if test="user.name != null and user.name == '0'.toString()">
                and name = #{user.name}
            </if>

        </where>

    </select>

1.1.2.2、判断字符串是否包含

    <select id="selectUser" resultType="com.tuacy.mybatisdemo.model.User">
        select * from user
        <where>
            <if test=" user.name != null and user.name.contains('1'.toString())">
                and name = #{user.name}
            </if>

        </where>

    </select>

1.1.2.3、判断是否以字符串开头

    <select id="selectUser" resultType="com.tuacy.mybatisdemo.model.User">
        select * from user
        <where>
            <if test=" user.name != null and user.name.startsWith('1'.toString())">
                and name = #{user.name}
            </if>

        </where>

    </select>

1.1.3、判断列表

经常判断列表是否为null，或者列表是否为空。列表判断也是很简单的直接 <if test=" nameList != null and nameList.size() > 0 "> 就搞定了。

一个简单的实例，通过用户名(list)查询用户

    List<User> selectUserByName(@Param("nameList") List<String> nameList);

    <!-- 通过用户名查找用户 -->
    <select id="selectUserByName" resultType="com.tuacy.mybatisdemo.model.User">
        select * from user
        <where>
            <if test=" nameList != null and nameList.size() > 0 ">
                <trim prefix=" name in ">
                    <foreach collection="nameList" item="i" index="index" open="(" separator="," close=")">
                        <if test="i != null">
                            #{i}
                        </if>
                    </foreach>
                </trim>
            </if>

        </where>

    </select>

1.2、choose，when，otherwise标签(switch case)

choose，when，otherwise标签的作用类似与咱们java里面的switch case的作用。

<choose>
    <when test="item.name != null">
        #{item.name,jdbcType=VARCHAR},
    </when>
    <otherwise>
        null,
    </otherwise>
</choose>

1.3、foreach循环标签

foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。大部分都是对一个list进行循环。

foreach元素的属性主要有 item，index，collection，open，separator，close。

foreach元素	解释
collection	迭代的对象
item	迭代过程中的每个元素
index	迭代过程中的位置
open	语句以什么开始
separator	语句以什么作为分隔符
close	语句以什么结束

在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：

1. 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list。
1. 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array。
1. 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以。

一个简单的实例

<select id="dynamicForeachTest" parameterType="java.util.List" resultType="Blog">
          select * from t_blog where id in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}       
        </foreach>    
    </select>

array

<select id="dynamicForeach2Test" parameterType="java.util.ArrayList" resultType="Blog">
     select * from t_blog where id in
     <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
          #{item}
     </foreach>
 </select>

map

<select id="dynamicForeach3Test" parameterType="java.util.HashMap" resultType="Blog">
         select * from t_blog where title like "%"#{title}"%" and id in
          <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
               #{item}
          </foreach>
 </select>

上面说的都是没有指定@Param的情况，如果指定了@Param则collection的属性值是@Param指定的名字。

1.4、trim标签

trim标记是一个格式化的标记，可以完成set或者是where标记的功能。

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

<trim prefix="WHERE" prefixOverrides="AND">
    <if test="state != null">
      state = #{state}
    </if> 
    <if test="title != null">
      AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
      AND author_name like #{author.name}
    </if>
</trim>

1.5、where标签

where标签是非常有用的，他有两个作用：

当where标签里面元素的元素都不满足条件的时候，不会插入where语句。
where标签里面若语句的开头为“AND”或“OR”，where 元素也会将它们去除。

有这么一个例子，我呢有一个user表(pkid, name, phone, password)，现在根据我们传入的条件来查询user信息。

    <!-- 查找指定的用户 -->
    <select id="selectUser" resultType="com.tuacy.mybatisdemo.model.User">
        select * from user
        <where>
            <if test="user.pkid != null">
                pkid = #{user.pkid}
            </if>

            <if test="user.name != null">
                and name = #{user.name}
            </if>

            <if test="user.phone != null">
                and phone = #{user.phone}
            </if>

            <if test="user.password != null">
                and password = #{user.password}
            </if>

        </where>

    </select>

1.6、set标签

set标签和where标签一样。会自动加上set关键字，并且去掉不必要的一些字符。

<set>   
    <if test="userId !=null and userId !=''">    
        AND A.userId = #{userId,jdbcType=CHAR}    
    </if>    
    <if test="userName !=null and userName !=''">    
        AND A.userName = #{userName,jdbcType=CHAR}    
    </if>   
</set>

1.7、bind标签

bind 标签可以使用 OGNL 表达式创建一个变量井将其绑定到上下文中。咱就可以简单的认为是声明了一个变量。

这个例子里面，我纯粹的是把user.name替换成了user.name了。

    <select id="selectUser" resultType="com.tuacy.mybatisdemo.model.User">
        select * from user
        <where>
            <bind name="userName" value="user.name"/>
            <if test=" userName != null ">
                and name = #{userName}
            </if>

        </where>

    </select>

这例子里面咱用bind标签声明了一个模糊查询的变量

    <select id="selectUser" resultType="com.tuacy.mybatisdemo.model.User">
        select * from user
        <where>
            <bind name="patternName" value="'%' + user.name + '%'" />
            <if test=" user.name != null ">
                and name like #{patternName}
            </if>

        </where>

    </select>

二、MyBatis动态SQL实例

最后我们根据今天所学到的内容，给出两个比较复杂的MyBats动态SQL。

2.1、批量插入并且更新主键

    <!-- 批量插入并且更新主键 -->
    <insert id="insertUserBatch" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="pkid">
        insert into user (name, password, phone)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <choose>
                    <when test="item.name != null">
                        #{item.name,jdbcType=VARCHAR},
                    </when>
                    <otherwise>
                        null,
                    </otherwise>
                </choose>
                <choose>
                    <when test="item.password != null">
                        #{item.password,jdbcType=VARCHAR},
                    </when>
                    <otherwise>
                        null,
                    </otherwise>
                </choose>
                <choose>
                    <when test="item.phone != null">
                        #{item.phone,jdbcType=VARCHAR},
                    </when>
                    <otherwise>
                        null,
                    </otherwise>
                </choose>
            </trim>
        </foreach>
    </insert>

2.2、批量更新

   <!-- 批量更新 -->
    <update id="updateUserBatch" parameterType="java.util.List">
        update user
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="name =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.name!=null">
                        when pkid=#{i.pkid} then #{i.name}
                    </if>
                </foreach>
            </trim>
            <trim prefix=" password =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.password!=null">
                        when pkid=#{i.pkid} then #{i.password}
                    </if>
                </foreach>
            </trim>

            <trim prefix="phone =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.phone!=null">
                        when pkid=#{i.pkid} then #{i.phone}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" separator="or" item="i" index="index">
            pkid=#{i.pkid}
        </foreach>
    </update>

上面涉及到的所有实例的下载地址：https://github.com/tuacy/mybatisdemo