MyBatis之五：动态sql语句

　　在mybatis 3 或以上的版本提供了4类标签，分别是：if，choose(when,otherwise)，rim(where,set)，foreach。接下来将分别介绍这几种标签的具体用法，映射xml文件以及调用示例如下：

　　一、映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.dynamicsql.dynamicMapper">

    <!-- 1、if操作含模糊查询 -->
    <select id="getIF" parameterType="com.mybatis.dynamicsql.IFArgs"
        resultType="com.mybatis.bean.YProducts">
        select * from Y_Products where CategoryID = 3

        <!-- 需要注意的是test="productName != null" 中的productName 是parameterType对应对象的
            的属性 -->
        <!-- 注意模糊查询的写法 '%${字段名}%' -->
        <if test="productName != null">
            and ProductName like '%${productName}%'
        </if>
        <if test="minNum != null and maxNum != null">
            and StockNum between #{minNum} and #{maxNum}
        </if>
    </select>

    <!-- 2、choose操作 -->
    <!-- 类似于Java 的switch语句，choose为switch，when为case，otherwise则为default。 -->
    <select id="getCHOOOSE" parameterType="com.mybatis.dynamicsql.CHOOSEArgs"
        resultType="com.mybatis.bean.YProducts">
        select * from Y_Products
        <where>
            <choose>
                <when test="productName != null ">
                    and ProductName like '%${productName}%'
                </when>
                <when test="stockNum != null and stockNum >0">
                    and StockNum > #{stockNum}
                </when>
                <when test="unitPrice != null and unitPrice>0">
                    <!-- 避免转义 -->
                <![CDATA[    and UnitPrice < #{unitPrice} ]]>
                </when>
                <otherwise> CategoryID > #{categoryid} </otherwise>
            </choose>
        </where>
    </select>

    <!-- 3、where操作 -->
    <select id="getWHERE" parameterType="com.mybatis.dynamicsql.IFArgs"
        resultType="com.mybatis.bean.YProducts">
        select * from Y_Products

        <where>
            <if test="productName != null">
                ProductName like '%${productName}%'
            </if>
            <if test="minNum != and minNum >0 and maxNum != null and maxNum>0">
                StockNum between #{minNum} and #{maxNum}
            </if>
        </where>
    </select>

    <!-- 4、set操作 -->
    <update id="updateSET" parameterType="com.mybatis.dynamicsql.SETArgs">
        update Y_Products
        <set>
            <!-- 注意行尾的逗号,会自动去掉末尾最后一个逗号 -->
            <if test="pname !=null and pname != '' ">
                ProductName = #{pname},
            </if>
            <if test="pdesc !=null and pdesc != '' ">
                ProductDesc = #{pdesc},
            </if>
            <if test="stocknum >0">
                StockNum = #{stocknum},
            </if>
        </set>
        where ProductID = #{id}
    </update>

    <!-- 5、trim操作 -->
    <update id="getTRIM" parameterType="com.mybatis.dynamicsql.SETArgs">
        update Y_Products
        <!-- 语句前面加set，结尾去掉逗号 -->
        <trim prefix="set" suffixOverrides=",">
            <!-- 注意行尾的逗号 -->
            <if test="pname !=null and pname != '' ">
                ProductName = #{pname},
            </if>
            <if test="pdesc !=null and pdesc != '' ">
                ProductDesc = #{pdesc},
            </if>
            <if test="stocknum >0">
                StockNum = #{stocknum},
            </if>
        </trim>
        where ProductID = #{id}
    </update>

    <!-- 主是要迭代一个集合，通常是用于IN 条件。List实例将使用“list”做为键，数组实例以“array”做为键 -->
    <!-- 6、foreach操作1 -->
    <select id="getFOREACH1" resultType="com.mybatis.bean.YProducts">
        select * from Y_Products
        where ProductID in
        <!-- 参数类型为整型类型 -->
        <foreach collection="list" item="listIds" open="(" separator=","
            close=")">
            #{listIds}
        </foreach>
    </select>

    <!-- 7、foreach操作2 -->
    <select id="getFOREACH2" resultType="com.mybatis.bean.YProducts">
        select * from Y_Products
        where ProductName in
        <!-- 参数类型为字符类型 ,不需要再加单引号，内部会自动识别匹配 -->
        <foreach collection="list" item="xxsList" open="(" separator=","
            close=")">
            #{xxsList}
        </foreach>
    </select>


</mapper>

　　二、调用示例

package com.mybatis.dynamicsql;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.ListIterator;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;

import com.mybatis.bean.YProducts;
import com.mybatis.util.MybatisUtils;

public class Main {
    
//    http://my.oschina.net/ydsakyclguozi/blog/270322?fromerr=0ndPg1QQ
    
    /**
     * if标签
     */
    @Test
    public void test_getIF() {
        SqlSessionFactory factory = MybatisUtils.getFactory();
        SqlSession session = null;
        try {
            session = factory.openSession(true);

            IFArgs ifArgs = new IFArgs();
            ifArgs.setProductName("o");
            ifArgs.setMinNum(40);
            ifArgs.setMaxNum(50);

            String statement = "com.mybatis.dynamicsql.dynamicMapper.getIF";
            List<YProducts> yProductsList = session.selectList(statement,
                    ifArgs);
            for (YProducts yProducts : yProductsList) {
                System.out.println(yProducts);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }

    }

    /**
     * choose标签 
     */
    @Test
    public void test_getCHOOSE() {
        SqlSessionFactory factory = MybatisUtils.getFactory();
        SqlSession session = null;
        try {
            session = factory.openSession(true);

            CHOOSEArgs chooseArgs = new CHOOSEArgs();
            // chooseArgs.setStockNum(10);
            // chooseArgs.setUnitPrice(100);
            // chooseArgs.setProductName("n");
//            chooseArgs.setCategoryid(1);

            String statement = "com.mybatis.dynamicsql.dynamicMapper.getCHOOOSE";
            List<YProducts> yProductsList = session.selectList(statement,
                    chooseArgs);
            for (YProducts yProducts : yProductsList) {
                System.out.println(yProducts);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     * where标签
     */
    @Test
    public void test_getWHERE() {
        SqlSessionFactory factory = MybatisUtils.getFactory();
        SqlSession session = null;
        try {
            session = factory.openSession(true);

            IFArgs ifArgs = new IFArgs();
            ifArgs.setProductName("o");
            // ifArgs.setMinNum(40);
            // ifArgs.setMaxNum(50);

            String statement = "com.mybatis.dynamicsql.dynamicMapper.getWHERE";
            List<YProducts> yProductsList = session.selectList(statement,
                    ifArgs);
            for (YProducts yProducts : yProductsList) {
                System.out.println(yProducts);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     * set标签
     */
    @Test
    public void test_updateSET() {
        SqlSessionFactory factory = MybatisUtils.getFactory();
        SqlSession session = null;
        try {
            session = factory.openSession(true);

            SETArgs setArgs = new SETArgs();
            setArgs.setId(1);

            setArgs.setPdesc("ddddddddddddddddddd");
            setArgs.setPname("hosyo");
//             setArgs.setStocknum(100);

            String statement = "com.mybatis.dynamicsql.dynamicMapper.updateSET";
            int rVal = session.update(statement, setArgs);
            System.out.println(rVal > 0 ? "success" : "fail");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     * trim标签
     */
    @Test
    public void test_getTRIM() {
        SqlSessionFactory factory = MybatisUtils.getFactory();
        SqlSession session = null;
        try {
            session = factory.openSession(true);

            SETArgs setArgs = new SETArgs();
            setArgs.setId(1);

            setArgs.setPdesc("xxxdddddxx");
            setArgs.setPname("hosyo2016");
//             setArgs.setStocknum(100);

            String statement = "com.mybatis.dynamicsql.dynamicMapper.getTRIM";
            int rVal = session.update(statement, setArgs);
            System.out.println(rVal > 0 ? "success" : "fail");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     * foreach标签
     */
    @Test
    public void test_getFOREACH() {
        SqlSessionFactory factory = MybatisUtils.getFactory();
        SqlSession session = null;
        try {
            session = factory.openSession(true);
                
            //1、整形类型参数
//            List<Integer> listIds = new ArrayList();
//            listIds.add(1);
//            listIds.add(3);
//            listIds.add(5);
            
            //2、字符串类型参数
            List<String> strList = new ArrayList<String>();
            strList.add("hosyo2016");
            strList.add("Chang");
            strList.add("Aniseed Syrup");
            
            String statement = "com.mybatis.dynamicsql.dynamicMapper.getFOREACH2";
            List<YProducts> yProductsList = session.selectList(statement, strList);
            for (YProducts yProducts : yProductsList) {
                System.out.println(yProducts);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
    }
}

　　三、需要注意的问题

　　　　1、模糊查询， '%${字段名}%' 

　　　　2、表达式类似test="productName != null " 这里面的"productName"就是parameterType所对应对象的属性

　　　　3、<![CDATA[ and UnitPrice < #{unitPrice} ]]> 特殊符号需要避免转义

　　　　4、<trim prefix="set" suffixOverrides=","> 前缀加set，去掉后缀,

　　　　5、<foreach collection=""></foreach> 中collection的值可以是list或者array，这个主要由调用方法传入参数决定　　

　　　　　　in 里面的不管只整型还是字符类型，都不需要单独加单引号，mybatis内部会自动匹配