一、動態(tài) SQL 之foreach

循環(huán)執(zhí)?sql的拼接操作，例如：SELECT * FROM USER WHERE id IN (1,2,5)

在IUserProxyDao接口中添加一個(gè)findUserByIds方法

 /**
     * 用數(shù)組進(jìn)行查詢，用來演示foreach標(biāo)簽
     * @param ids
     * @return
     */
    List<User> findUserByIds(int[] ids);

在UserProxyMapper.xml文件中添加以下代碼片斷

<select id="findUserByIds" parameterType="list" resultType="user">
        SELECT * FROM user
        <where>
            <foreach collection="array" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

編寫測試方法，在MyBatisProxyTest測試類中添加如下代碼片斷進(jìn)行單元測試

@Test
    public void testFindByIds() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        IUserProxyDao mapper = sqlSession.getMapper(IUserProxyDao.class);
        int[] ids = new int[]{1,2,6};
        List<User> all = mapper.findUserByIds(ids);
        for (User user : all) {
            System.out.println(user);
        }
    }

foreach標(biāo)簽的屬性含義如下：
標(biāo)簽?于遍歷集合，它的屬性：
? collection：代表要遍歷的集合元素，注意編寫時(shí)不要寫#{}
? open：代表語句的開始部分
? close：代表結(jié)束部分
? item：代表遍歷集合的每個(gè)元素，?成的變量名
? sperator：代表分隔符

二、SQL?段抽取

Sql 中可將重復(fù)的 sql 提取出來，使?時(shí)? include 引?即可，最終達(dá)到 sql 重?的?的

<?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="study.lagou.com.dao.IUserProxyDao">
    <sql id="selectUser">
        SELECT * FROM user
    </sql>
    
    <!--查詢方法-->
    <select id="findAll" resultType="study.lagou.com.pojo.User">
        <include refid="selectUser"/>
    </select>

    <select id="findByCondition" resultType="user" parameterType="user">
        <include refid="selectUser"/>
        <where>
            <if test="id != null">
                AND id = #{id}
            </if>
            <if test="username != null and username != ''">
                AND username = #{username}
            </if>
        </where>
    </select>

    <select id="findUserByIds" parameterType="list" resultType="user">
        <include refid="selectUser"/>
        <where>
            <foreach collection="array" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>

上一篇筆記地址:http://www.itdecent.cn/p/26a4cbba4a98

下一篇筆記地址:http://www.itdecent.cn/p/200d11349f8d
具體代碼對應(yīng)下載地址:https://gitee.com/happymima/mybatis.git