Mybatis动态SQL

‘https://my.oschina.net/malajava/blog/1648191

动态SQL

多数据库支持

• 在 MyBatis 配置文件中提供数据库别名

        <databaseIdProvider type="DB_VENDOR">
            <property name="MySQL" value="mysql" />
            <property name="Oracle" value="oracle" />
            <property name="SQL Server" value="sqlserver" />
        </databaseIdProvider>

• 在执行SQL时指定 databaseId

        <selectKey order="BEFORE" resultType="java.lang.Integer" keyProperty="id" databaseId="mysql">
          SELECT IFNULL( max(id) , 0 ) + 1 FROM t_pandas
        </selectKey>
        <selectKey order="BEFORE" resultType="java.lang.Integer" keyProperty="id" databaseId="oracle">
            SELECT NVL( max(id) , 0 ) + 1 FROM t_pandas
        </selectKey>

if

• insert

        INSERT INTO t_pandas (
            <if test="name != null and name !=''"> name , </if>
            <if test="gender != null and gender !=''"> gender , </if>
            <if test="birthdate != null"> birthdate , </if>
            id
         )  VALUES  (
                <if test="name != null and name !=''"> #{name} , </if>
                <if test="gender != null and gender !=''"> #{gender} , </if>
                <if test="birthdate != null"> #{birthdate} ,  </if>
               #{id}
         )

set

    <!-- int update( Panda p ) ; -->
    <update id="update" parameterType="Panda" >
        UPDATE t_pandas
        <set>
            <if test="name != null and name !=''"> name =  #{name}, </if>
            <if test="gender != null and gender !=''"> gender = #{gender} , </if>
            <if test="birthdate != null"> birthdate = #{birthdate} , </if>
        </set>
        WHERE id = #{id}
    </update>

使用 set 标签可以产生 UPDATE 语句中的 SET 字句。

同时，对于 SET 字句中最后一个字符如果是 逗号，MyBatis 会将这个 逗号 剔除。

choose

    <insert id="persist" parameterType="Human" useGeneratedKeys="true" keyProperty="id" statementType="PREPARED">
      INSERT INTO t_humans
        (
            <if test="gender != null and gender !=''"> gender , </if>
            <if test="birthdate != null"> birthdate , </if>
            <if test="married != null"> married , </if>
            name
        )
      VALUES
      (
            <if test="gender != null and gender !=''"> #{gender} , </if>
            <if test="birthdate != null"> #{birthdate} , </if>
            <if test="married != null">
                <choose>
                      <when test="married == true">'Y' , </when>
                      <otherwise>'N' , </otherwise>
                </choose>
            </if>
            #{name}
        )
    </insert>

where 、 trim

    <select id="query" resultMap="humanResultMap">
        SELECT id , name , gender , birthdate , married FROM t_humans
        <where>
              <if test="gender != null and gender !=''">
                  AND gender = #{gender}
              </if>
                <if test="married != null">
                    AND married =
                    <choose>
                        <when test="married == true">'Y'</when>
                        <otherwise>'N'</otherwise>
                    </choose>
                </if>
                <trim prefixOverrides="AND |OR "></trim>
                   <!--用于去掉第一个满足条件前的AND或OR-->
        </where>
    </select>

sql

    <!-- 声明SQL片段 ( fragment ) -->
    <sql id="baseQuery">
        SELECT id , name , gender , birthdate , married FROM t_humans
    </sql>

bind

    <!-- List<Human> findByName( String nameLike ) ; -->
    <select id="findByName" parameterType="java.lang.String" resultMap="humanResultMap">
          <!-- 引入 SQL 片段 -->
          <include refid="baseQuery" />
          <!-- 绑定变量，为 name 变量指定值 ( 使用 OGNL 表达式拼接字符串 ) -->
        <bind name="name" value="'%' + nameLike + '%'" />
          WHERE name LIKE #{name}
    </select>

foreach

• MySQL 批量插入

    <insert id="persistBatch" parameterType="java.util.List" databaseId="mysql">
        INSERT INTO t_humans
          ( name , gender , birthdate , married )
        VALUES
        <foreach collection="list" item="h" open="" close="" separator=",">
            ( #{h.name} , #{h.gender} , #{h.birthdate} , #{h.married,typeHandler=org.malajava.dynamic.type.BooleanTypeHandler} )
        </foreach>
    </insert>

• 批量删除

    <delete id="removeBatch" parameterType="java.util.List" >
        DELETE FROM t_humans WHERE
        <choose>
            <when test="list != null and list.size() > 0">
                 id IN
                <foreach collection="list" open="(" close=")" item="id" separator=",">
                    #{id}
                </foreach>
            </when>
            <otherwise> 1 = 2 </otherwise>
        </choose>
    </delete>

注意：

• if标签为判断条件，if标签内可以有choose标签
• choose标签用于选择，其内又有when和otherwise子标签，注意书写顺序
• where标签用于多条件查询
• foreach标签用于迭代数组或集合，常用于批量插入和删除