#MyBatis多表查询 #多对多查询 @FDDLC

程序员文章站 2022-07-12 23:16:14

...

我们知道，用户与角色之间存在多对多的关系：

关键配置1：

<mapper namespace="cn.liuxingchang.dao.UserDao">
    <resultMap id="userWithRole" type="cn.liuxingchang.domain.User">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="age" column="age" />
        <collection property="roles" ofType="cn.liuxingchang.domain.Role">
            <id property="id" column="rid" />
            <result property="role" column="role" />
            <result property="info" column="info" />
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userWithRole">
        select u.*, r.id as rid, role, info from user u left outer join user_role ur on u.id = ur.uid
         left outer join role r on ur.rid = r.id
    </select>

关键配置2：

<resultMap id="roleWithUser" type="cn.liuxingchang.domain.Role">
    <id property="id" column="id" />
    <result property="role" column="role" />
    <result property="info" column="info" />
    <collection property="users" ofType="cn.liuxingchang.domain.User">
        <id property="id" column="uid" />
        <result property="name" column="name" />
        <result property="age" column="age" />
    </collection>
</resultMap>

<select id="findAll" resultMap="roleWithUser">
    select r.*, u.id as uid, name, age from role r left outer join user_role ur on r.id = ur.rid
      left outer join user u on ur.uid = u.id
</select>

最后附上源码：

项目结构：

#MyBatis多表查询 #多对多查询 @FDDLC

pom.xml：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>P076_MyBatis_M2N</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

数据库的相关信息：

MySQL：8.0.16 port：3306 database：multi_table

user表：

#MyBatis多表查询 #多对多查询 @FDDLC

role表：

#MyBatis多表查询 #多对多查询 @FDDLC

user_role表：

#MyBatis多表查询 #多对多查询 @FDDLC

User类：

package cn.liuxingchang.domain;

import java.io.Serializable;
import java.util.List;

public class User implements Serializable {
    private Integer id;
    private String name;
    private Integer age;
    private List<Role> roles;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

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

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

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", roles=" + roles +
                '}';
    }
}

Role类：

package cn.liuxingchang.domain;

import java.io.Serializable;
import java.util.List;

public class Role implements Serializable {
    private Integer id;
    private String role;
    private String info;
    private List<User> users;

    public Integer getId() {
        return id;
    }

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

    public String getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }

    public String getInfo() {
        return info;
    }

    public void setInfo(String info) {
        this.info = info;
    }

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

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

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", role='" + role + '\'' +
                ", info='" + info + '\'' +
                ", users=" + users +
                '}';
    }
}

UserDao接口：

package cn.liuxingchang.dao;

import cn.liuxingchang.domain.User;

import java.util.List;

public interface UserDao {
    List<User> findAll();
}

RoleDao接口：

package cn.liuxingchang.dao;

import cn.liuxingchang.domain.Role;

import java.util.List;

public interface RoleDao {
    List<Role> findAll();
}

UserMapper.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="cn.liuxingchang.dao.UserDao">
    <resultMap id="userWithRole" type="cn.liuxingchang.domain.User">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="age" column="age" />
        <collection property="roles" ofType="cn.liuxingchang.domain.Role">
            <id property="id" column="rid" />
            <result property="role" column="role" />
            <result property="info" column="info" />
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userWithRole">
        select u.*, r.id as rid, role, info from user u left outer join user_role ur on u.id = ur.uid
         left outer join role r on ur.rid = r.id
    </select>
</mapper>

RoleMapper.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="cn.liuxingchang.dao.RoleDao">
    <resultMap id="roleWithUser" type="cn.liuxingchang.domain.Role">
        <id property="id" column="id" />
        <result property="role" column="role" />
        <result property="info" column="info" />
        <collection property="users" ofType="cn.liuxingchang.domain.User">
            <id property="id" column="uid" />
            <result property="name" column="name" />
            <result property="age" column="age" />
        </collection>
    </resultMap>

    <select id="findAll" resultMap="roleWithUser">
        select r.*, u.id as uid, name, age from role r left outer join user_role ur on r.id = ur.rid
          left outer join user u on ur.uid = u.id
    </select>
</mapper>

mybatis.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/multi_table?characterEncoding=UTF8&amp;serverTimezone=Asia/Shanghai"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/UserMapper.xml" />
        <mapper resource="mapper/RoleMapper.xml" />
    </mappers>
</configuration>

Test.java:

import cn.liuxingchang.dao.RoleDao;
import cn.liuxingchang.dao.UserDao;
import cn.liuxingchang.domain.Role;
import cn.liuxingchang.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;

import java.io.InputStream;
import java.util.List;

public class Test {
    private InputStream in;
    private SqlSessionFactory factory;
    private SqlSession sqlSession;
    private UserDao userDao;
    private RoleDao roleDao;

    @Before
    public void before() throws Exception {
        in = Resources.getResourceAsStream("mybatis.xml");
        factory = new SqlSessionFactoryBuilder().build(in);
        sqlSession = factory.openSession();
        userDao = sqlSession.getMapper(UserDao.class);
        roleDao = sqlSession.getMapper(RoleDao.class);
    }

    @After
    public void after() throws Exception {
        sqlSession.commit();
        sqlSession.close();
        in.close();
    }

    @org.junit.Test
    public void findAllUsersTest() {
        List<User> users = userDao.findAll();
        for (User user : users) {
            System.out.println(user);
        }
    }

    @org.junit.Test
    public void findAllRolesTest() {
        List<Role> roles = roleDao.findAll();
        for(Role role: roles) {
            System.out.println(role);
        }
    }
}

#MyBatis多表查询 #多对多查询 @FDDLC

django-blog:多对多查询

[sql]使用自连接查询多对一数据

关于MyBatis 查询数据时属性中多对一的问题(多条数据对应一条数据)

mybatis 多对一关联查询

MySQL 多表关联一对多查询实现取最新一条数据的方法示例

MyBatis一对多嵌套查询的完整实例

Dapper 一对多查询 one to many

Mybatis一对多与多对一查询处理详解

Mybatis多表查询--多对多操作（选课示例）

#MyBatis多表查询 #多对多查询 @FDDLC