【MyBatus学习笔记】动态SQL

动态SQL

<where>标签会自动处理第一个<if>标签中的and：

    <select id="queryStudentBySQLTag" parameterType="student" resultType="student">
        select * from student
        <where>
            <if test="sname != null and sname != ''">
                and sname = #{sname}
            </if>

            <if test="sage != null and sage != 0">
                and sage = #{sage}
            </if>
        </where>
    </select>

<foreach>标签迭代的类型：数组、对象数组、集合、属性(Grade类：List<Integer> ids)

xml:

<select id="queryStudentByGeadeWithSQLTag" resultType="student" parameterType="Grade">
    select  * from student
    <where>
        <if test="sid!=null and sid.size>0">
            <foreach collection="sid" open="and sid in (" close=")" separator="," item="id">
                #{id}
            </foreach>
        </if>
    </where>
</select>

test.java:

    Grade grade = new Grade();
    List<Integer> list = new ArrayList<>();
    list.add(5);
    list.add(4);
    list.add(7);
    grade.setSid(list);
    List<student> students = studentMapper.queryStudentByGeadeWithSQLTag(grade);

简单类型数组（在xml中参数名必须为array）：

xml：

<select id="queryStudentByArray" resultType="student" parameterType="int[]">
    select  * from student
    <where>
        <if test="array!=null and array.length>0">
            <foreach collection="array" open="and sid in (" close=")" separator="," item="id">
                #{id}
            </foreach>
        </if>
    </where>
</select>

test.java:

    int[] arr = {4, 5, 6, 7};
    List<student> students = studentMapper.queryStudentByArray(arr);

集合（在xml中参数名必须为list）因为和数组类似就不放代码了

对象数组（参数类型全部用Object[]）