MyBatis一对多,多对一和多对多联表查询

2021-12-03
2

MyBatis中的一对多和对多一,多对多
主要就是resultMap中
association – 一个复杂的类型关联;许多结果将包成这种类型(一对多)
collection – 复杂类型的集合(多对一)
这2个属性的使用,而一对多和多对一都是相互的,只是站的角度不同。
实例:
这个实例只说这2个属性的使用方法,具体的配置和运行结果在SSM环境搭建文章中写出。
1.首先是数据库,沿用之前员工管理系统的数据库设计点击这里,上面是之前写过的一篇数据库设计,包含sql语句。

2.实体类:
我这里只列要实现映射功能用到的类user,role,department。department和user,是1对多关系,1个部门拥有多个员工。
role和user是多对多关系,1个角色可能有多个员工,而1个员工也可能有多个角色。而要实现多对多,在程序中则是拆分成2个一对多,详情见下面的实体类注释。

1)User.java

public class User {
    
    
    private int user_id;
    private String user_name;
    private String user_gender;
    private String user_email;
    private String user_phone;
    private String user_address;
    private Date user_birthday;
    private int department_id;

    //一对多,1个用户可能有多个角色。
    private List<Role> roles;

    //多对一,1个部门多个员工
    private Department department;

    public User() {
        super();
    }

    public User(String user_name, String user_gender,
            String user_email, String user_phone, String user_address,
            Date user_birthday, int department_id) {
        super();
        this.user_name = user_name;
        this.user_gender = user_gender;
        this.user_email = user_email;
        this.user_phone = user_phone;
        this.user_address = user_address;
        this.user_birthday = user_birthday;
        this.department_id = department_id;
    }

    public int getUser_id() {
        return user_id;
    }

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

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getUser_gender() {
        return user_gender;
    }

    public void setUser_gender(String user_gender) {
        this.user_gender = user_gender;
    }

    public String getUser_email() {
        return user_email;
    }

    public void setUser_email(String user_email) {
        this.user_email = user_email;
    }

    public String getUser_phone() {
        return user_phone;
    }

    public void setUser_phone(String user_phone) {
        this.user_phone = user_phone;
    }

    public String getUser_address() {
        return user_address;
    }

    public void setUser_address(String user_address) {
        this.user_address = user_address;
    }

    public Date getUser_birthday() {
        return user_birthday;
    }

    public void setUser_birthday(Date user_birthday) {
        this.user_birthday = user_birthday;
    }

    public int getDepartment_id() {
        return department_id;
    }

    public void setDepartment_id(int department_id) {
        this.department_id = department_id;
    }

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public List<Role> getRoles() {
        return roles;
    }

    public void setRoles(List<Role> roles) {
        this.roles = roles;
    }

    @Override
    public String toString() {
        return "User [user_id=" + user_id + ", user_name=" + user_name
                + ", user_gender=" + user_gender + ", user_email=" + user_email
                + ", user_phone=" + user_phone + ", user_address="
                + user_address + ", user_birthday=" + user_birthday
                + ", department_id=" + department_id + ", roles=" + roles
                + ", department=" + department + "]\n";
    }
}
 
  
  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123

2)Role.java

public class Role {
    
    
    private int role_id;
    private String role_name;

    //一对多,1个角色也可能被多个用户拥有,所以是list
    private List<User> users;

    public Role() {

    }

    public Role(int role_id, String role_name) {
        super();
        this.role_id = role_id;
        this.role_name = role_name;
    }

    public int getRole_id() {
        return role_id;
    }

    public void setRole_id(int role_id) {
        this.role_id = role_id;
    }

    public String getRole_name() {
        return role_name;
    }

    public void setRole_name(String role_name) {
        this.role_name = role_name;
    }

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }

    @Override
    public String toString() {
        return "Role [role_id=" + role_id + ", role_name=" + role_name
                + ", users=" + users + "]";
    }

}

 
  
  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49

3)Department.java

public class Department {
    
    
    private int department_id;
    private String department_name;

    //1对多,1个部门多个用户
    private List<User> users;

    public Department() {
        super();
    }

    public Department(int department_id, String department_name,
            List<User> users) {
        super();
        this.department_id = department_id;
        this.department_name = department_name;
        this.users = users;
    }

    public int getDepartment_id() {
        return department_id;
    }

    public void setDepartment_id(int department_id) {
        this.department_id = department_id;
    }

    public String getDepartment_name() {
        return department_name;
    }

    public void setDepartment_name(String department_name) {
        this.department_name = department_name;
    }

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }

    @Override
    public String toString() {
        return "Department [department_id=" + department_id
                + ", department_name=" + department_name + ", users=" + users
                + "]"+"\n";
    }
}
 
  
  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50

3.实体类的mapper文件
1)UserDepartmentMapper.xml,多对一,多个user对应1个department,使用association

<?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="employee_management.mapper.UserDepartmentMapper">
    <resultMap type="employee_management.entity.User"
        id="userDepartmentList">
        <id property="user_id" column="user_id" javaType="java.lang.Integer" />
        <result property="user_name" column="user_name" javaType="java.lang.String" />
        <result property="user_gender" column="user_gender" javaType="java.lang.String" />
        <result property="user_email" column="user_email" javaType="java.lang.String" />
        <result property="user_phone" column="user_phone" javaType="java.lang.String" />
        <result property="user_address" column="user_address" javaType="java.lang.String" />
        <result property="user_birthday" column="user_birthday"
            javaType="java.util.Date" />
        <result property="department_id" column="department_id"
            javaType="java.lang.Integer" />
            <!-- property是user实体类中department类属性的名字
            多个user对应1个department-->
        <association property="department"
            javaType="employee_management.entity.Department">
            <id property="department_id" column="department_id" javaType="java.lang.Integer" />
            <result property="department_name" column="department_name"
                javaType="java.lang.String" />
        </association>
    </resultMap>

    <select id="getAll" resultMap="userDepartmentList">
        select u.*,d.department_name from user u left join department d on u.department_id=d.department_id;
    </select>
</mapper>
 
  
  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

2)UserMapper.xml,一对多,使用collection,1个user对应多个role,同时也是多对多中user这一方的一对多。如果要实现多对多,还需要完成1个role对应多个user,即完成2个1对多,写法同这里,就不在赘述。

<?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="employee_management.mapper.UserMapper">

    <!-- get user list only -->
    <resultMap type="employee_management.entity.User" id="userList">
        <id property="user_id" column="user_id" javaType="java.lang.Integer" />
        <result property="user_name" column="user_name" javaType="java.lang.String" />
        <result property="user_gender" column="user_gender" javaType="java.lang.String" />
        <result property="user_email" column="user_email" javaType="java.lang.String" />
        <result property="user_phone" column="user_phone" javaType="java.lang.String" />
        <result property="user_address" column="user_address" javaType="java.lang.String" />
        <result property="user_birthday" column="user_birthday"
            javaType="java.util.Date" />
        <result property="department_id" column="department_id"
            javaType="java.lang.Integer" />
    </resultMap>

    <!-- get user list with role -->
    <resultMap type="employee_management.entity.User" id="userRoleList"
        extends="userList">
        <!-- collection中不是javaType而是ofType,通过property也知道是list,即多个。
        extends属性,即在父resultMap的基础上再加上子resultMap的结果,在这里即再加上部门的信息。 -->
        <collection property="roles" ofType="employee_management.entity.Role">
            <id property="role_id" column="role_id" javaType="java.lang.Integer" />
            <result property="role_name" column="role_name" javaType="java.lang.String" />
        </collection>
    </resultMap>

    </resultMap>

<select id="getAll" resultMap="userList">
        select * from user
    </select>
<select id="getAllUserAndRole" resultMap="userRoleList">
        select u.*,r.* from
        user u left join user_role ur on u.user_id=ur.user_id
        left join role r
        on r.role_id=ur.role_id;
    </select>
 
  
  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42

不管是一对多还是多对一,还是多对多,只需要知道这2个属性哪个代表多哪个代表1,就可以很好的在实体的mapper文件中配置出来。

评论