MyBatis学习_动态sql_foreach遍历_sql片段

动态sql

在mapper.xml文件中添加标签

<!--动态sql-->
    <select id="dynamicQueryByIdAge" resultType="person" parameterType="person">
        select * from person
        <where>
            <if test="id != null and id !='' ">
                and id=#{id}
            </if>
            <if test="age != null and age !='' ">
                and age=#{age}
            </if>
        </where>
    </select>

以上标签等价于

select * from person where id=? and age=?

foreach遍历

如果参数是简单数组，在mapper.xml中传入参数必须用int[]，必须用array代替该数组
如果参数是集合，在mapper.xml中传入参数必须用list，必须用list代替该数组
如果参数是对象数组，在mapper.xml中传入参数必须用Object[]，还是用array代替数组
新建一个Age.java类

public class Age {
    private List<Integer> ages;
    public List<Integer> getAges() {
        return ages;
    }
    public void setAge(List<Integer> ages) {
        this.ages = ages;
    }
}

在mapper.xml文件中添加标签

<!--foreach遍历  参数是类-->
   <select id="foreachQueryByAge" resultType="Person" parameterType="Age">
       select * from person
       <where>
           <if test="ages != null and ages.size>0">
               <foreach collection="ages" open=" and age in (" close=")" item="ageItem" separator=",">
                   #{ageItem}
               </foreach>
           </if>
       </where>
   </select>
<!--foreach遍历 参数是数组-->
   <select id="foreachQueryByArrayAge" resultType="Person" parameterType="int[]">
       select * from person
       <where>
           <if test="array != null and array.length>0">
               <foreach collection="array" open=" and age in (" close=")" item="ageItem" separator=",">
                   #{ageItem}
               </foreach>
           </if>
       </where>
   </select>
<!--foreach遍历 参数是对象数组-->
    <select id="foreachQueryByObjectArrayAge" resultType="Person" parameterType="Object[]">
        select * from person
        <where>
            <if test="array != null and array.length>0">
                <foreach collection="array" open=" and age in (" close=")" item="ageItem" separator=",">
                    #{ageItem.age}
                </foreach>
            </if>
        </where>
    </select>
    

上面标签相当于：

select * from person where age in(?,?,?)

sql片段

就是让代码复用，java中通过方法，sql中通过存储过程/函数，MyBatis则通过sql片段
示例：

<!--复用前-->
<!--foreach遍历 参数是对象数组-->
<select id="foreachQueryByObjectArrayAge" resultType="Person" parameterType="Object[]">
    select * from person
    <where>
        <if test="array != null and array.length>0">
            <foreach collection="array" open=" and age in (" close=")" item="ageItem" separator=",">
                #{ageItem.age}
            </foreach>
        </if>
    </where>
</select>

<!--复用后-->
<sql id="sqlQueryObjectArrayAge">
     <where>
         <if test="array != null and array.length>0">
             <foreach collection="array" open=" and age in (" close=")" item="ageItem" separator=",">
                 #{ageItem.age}
             </foreach>
         </if>
     </where>
 </sql>
 <!--foreach遍历 参数是对象数组-->
 <select id="foreachQueryByObjectArrayAge" resultType="Person" parameterType="Object[]">
     select * from person
     <include refid="sqlQueryObjectArrayAge"></include>
 </select>

需要知道的是如果要引用的sql片段不在当前.xml文件中，需要给refid里写的是【sql片段所在xml的namespace.sqld】

愿你心如花木，向阳而生