动态SQL

mapper与接口分离

我们之前的写法是将mapper文件和接口代码放在java目录的同一个包下了,此时为了保证mapper文件能够被maven放到target下,在pom文件中添加了这个配置:

<build>
    <!--告诉maven将xml文件放到target目录下-->
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
        </resource>
    </resources>
</build>

其实在resources目录下创建与dao接口同名的文件夹,然后把mapper文件放到这个resources中新建的文件夹下即可。例如dao层的包名是:

com.monkey1024.dao

此时在resources下创建三级文件夹对应的是这三个包名即可

com/monkey1024/dao

这样只需要把mapper文件放到这个包下,maven在编译的时候会将mapper放到target对应的目录中了。这样修改完毕之后就不用在pom文件中添加那个build配置了。

动态SQL

在实际应用中,在执行查询操作的时候会有多个条件,这些条件是由用户指定的,比如查询的条件有:用户名、年龄、生日。此时我们需要在sql语句中将这三个查询条件加上。不过这三个查询条件用户在输入的时候,填写的个数不确定,可能只填写了一个,也可能三个都填写了,此时就可以使用动态SQL来解决这个问题了,动态SQL会根据传入的条件动态拼接sql语句。

if标签

这里的if标签主要是用来判断用户是否输入了某个条件,如果输入了再将该条件拼接到sql语句中,如下示例表示用户可以输入两个查询条件,name和age:

<select id="selectIf" resultType="student">
    SELECT id,name,age,score
    FROM t_student
    WHERE 1=1
    <if test="name != null and name != ''">
      AND name LIKE '%' #{name} '%'
    </if>
    <if test="age>=0">
      AND age > #{age}
    </if>
</select>

在上面的语句中,我们在where后面添加了一个1=1的条件,这样就不至于两个条件均未设定而出现只剩下一个where,这样sql语句就不正确了,所以在后面添加了1=1这个为true的条件。

在dao中添加方法:

List<Student> selectIf(Student student);

在测试类中进行测试:

@Test
public void selectIf(){
    Student student = new Student("富", 0, 0.0);
    List<Student> students = studentDao.selectIf(student);
    students.forEach((s)-> {
        System.out.println(s);
    });
}

可以调整Student中的name和age属性来看下控制台sql语句的打印。

where标签

在上面的if语句中,为了防止用户未设置条件而导致sql语句出现一个where,我们添加了1=1这个条件,但是这个条件没有什么意义,所以可以使用where标签来解决这个问题。
在mapper中添加下面sql:

<select id="selectWhere" resultType="student">
    SELECT id,name,age,score
    FROM t_student
    <where>
        <if test="name != null and name != ''">
            name LIKE '%' #{name} '%'
        </if>
        <if test="age>=0">
            AND age > #{age}
        </if>
    </where>
</select>

使用where标签后,就无需再写1=1了,注意在第一个if标签中的sql可以不加and,但是其后面的if标签中必须要加and。

在dao接口中添加方法:

List<Student> selectWhere(Student student);

在测试类中添加测试方法:

@Test
public void selectWhere(){
    Student student = new Student("富", 0, 0.0);
    List<Student> students = studentDao.selectWhere(student);
    students.forEach((s)-> {
        System.out.println(s);
    });
}

trim标签

上面使用了where标签来完成了查询操作,如果where的元素与我们期望的不一样时,可以使用trim标签来定制where元素的功能,例如下面内容跟上面的where是等价的,只不过是我们自己设定了元素

    <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="name != null and name != ''">
            name LIKE '%' #{name} '%'
        </if>
        <if test="age>=0">
            AND age > #{age}
        </if>
    </trim>

其中mybatis会根据实际元素是否满足条件来判断添加或删除AND,OR

choose标签

通过choose标签实现下面功能若姓名不空,则按照姓名查询;若姓名为空,则按照年龄查询;若没有查询条件,则没有查询结果。

在mapper中添加下面sql:

<select id="selectChoose" resultType="student">
    SELECT id,name,age,score
    FROM t_student
    <where>
        <choose>
          <when test="name != null and name != ''">
              name LIKE '%' #{name} '%'
          </when>
          <when test="age>=0">
              age > #{age}
          </when>
          <otherwise>
              1 != 1
          </otherwise>
        </choose>
    </where>
</select>

在choose标签中可以有多个when,但是只能有一个otherwise,这个有点类似java中的switch语句。

在dao接口中添加下面方法:

List<Student> selectChoose(Student student);

在测试类中添加测试方法:

@Test
public void selectChoose(){
    Student student = new Student(null, -1, 0.0);
    List<Student> students = studentDao.selectChoose(student);
    students.forEach((s)-> {
        System.out.println(s);
    });
}

set标签

在做update操作的时候倘若某个实体bean的属性是null时,数据库中对应的字段也会变成null,这样就会出现问题,此时我们可以通过set标签解决,在mapper文件中添加下面内容

<update id="updateStudent">
    UPDATE t_student
    <set>
        <if test="name != null">name=#{name},</if>
        <if test="age >= 0">age=#{age},</if>
        <if test="score > 30">score=#{score},</if>
    </set>
    WHERE id=#{id}
</update>

mybatis会忽略不满足条件的数据,并且会删除额外的逗号。与该语句等价的trim如下:

<update id="updateStudent">
    UPDATE t_student
    <trim prefix="SET" suffixOverrides=",">
        <if test="name != null">name=#{name},</if>
        <if test="age >= 0">age=#{age},</if>
        <if test="score > 30">score=#{score},</if>
    </trim>
    WHERE id=#{id}
</update>

foreach标签遍历数组

有时候会有这样的操作,用户需要查询Student的id是5,6,10,15的数据,这些数据可能会被放到数组里面作为参数进行传递,以前我们可以在sql语句中使用in来实现,在mybatis中就可以使用foreach标签。

foreach标签的属性中

  • collection 表示要遍历的集合类型,这里是数组,即 array。
  • open、close、separator 为对遍历内容的 SQL 拼接。

在mapper文件中添加下面内容:

<select id="selectForeachArray" resultType="student">
    SELECT id,name,age,score
    FROM t_student
    <if test="array != null and array.length>0">
      WHERE id IN 
        <foreach collection="array" open="(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </if>
</select>

需要注意的是在if标签中的test属性里面,array是固定写法,表示数组。

在dao中添加方法:

List<Student> selectForeachArray(Object[] ids);

在测试类中添加测试方法:

@Test
public void selectForeachArray(){
    Object[] ids = new Object[]{5,6,10,15};

    List<Student> students = studentDao.selectForeachArray(ids);
    students.forEach((s)-> {
        System.out.println(s);
    });
}

foreach标签遍历基本数据类型的集合

遍历集合的方式跟数组差不多,只不过有些地方需要稍作修改。
在mapper中创建sql:

<select id="selectForeachList" resultType="student">
    SELECT id,name,age,score
    FROM t_student
    <if test="list != null and list.size>0">
        WHERE id IN
        <foreach collection="list" open="(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </if>
</select>

需要注意这里在if的test属性中使用的是list和list.size,foreach中的collection也是list。

在dao中添加方法:

List<Student> selectForeachList(List<Integer> ids);

在测试类中添加测试方法:

@Test
public void selectForeachList(){
    List<Integer> list = new ArrayList<>();
    list.add(5);
    list.add(6);
    list.add(10);
    list.add(15);

    List<Student> students = studentDao.selectForeachList(list);
    students.forEach((s)-> {
        System.out.println(s);
    });
}

foreach标签遍历自定义数据类型的集合

整体方式跟之前的差不多。
在mapper中添加下面内容:

<select id="selectForeachListStudent" resultType="student">
    SELECT id,name,age,score
    FROM t_student
    <if test="list != null and list.size>0">
        WHERE id IN
        <foreach collection="list" open="(" close=")" item="stu" separator=",">
            #{stu.id}
        </foreach>
    </if>
</select>

在dao中添加方法:

List<Student> selectForeachListStudent(List<Student> students);

在测试类中添加方法:

@Test
public void selectForeachListStudent(){
    List<Student> list = new ArrayList<>();

    Student s1 = new Student();
    s1.setId(5);
    Student s2 = new Student();
    s2.setId(10);
    list.add(s1);
    list.add(s2);

    List<Student> students = studentDao.selectForeachListStudent(list);
    students.forEach((s)-> {
        System.out.println(s);
    });
}

sql标签

sql标签可以用来定义一个可被复用的sql片段,在使用的时候写上include标签就可以将sql标签中的内容引入。当某段sql语句会被多次使用时,可以将这段sql语句放到sql标签中。

在mapper中添加和使用sql标签:

<!--定义sql片段-->
<sql id="select">
    SELECT id,name,age,score
    FROM t_student
</sql>

<select id="selectSQL" resultType="student">
    <!--使用sql片段-->
    <include refid="select"/>

    <if test="list != null and list.size>0">
        WHERE id IN
        <foreach collection="list" open="(" close=")" item="stu" separator=",">
            #{stu.id}
        </foreach>
    </if>
</select>

在dao中添加方法:

List<Student> selectSQL(List<Student> students);

添加测试方法:

@Test
public void selectSQL(){
    List<Student> list = new ArrayList<>();

    Student s1 = new Student();
    s1.setId(5);
    Student s2 = new Student();
    s2.setId(10);
    list.add(s1);
    list.add(s2);

    List<Student> students = studentDao.selectSQL(list);
    students.forEach((s)-> {
        System.out.println(s);
    });
}