Mybatis多表关联查询与动态SQL

最新推荐文章于 2024-04-26 10:00:39 发布

飞川撸码

最新推荐文章于 2024-04-26 10:00:39 发布

阅读量1.8k

点赞数 1

分类专栏： maven 文章标签： sql java mybatis SSM idea

本文链接：https://blog.csdn.net/qq_47831505/article/details/124830555

版权

maven 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

Mybatis多表关联查询与动态SQL

一、多表关联查询

表与表之间有三种常见的关联关系，分别是一对一，一对多与多对多关系，MyBatis直接提供一对一与一对多的关联关系，可以通过间接的方式实现多对多关联。
1.1、一对一关系
1.1.1、执行环境

1.1.2、关联查询（1次查询
实体：

用户：

/**用户POJO*/
public class User {
 private int id;
 private String username;
 private String password;

 public int getId() {
 return id;
 }

 public void setId(int id) {
 this.id = id;
 }

 public String getUsername() {
 return username;
 }

 public void setUsername(String username) {
 this.username = username;
 }

 public String getPassword() {
 return password;
 }

 public void setPassword(String password) {
 this.password = password;
 }
}

员工：

/**员工POJO*/
public class Emp {
 private int id;
 /**用户编号*/
 private int user_id;
 private String realname;
 private String email;

 /**用户对象*/
 private User user;

 public int getId() {
 return id;
 }

 public void setId(int id) {
 this.id = id;
 }

 public int getUser_id() {
 return user_id;
 }

 public void setUser_id(int user_id) {
 this.user_id = user_id;
 }

 public String getRealname() {
 return realname;
 }

 public void setRealname(String realname) {
 this.realname = realname;
 }

 public String getEmail() {
 return email;
 }

 public void setEmail(String email) {
 this.email = email;
 }

 public User getUser() {
 return user;
 }

 public Emp setUser(User user) {
 this.user = user;
 return this;
 }
}

接口：

import com.zhangguo.mybatis03.entities.Emp;

/**员工数据访口*/
public interface EmpMapper {

 /**获得员工通过员工编号*/
 Emp getEmpById_1(int id);

}

映射：

<?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="com.zhangguo.mybatis03.dao.EmpMapper">

 <!--一对一查询，方法1，通过内联接-->
 <select id="getEmpById_1" resultMap="empMap_1" parameterType="int">
 SELECT
 emp.id,
 emp.user_id,
 emp.realname,
 emp.email,
 `user`.username,
 `user`.`password`
 FROM
 emp
 INNER JOIN `user` ON emp.user_id = `user`.id where emp.id=#{id}
 </select>

 <!--员工关联查询结果映射-->
 <resultMap id="empMap_1" type="Emp">
 <id property="id" column="id"></id>
 <result property="user_id" column="user_id"></result>
 <result property="realname" column="realname"></result>
 <result property="email" column="email"></result>
 <!--映射关系，指定属性与属性的类型-->
 <association property="user" javaType="User">
 <id property="id" column="user_id"></id>
 <result property="username" column="username"></result>
 <result property="password" column="password"></result>
 </association>
 </resultMap>

</mapper>

测试：

import com.zhangguo.mybatis03.entities.Emp;
import org.junit.Assert;
import org.junit.Test;
import org.junit.Before;
import org.junit.After;

/**
 * EmpDao Tester.
 *
 * @author <Authors name>
 * @version 1.0
 * @since <pre>09/30/2018</pre>
 */
public class EmpDaoTest {
 EmpMapper empDao;
 @Before
 public void before() throws Exception {
 empDao=new EmpDao();
 }

 @After
 public void after() throws Exception {
 }

 /**
 * Method: getEmpById_1(int id)
 * 获得员工通过员工编号
 */
 @Test
 public void testGetEmpById_1() throws Exception {
 Emp entity=empDao.getEmpById_1(1);
 System.out.println(entity);
 Assert.assertNotNull(entity);
 }


}

1.1.3、嵌套查询（2次查询）

实体：同上

接口：

 /**获得员工通过员工编号，多次查询*/
 Emp getEmpById_2(int id);

映射：

 <!--一对一查询，方法2，通过多次查询（嵌套查询）-->
 <select id="getEmpById_2" resultMap="empMap_2">
 SELECT
 emp.id,
 emp.user_id,
 emp.realname,
 emp.email
 FROM
 emp where id=#{id}
 </select>

 <!--员工多次查询结果映射-->
 <resultMap id="empMap_2" type="Emp">
 <id property="id" column="id"></id>
 <result property="user_id" column="user_id"></result>
 <result property="realname" column="realname"></result>
 <result property="email" column="email"></result>
 <!--通过外键user_id再次发起查询，调用selectUserById获得User对象-->
 <association property="user" column="user_id" select="selectUserById"></association>
 </resultMap>

 <!--根据用户编号获得用户对象-->
 <select id="selectUserById" resultType="User">
 SELECT
 `user`.id,
 `user`.username,
 `user`.`password`
 FROM
 `user` where id=#{id}
 </select>

测试：

 /**
 * Method: getEmpById_2(int id)
 * 获得员工通过员工编号，一对一方法二
 */
 @Test
 public void testGetEmpById_2() throws Exception {
 Emp entity=empDao.getEmpById_2(2);
 System.out.println(entity);
 Assert.assertNotNull(entity);
 }

学习总结

MyBatis中使用association标签来解决一对一的关联查询，association标签可用的属性如下：

property:对象属性的名称
javaType:对象属性的类型
column:所对应的外键字段名称
select:使用另一个查询封装的结果

1.2、一对多关系
1.2.1、执行环境

1.2.2、关联查询（1次查询）
实体：

员工：

/**员工POJO*/
public class Emp {
 private int id;
 /**用户编号*/
 private int user_id;
 private String realname;
 private String email;

 /**用户对象*/
 private User user;

 public int getId() {
 return id;
 }

 public void setId(int id) {
 this.id = id;
 }

 public int getUser_id() {
 return user_id;
 }

 public void setUser_id(int user_id) {
 this.user_id = user_id;
 }

 public String getRealname() {
 return realname;
 }

 public void setRealname(String realname) {
 this.realname = realname;
 }

 public String getEmail() {
 return email;
 }

 public void setEmail(String email) {
 this.email = email;
 }

 public User getUser() {
 return user;
 }

 public Emp setUser(User user) {
 this.user = user;
 return this;
 }

 @Override
 public String toString() {
 return "Emp{" +
 "id=" + id +
 ", user_id=" + user_id +
 ", realname='" + realname + '\'' +
 ", email='" + email + '\'' +
 ", user=" + user +
 '}';
 }
}

用户：

import java.util.List;

/**用户POJO*/
public class User {
 private int id;
 private String username;
 private String password;

 /**员工集合，一个用户对象对应多个员工对象*/
 private List<Emp> emps;

 public int getId() {
 return id;
 }

 public void setId(int id) {
 this.id = id;
 }

 public String getUsername() {
 return username;
 }

 public void setUsername(String username) {
 this.username = username;
 }

 public String getPassword() {
 return password;
 }

 public void setPassword(String password) {
 this.password = password;
 }

 public List<Emp> getEmps() {
 return emps;
 }

 public User setEmps(List<Emp> emps) {
 this.emps = emps;
 return this;
 }

 @Override
 public String toString() {
 return "User{" +
 "id=" + id +
 ", username='" + username + '\'' +
 ", password='" + password + '\'' +
 ", emps=" + emps +
 '}';
 }
}

接口：

 /**获得用户通过用户编号，1对多级联查询*/
 User getUserById_1(int id);

映射：

 <!--一对多查询，方法1，通过内联接-->
 <select id="getUserById_1" resultMap="userMap_1" parameterType="int">
 SELECT
 emp.id,
 emp.user_id,
 emp.realname,
 emp.email,
 `user`.username,
 `user`.`password`
 FROM
 emp
 INNER JOIN `user` ON emp.user_id = `user`.id
 where `user`.id=#{id}
 </select>

 <resultMap id="userMap_1" type="User">
 <id property="id" column="user_id"></id>
 <result property="username" column="username"></result>
 <result property="password" column="password"></result>
<!--将emps对象映射成一个集合，emps是user类型中的属性，ofType用于指定集合中存放的对象类型-->
 <collection property="emps" ofType="Emp">
 <id property="id" column="id"></id>
 <result property="user_id" column="user_id"></result>
 <result property="realname" column="realname"></result>
 <result property="email" column="email"></result>
 </collection>
 </resultMap>
测试：

 /**
 * Method: getUserById_1(int id)
 * 获得用户过用户编号，级联查询
 */

 @Test
 public void testGetUserById_1() throws Exception {
 User entity=empDao.getUserById_1(2);
 System.out.println(entity);
 Assert.assertNotNull(entity);
 }

映射：

 <resultMap id="userMap_1" type="User">
 <id property="id" column="user_id"></id>
 <result property="username" column="username"></result>
 <result property="password" column="password"></result>
 <!--将emps对象映射成一个集合，emps是user类型中的属性，ofType用于指定集合中存放的对象类型-->
 <collection property="emps" ofType="Emp">
 <id property="id" column="id"></id>
 <result property="user_id" column="user_id"></result>
 <result property="realname" column="realname"></result>
 <result property="email" column="email"></result>
 <!--映射关系，指定属性与属性的类型-->
 <association property="user" javaType="User">
 <id property="id" column="user_id"></id>
 <result property="username" column="username"></result>
 <result property="password" column="password"></result>
 </association>
 </collection>
 </resultMap>

1.1.3、嵌套查询（多次查询）
实体：同上

接口：

 /**获得用户通过用户编号，1对多嵌套查询*/
 User getUserById_2(int id);

映射：

 <!--一对多查询，方法2，通过嵌套查询多次-->
 <select id="getUserById_2" resultMap="userMap_2" parameterType="int">
 SELECT
 `user`.id,
 `user`.username,
 `user`.`password`
 FROM
 `user` where id=#{id}
 </select>

 <resultMap id="userMap_2" type="User">
 <id property="id" column="user_id"></id>
 <result property="username" column="username"></result>
 <result property="password" column="password"></result>
 <!--将emps对象映射成一个集合，emps是user类型中的属性，ofType用于指定集合中存放的对象类型-->
 <!--select用于指定再次查询的SQL编号，column用于指定参数列-->
 <collection property="emps" ofType="Emp" column="id" select="selectEmpById"></collection>
 </resultMap>

 <!--根据员工编号获得员工对象-->
 <select id="selectEmpById" resultType="Emp">
 SELECT
 emp.id,
 emp.user_id,
 emp.realname,
 emp.email
 FROM
 emp where user_id=#{id}
 </select>

测试：

 /**
 * Method: getUserById_2(int id)
 * 获得用户过用户编号，嵌套查询
 */
 @Test
 public void testGetUserById_2() throws Exception {
 User entity=empDao.getUserById_2(5);
 System.out.println(entity);
 Assert.assertNotNull(entity);
 }

学习总结:

MyBatis中使用collection标签来解决一对多的关联查询，ofType属性指定集合中元素的对象类型。

二、动态SQL

2.0、MySQL环境与前置要求
数据与SQL环境如下：

2.1、什么是动态SQL
MyBatis的动态SQL是基于OGNL的表达式的。它对SQL语句进行灵活的操作，通过表达式判断来实现对SQL的灵活拼接、组装。

mybatis核心对sql语句进行灵活操作，通过表达式进行判断，对sql进行灵活拼接、组装。

主要通过以下标签：if，where，choose（when，otherwise），trim，set，foreach。

2.2、if条件判断
根据 name和 sex 来查询数据。如果name为空，那么将只根据sex来查询；反之只根据name来查询

首先不使用动态SQL 来书写

接口：

    /**
     * 根据学生姓名和性别获得学生集合
     */
    List<Student> selectStudentsByNameAndSex(@Param("name") String name,@Param("sex") String sex);
映射：

    <select id="selectStudentsByNameAndSex" resultType="student">
        SELECT id,name,sex from student where name=#{name} and sex=#{sex};
    </select>

测试：

/**

Method: selectStudentsByNameAndSex
*/
@Test
public void testSelectStudentsByNameAndSex() throws Exception {
List students=dao.selectStudentsByNameAndSex(“rose”,null);
System.out.println(students);
Assert.assertNotNull(students);
}

上面的查询语句，我们发现如果 #{sex} 为空，那么查询结果也是空，如何解决这个问题呢？使用 if 来判断

<select id="selectStudentsByNameAndSex" resultType="student">

    SELECT id,name,sex from student where 1=1
    <!--如果test为真会输出中间的内容-->
    <if test="name!=null and name!=''">
        and name=#{name}
    </if>

    <if test="sex!=null and sex!=''">
        and sex=#{sex}
    </if>

</select>



<!-- 2 if(判断参数) - 将实体类不为空的属性作为where条件 -->
<select id="getStudentList_if" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity">
 SELECT ST.STUDENT_ID,
 ST.STUDENT_NAME,
 ST.STUDENT_SEX,
 ST.STUDENT_BIRTHDAY,
 ST.STUDENT_PHOTO,
 ST.CLASS_ID,
 ST.PLACE_ID
 FROM STUDENT_TBL ST
 WHERE
 <if test="studentName !=null ">
 ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')
 </if>
 <if test="studentSex != null and studentSex != '' ">
 AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}
 </if>
 <if test="studentBirthday != null ">
 AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}
 </if>
 <if test="classId != null and classId!= '' ">
 AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}
 </if>
 <if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=' ' ">
 AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}
 </if>
 <if test="placeId != null and placeId != '' ">
 AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}
 </if>
 <if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != '' ">
 AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}
 </if>
 <if test="studentId != null and studentId != '' ">
 AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}
 </if>
</select> 
 虽然1=1这种方法结合if可以解决我们的需求，但是1=1明显是冗余的，通过where可以解决。

2.3、where条件
where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。

修改后的映射：

SELECT id,name,sex from student

<!--1、如果两个if只要有一个有输出就会在sql中添加 where-->
<where>
    <if test="name!=null and name!=''">
        <!--2、如果where后以and或or开始则会删除and或or-->
        and name like concat(concat('%',#{name}),'%');
    </if>

    <if test="sex!=null and sex!=''">
        and sex=#{sex}
    </if>
</where>

这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

where标记的作用类似于动态sql中的set标记，他的作用主要是用来简化sql语句中where条件判断的书写的，如下所示：

　　　　select * from user
　　　　
　　　　　　id=#{id}
　　　　　　and name=#{name}
　　　　　　and gender = #{gender}
　　　　
　　

where 标记会自动将其后第一个条件的and或者是or给忽略掉

2.4、if+set设置值
当update语句中没有使用if标签时，如果有一个参数为null，都会导致错误。

当在update语句中使用if标签时，如果前面的if没有执行，则或导致逗号多余错误。使用set标签可以将动态的配置SET 关键字，和剔除追加到条件末尾的任何不相关的逗号。如果set包含的内容为空的话则会出错。

使用if+set标签修改后，如果某项为null则不进行更新，而是保持数据库原值。

如果通过if判断表面可以解决问题，如下所示：

update student set

<if test="name!=null and name.lenght()>0">
    name=#{name} ,
</if>

<if test="sex!=null and sex.lenght()>0">
    sex=#{sex}
</if>

where id=#{id}

这样做也会有问题，就是当sex为空时的sql就变成了 update student set name=#{name} , where id=#{id}，这明显是错误的。

同理，上面的对于查询 SQL 语句包含 where 关键字，如果在进行更新操作的时候，含有 set 关键词，我们怎么处理呢？

接口：

/**
 * 更新学生
 */
int updateStudent(Student entity);

映射：

update student name=#{name}

    <if test="sex!=null and sex.length()>0">
        sex=#{sex}
    </if>
</set>
where id=#{id}

注意：某些情况下逗号必须添加，如下所示：

update student name=#{name} ,

    <if test="sex!=null and sex.length()>0">
        sex=#{sex} ,
    </if>
</set>
where id=#{id}

这样写，如果第一个条件 name 为空，那么 sql 语句为：update student set sex=? where id=?

如果第一个条件不为空，那么 sql 语句为：update student u set name= ? , sex = ? where id=?

set主要解决了自动添加标签与处理逗号的问题，另外这种更新方法比较以前的全部更新方式在开发中性能更高。

2.5、choose(when,otherwise) 开关
如果不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 的 switch 语句。

假定这里需要优先根据编号搜索，没有时选择name，最后考虑sex：

接口：

/**
 * 根据学生编号、姓名和性别获得学生集合
 */
List<Student> selectStudentsByNameAndSex(@Param("id") int id, @Param("name") String name,@Param("sex") String sex);

映射：

SELECT id,name,sex from student
<where>
<choose>
    <when test="id>0">
        id=#{id}
    </when>
    <when test="name!=null and name!=''">
        name=#{name}
    </when>
    <otherwise>
        sex=#{sex}
    </otherwise>
</choose>
</where>

也就是说，这里我们有三个条件，id,name,sex，只能选择一个作为查询条件

如果 id 不为空，那么查询语句为：select * from student where id=?

如果 id 为空，那么看name是否为空，如果不为空，那么语句为 select * from student where name=?;

如果name为空，那么查询语句为 select * from student where sex=?

2.6、trim裁剪
trim标记是一个格式化的标记，可以完成set或者是where标记的功能

①、用 trim 改写上面第二点的 if+where 语句

if+where的办法：

  <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT id,name,sex from student

        <!--1、如果两个if只要有一个有输出就会在sql中添加 where-->
        <where>
            <if test="name!=null and name!=''">
                <!--2、如果where后以and或or开始则会删除and或or-->
                and name like concat(concat('%',#{name}),'%');
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </where>

    </select>

trim的办法：

 <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT id,name,sex from student

        <!--1、prefix表示将前置where，prefixOverrides将删除打头内容-->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name!=null and name!=''">
                and name like concat(concat('%',#{name}),'%')
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </trim>

    </select>

prefix：将加上前缀

prefixoverride：去掉第一个and或者是or

②、用 trim 改写上面第三点的 if+set 语句

if+set的方法：

  <update id="updateStudent" parameterType="student">
        update student
        <!--自动添加set-->
        <set>
            <!--智能处理逗号问题-->
            <if test="name!=null and name.length()>0">
                name=#{name}
            </if>

            <if test="sex!=null and sex.length()>0">
                sex=#{sex}
            </if>
        </set>
        where id=#{id}
    </update>

trim的方法：

update student

   <trim prefix="set" suffixOverrides=",">
       <if test="name!=null and name.length()>0">
           name=#{name},
       </if>

       <if test="sex!=null and sex.length()>0">
           sex=#{sex},
       </if>
   </trim>

where id=#{id}

suffix：后缀

suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

可以自定义添加前后缀，与之对应的属性是prefix和suffix。同时通过prefixOverrides和suffixOverrides分别来覆盖首尾部的内容，即忽略不必要的前后缀。就是说它可以充当where标签，也可以充当set标签啦~