动态SQL

动态SQL

动态SQL就是根据不同的SQL生成不同的SQL语句

if

<select id="sellctStudentss" parameterType="Student" resultType="Student">
    select * from student
    <where>
        <if test="id!=null and id>0">
            and id=#{id}
        </if>
        <if test="name!=null">
            and name like concat('%',#{name},'%')
        </if>
        <if test="sex!=null">
            and sex=#{sex}
        </if>
        <if test="address!=null">
            and address like concat('%',#{address},'%')
        </if>
    </where>
</select>

choose、when、otherwise

    <select id="sellctStudentsss" parameterType="Student" resultType="Student">
        select * from student
        <where>
        <choose>
            <when test="id!=null and id>0">
                and id=#{id}
            </when>
            <when test="name!=null">
                and name like concat('%',#{name},'%')
            </when>
            <otherwise>
                and id=0
            </otherwise>
        </choose>
        </where>
    </select>

trim、where、set

where

主要是用来解决出现多余或缺少的“and”或者“or

<select id="seleWhene" resultType="Student" parameterType="Student">
    select * from student
    <where>
        <if test="id!=null and id>0">
            and id=#{id}
        </if>
        <if test="name!=null">
            and name like concat('%',#{name},'%')
        </if>
    </where>
</select>

set

主要用来解决删掉额外的逗号

    <update id="updateSett" parameterType="Student">
        update student
        <set>
            <if test="name!=null">
                name=#{name},
            </if>
            <if test="classer!=null">
                <if test="classer.id!=null">
                    classesid=#{classer.id},
                </if>
            </if>
        </set>
        <where>
            <if test="id!=null">
                id=#{id}
            </if>
        </where>

    </update>

trim

trim标签是综合性标签

在sqlNode接口下，有一个实体类是TrimSqlNode,有两个子类，一个是SetSqlNode，一个是WhereSqlNode

在类面有这些属性

​ prefix前缀.在标签包含的实际内容前添加（可以设置WHETE和SET）
​ suffix后缀.在标签包含的实际内容后添加
​ prefixOverrides前缀覆盖.在标签包含的实际内容前覆盖（一般用于and和or）
​ suffixOverrides后缀覆盖.在标签包含的实际内容后覆盖(一般用于 , )

trim 可以代替where标签和set标签

<select id="seleWhene" resultType="Student" parameterType="Student">
    select * from student
    <trim prefix="WHERE" prefixOverrides="and||or">
        <if test="id!=null and id>0">
            and id=#{id}
        </if>
        <if test="name!=null">
            and name like concat('%',#{name},'%')
        </if>
    </trim>
</select>

<update id="updateSett" parameterType="Student">
        update student
        <trim prefix="SET" suffixOverrides=",">
            <if test="name!=null">
                name=#{name},
            </if>
            <if test="classer!=null">
                <if test="classer.id!=null">
                    classesid=#{classer.id},
                </if>
            </if>

        </trim>

        <where>
            <if test="id!=null">
                id=#{id}
            </if>
        </where>

foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）

in查询

<select id="selectUForer" resultType="Student" parameterType="list">
    select * from student
    <where>
        id in
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </where>
</select>

接口：

List<Student> selectUForer(@Param("list")List<Integer> list);

测试：

SqlSession sqlSession=BaseDao.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student=new Student();
        List<Integer> list=new ArrayList<>();
        list.add(1);
        list.add(2);
        list.add(3);
        List<Student> students = mapper.selectUForer(list);
        sqlSession.close();

批量增加

<insert id="insertByFor" parameterType="list">
        insert into student(`name`,classesid) values
        <foreach collection="list" item="item"  separator=",">
            (#{item.name},#{item.classer.id})
        </foreach>
    </insert>

接口：

int insertByFor(@Param("list") List<Student> students);

测试：

SqlSession sqlSession=BaseDao.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students=new ArrayList<>();
students.add(new Student(10000,"1",new Classer(1)));
students.add(new Student(20000,"2",new Classer(1)));
students.add(new Student(30000,"3",new Classer(1)));
students.add(new Student(40000,"4",new Classer(1)));
mapper.insertByFor(students);

经典案例

1.根据用户输入的值来判断是否增加那一条sql语句

xml

<select id="sellctStudentss" parameterType="Student" resultType="Student">
    select * from student
    <where>
        <if test="id!=null and id>0">
            and id=#{id}
        </if>
        <if test="name!=null">
            and name like concat('%',#{name},'%')
        </if>
        <if test="sex!=null">
            and sex=#{sex}
        </if>
        <if test="address!=null">
            <!--concat连接字符串-->
            and address like concat('%',#{address},'%')
        </if>
    </where>
</select>

接口：

List<Student> sellctStudentss(Student student);

测试：

SqlSession sqlSession=BaseDao.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    Student student=new Student();
    Scanner scanner=new Scanner(System.in);
    System.out.println("请输入用户名");
    String name=scanner.next();
    System.out.println("请输入性别");
    String sex=scanner.next();
    student.setName(name);
    student.setSex(sex);
    List<Student> students = mapper.sellctStudentss(student);
    System.out.println(students);
sqlSession.close();

2.判断在一个日期内的区间

xml

<select id="selectByTime" resultType="Student" parameterType="student">
    select * from student
    <where>
        <if test="beginTime!=null">
            and time>=#{beginTime}
        </if>
        <if test="eninTime!=null">
            and time &lt;=#{eninTime}
        </if>
    </where>
</select>

接口：

List<Student> selectByTime(Student student);

测试：

SqlSession sqlSession=BaseDao.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    Student student=new Student();
    Scanner scanner=new Scanner(System.in);
    System.out.println("请输入入学时间：");
    String ru=scanner.next();
    System.out.println("请输入离校时间：");
    String li=scanner.next();

    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    Date rux=null;
    Date lix=null;
    try {
        rux = simpleDateFormat.parse(ru);
        lix = simpleDateFormat.parse(li);
    } catch (ParseException e) {
        rux=null;
        lix = null;
    }
    student.setBeginTime(rux);
    student.setEninTime(lix);
    List<Student> students = mapper.selectByTime(student);
    System.out.println(students);