sqlchemy多对多查询 多对一查询sql

数据库表(如果没有,自行创建)

 

这里主要关注user(用户表)和orders(订单表),两张表之间的关系属于一对多关系,即一个用户可能对应多个订单(在订单表中添加uid外键,维持两表关系)

user用户表字段和结构

Field          Type          Collation        Null    Key     Default  Extra           Privileges                       Comment       
-------------  ------------  ---------------  ------  ------  -------  --------------  -------------------------------  --------------
id             int(11)       (NULL)           NO      PRI     (NULL)   auto_increment  select,insert,update,references                
user_name      varchar(32)   utf8_general_ci  NO              (NULL)                   select,insert,update,references  用户名称  
user_birthday  timestamp     (NULL)           YES             (NULL)                   select,insert,update,references  生日        
user_sex       char(1)       utf8_general_ci  YES             (NULL)                   select,insert,update,references  性别        
home_address   varchar(256)  utf8_general_ci  YES             (NULL)                   select,insert,update,references  地址

orders订单表字段和结构

Field      Type      Collation  Null    Key     Default  Extra           Privileges                       Comment  
---------  --------  ---------  ------  ------  -------  --------------  -------------------------------  ---------
id         int(11)   (NULL)     NO      PRI     (NULL)   auto_increment  select,insert,update,references           
uid        int(11)   (NULL)     YES     MUL     (NULL)                   select,insert,update,references           
ordertime  datetime  (NULL)     YES             (NULL)                   select,insert,update,references           
money      double    (NULL)     YES             (NULL)                   select,insert,update,references

user用户表对应的实体类

@Data
public class User implements Serializable {
    private static final long serialVersionUID = -6391149300294480283L;
    private Integer id;
    private String username;
    private Date birthday;
    private Character sex;
    private String homeAddress;
}

orders订单表对应的实体类

@Data
public class Order implements Serializable {
    private static final long serialVersionUID = 4708094005499936459L;
    private Integer id;
    private Integer uid;//外键,指向用户表
    private Date orderTime;
    private Double money;
}

一对一查询SQL(多对一其实就是一对一)

需求:查询所有的订单列表信息,同时查询出订单所属的用户信息

分析:订单对应用户,即一对一的关系,我们使用左外连接(LEFT JOIN... 主表.主键=从表.从键).既然是查询所有的订单信息,所以订单表是左表,那么用户表就是右表

一对一查询使用的标签:<association>

Mapper.xml

<?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.mybatisplus.mapper.Mapper">
  
    <resultMap id="orderMap" type="com.mybatisplus.pojo.Order">
        <id column="id" property="id"></id>
        <result column="ordertime" property="orderTime"></result>
        <result column="money" property="money"></result>
        <association property="user" javaType="com.mybatisplus.pojo.User">
            <id column="id" property="id"></id>
            <result column="user_name" property="username"></result>
            <result column="user_birthday" property="birthday"></result>
            <result column="user_sex" property="sex"></result>
            <result column="home_address" property="homeAddress"></result>
        </association>
    </resultMap>

    <select id="selectOrderWithUser" resultMap="orderMap">
        SELECT o.id , o.ordertime , o.money , u.user_name , u.user_sex from `orders` o left join `user` u on u.id = o.uid
    </select>
</mapper>

如果你的实体类也和我上面的一样,那么你会发现<association property="user">中的user显示的是红色.这表示有错误.测试结果也不会返回正确的信息.

请注意:如果你的sql也是这么写的,我们需要在order实体类中,添加User类型的变量,这样才能将User和order关联在一起.

@Data
public class Order implements Serializable {
    private static final long serialVersionUID = 4708094005499936459L;
    private Integer id;
    private Integer uid;//外键,指向用户表
    private Date orderTime;
    private Double money;
    private User user;

修改Order实体类后,发现<association property="user">中的user显示正常,再次测试成功!

一对多查询SQL

需求:查询所有的用户,同时查询出每个用户所属的所有订单信息

分析:用户对应订单,即一对多的关系,查询所有的用户,即用户表在左,订单表在右

一对多查询使用的标签:<collection>

Mapper.xml

<?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.mybatisplus.mapper.Mapper">
   
    <resultMap id="userMap" type="com.mybatisplus.pojo.User">
        <id column="id" property="id"></id>
        <result column="user_name" property="username"></result>
        <result column="user_birthday" property="birthday"></result>
        <result column="user_sex" property="sex"></result>
        <result column="home_address" property="homeAddress"></result>
        <collection property="orderList" ofType="com.mybatisplus.pojo.Order">
            <id column="id" property="id"></id>
            <result column="ordertime" property="orderTime"></result>
            <result column="money" property="money"></result>
        </collection>
    </resultMap>
  
    <select id="selectUserWithOrder" resultMap="userMap">
        SELECT u.user_name , u.user_birthday , u.user_sex , u.home_address , o.ordertime , o.money FROM `user` u LEFT JOIN `orders` o ON U.id = o.uid
    </select>
</mapper>

同样的,如果你的User实体类也是和我上面定义的一样,会发现<collection property="orderList">中的orderList显示红色,原因便是在User实体类中没有定义该属性

我们需要在User类中添加集合属性,集合中的对象就是Order

@Data
public class User implements Serializable {
    private static final long serialVersionUID = -6391149300294480283L;
    private Integer id;
    private String username;
    private Date birthday;
    private Character sex;
    private String homeAddress;
    private List<Order> orderList;
}

修改后xml中不再报错,查询结果也成功返回.

总结:

一对一查询使用的标签:<association>

一对多查询使用的标签:<collection>

 追加():

对于三张表或三张表以上的多表查询,sql语法分析主要分为两点:

1.看你要查询的数据都有哪些?

如果你所要查找的数据只存在于一张表中,那么只需要在mapper.xml中直接编写sql语句即可,不需要将某个实体类引入到另一个实体类中作为其属性,更不需要使用<association>或者<collection>关联标签

2.缕清sql语法所涉及的所有表之间的表关系

举个例子:根据学生姓名查询对应的班主任姓名,同时查询出班主任所教授的课程名称

(表关系:课程表对班主任表是一对一的关系,班主任表对学生表是一对多的关系)

<?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.mybatisplus.mapper.StudentMapper">
    <resultMap id="courseResultMap" type="com.mybatisplus.pojo.Course">
        <id column="c_id" property="cid"></id>
        <result column="c_name" property="cName"></result>
        <association property="teacher" javaType="com.mybatisplus.pojo.Teacher">
            <id column="t_id" property="tid"></id>
            <result column="t_name" property="tName"></result>
            <collection property="studentList" ofType="com.mybatisplus.pojo.Student">
                <id column="s_id" property="sid"></id>
                <result column="s_name" property="sName"></result>
                <result column="s_birth" property="sBirth"></result>
                <result column="s_sex" property="sSex"></result>
            </collection>
        </association>
    </resultMap>
    <select id="selectCourseAndTeacherByStudent" resultMap="courseResultMap">
    SELECT t.`t_name` , c.c_name FROM `course` c LEFT JOIN `teacher` t ON c.t_id = t.`t_id` LEFT JOIN `student` st ON t.`t_id` = st.`t_id` WHERE st.`s_name`=#{sName}
    </select>
</mapper>

考虑到最终要查询到的数据是课程名称和班主任姓名,我们需要在课程实体类中引入班主任属性,因为是一对一的关系,所以使用<association>标签;在班主任实体类中引入集合属性(集合对象为学生),因为是一对多关系,所以使用<collection>标签.

也就是说,<association>和<collection>标签是可以互相嵌套使用的.