mybatis进阶案例之多表查询

程序员文章站 2022-05-24 10:26:29

...

mybatis进阶案例之多表查询

一、mybatis中表之间的关系

在数据库中，实体型之间的关系主要有如下几种：

1、一对一

如果对于实体集A中的每一个实体，实体集B中至多有一个（也可以没有）实体与之联系，反之亦然，则称实体集A与实体集B具有一对一联系，记为1:1 。例如，一个班级只有一个正班长，一个班长只在一个班中任职。

2、一对多

如果对于实体集A中的每一个实体，实体集B中有n个实体（n≥0）与之联系，反之，对于实体集B中的每一个实体，实体集A中至多只有一个实体与之联系，则称实体集A与实体集B有一对多联系，记为1:n。例如，一个班级中有若干名学生，每个学生只在一个班级中学习。

3、多对多

如果对于实体集A中的每一个实体，实体集B中有n个实体（n≥0）与之联系，反之，对于实体集B中的每一个实体，实体集A中也有m个实体（m≥0）与之联系，则称实体集A与实体B具有多对多联系，记为m:n。例如，一门课程同时有若干个学生选修，一个学生可以同时选修多门课程。

二、mybatis一对一查询

1.在数据库中新建account表

DROP TABLE IF EXISTS `account`;

CREATE TABLE `account` (
  `ID` int(11) NOT NULL COMMENT '编号',
  `UID` int(11) default NULL COMMENT '用户编号',
  `MONEY` double default NULL COMMENT '金额',
  PRIMARY KEY  (`ID`),
  KEY `FK_Reference_8` (`UID`),
  CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `account`(`ID`,`UID`,`MONEY`) values (1,46,1000),(2,45,1000),(3,46,2000);

2.在入门案例的基础上，新建maven工程

1.在domain包下新建实体类Account，注意implements Serializable接口（之前的代码都没注意要实现序列化接口，具体可以参考谈谈序列化—实体bean一定要实现Serializable接口？）。

public class Account implements Serializable {

    private Integer id;
    private Integer uid;
    private Double money;

    public Integer getId() {
        return id;
    }

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

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                '}';
    }
}

2.创建dao接口

在dao包下新建DAO接口IAccountDao，如下：

public interface IAccountDao {

    /**
     * 查询所有账户
     * @return
     */
    List<Account> findAll();
}

3.配置映射配置文件

在IUserDao.xml的同级目录下新建文件IAccountDao.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="dao.IAccountDao">
    <!-- 配置查询所有 -->
    <select id="findAll" resultType="domain.Account">
        select * from account;
    </select>
</mapper>

4.在主配置文件中指定映射配置文件

在主配置文件SqlMapConfig.xml中指定IAccountDao.xml的位置，如下：

<!-- 配置映射文件的位置 -->
<mappers>
    <mapper resource="com/whu/cs/dao/IUserDao.xml"></mapper>
    <mapper resource="com/whu/cs/dao/IAccountDao.xml"></mapper>
</mappers>

5.添加测试函数

在MybatisTest类的同级目录下创建测试类AccountTest，如下：

public class AccountTest {

    private InputStream in;
    private SqlSession sqlSession;
    private IAccountDao accountDao;


    @Before//用于在测试方法执行之前运行
    public void init() throws IOException {
        //1.读取配置文件，生成字节输入流
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.获取SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //3.获取SqlSession对象
        sqlSession = factory.openSession();
        //4.获取dao的代理对象
        accountDao = sqlSession.getMapper(IAccountDao.class);
    }

    @After//用于在测试方法执行之后运行
    public void close() throws IOException {
        //5.提交事务
        sqlSession.commit();
        //6.释放资源
        sqlSession.close();
        in.close();
    }

    /**
     * 测试查询所有
     */
    @Test
    public void testFindAll() throws IOException {
        //执行查询所有方法
        List<Account> accounts = accountDao.findAll();
        for (Account account : accounts) {
            System.out.println(account);
        }
    }
}

6.运行测试函数

这个运行只是查询account表单独的信息，还只是单表查询，并非多表查询。结果如下：

Account{id=1, uid=41, money=1000.0}
Account{id=2, uid=45, money=1000.0}
Account{id=3, uid=41, money=2000.0}

3.开始一对一多表查询

如果我们想查询和一个人相关的个人信息和账户信息，就需要同时对account表和user表进行查询。一种可行的思路是：新建一个类，这个类包括一个user对象和account对象，然后将查询结果封装到这个对象中。这里我们让这个类继承自Account类，然后增加username和address两个属性。

1.在domain包下新建AccountUser类，如下:

public class AccountUser extends Account {

    private String username;
    private String address;

    public String getUsername() {
        return username;
    }

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

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return super.toString() +
                "   AccountUser{" +
                "username='" + username + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

2.在IAccountDao接口中，添加查询方法

/**
 * 查询账户和用户信息
 * @return
 */
List<AccountUser> findUserAndAccount();

3.配置IAccountDao.xml

<!-- 配置查询用户和账户信息 -->
<select id="findUserAndAccount" resultType="domain.AccountUser">
    SELECT  user.username, user.address, account.* from user, account WHERE user.id = account.UID
</select>

4.在AccountTest类中测试

/**
 * 测试查询账户和用户信息
 * @throws IOException
 */
@Test
public void testFindUserAndAccount() throws IOException {
    List<AccountUser> ausers = accountDao.findUserAndAccount();
    for (AccountUser auser : ausers
    ) {
        System.out.println(auser);
    }
}

结果如下：
Account{id=1, uid=41, money=1000.0}   AccountUser{username='老王', address='北京'}
Account{id=2, uid=45, money=1000.0}   AccountUser{username='传智播客', address='北京金燕龙'}
Account{id=3, uid=41, money=2000.0}   AccountUser{username='老王', address='北京'}

不过上述代码维护起来比较困难，因为继承会造成高耦合。一旦Account类修改，AccountUser类也要修改。因此我们还有一种方式来实现一对一查询。

在第二种方法中，我们通过标签来指定主从表的映射关系，并且在从表实体类中添加主表实体类的引用。

1.添加实体类引用

在domain包下的Account类中，添加如下代码

    //从表实体应该包含主表实体的引用
    private User user;

    public User getUser() {
        return user;
    }

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

2.添加查询方法

在dao包下的IAccountDao接口中添加对应的查询方法：

/**
 * 查询带有用户信息的账户
 * @return
 */
List<Account> findAllAccountsWithUser();

3.配置查询结果和实体类属性的对应关系

在resources下的映射配置文件IAccountDao.xml文件中添加如下代码，其中为了解决Account类中id和User类中id重名的问题，我们在写sql语句时采取了对列名起别名的方式，将account.id重名为aid，具体可以参考MyBatis两张表字段名相同产生的问题。

<!-- 建立实体类的对应关系,id属性是resultMap的名称下, type是从表实体类型-->
<resultMap id="accountUserMap" type="domain.Account">
  <!-- column是查询结果的列名, property是对应实体类中的属性名 -->
    <id column="aid" property="id"/>
    <result column="uid" property="uid"/>
    <result column="money" property="money"/>
    <!-- association是用于指定从表方的引用实体属性的 -->
 		<!-- property是用于指定从表实体对主表实体引用的名称, javaType是主表实体类型 -->
    <association property="user" javaType="domain.User">
      <!-- column是查询结果的列名, property是对应实体类中的属性名 -->
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
        <result column="address" property="address"/>
    </association>
</resultMap>

4.配置查询函数

在resources下的映射配置文件IAccountDao.xml文件中添加如下代码:

<!-- 配置查询带有用户信息的账户信息 -->
<select id="findAllAccountsWithUser" resultMap="accountUserMap">
    select user.*, account.id aid, account.uid, account.money from user, account WHERE user.id = account.UID
</select>

5.进行测试

在test目录下的AccountTest类中，添加测试方法：

/**
 * 测试查询带有用户信息的账户信息
 * @throws IOException
 */
@Test
public void testFindAllAccountsWithUser() throws IOException {
    List<Account> accounts = accountDao.findAllAccountsWithUser();
    for (Account account : accounts
    ) {
        System.out.println(account);
        System.out.println(account.getUser());
    }
}

结果如下：
Account{id=1, uid=41, money=1000.0}
User{id=41, username='老王', address='北京', sex='男', birthday=Wed Feb 28 07:47:08 CST 2018}
Account{id=2, uid=45, money=1000.0}
User{id=45, username='传智播客', address='北京金燕龙', sex='男', birthday=Mon Mar 05 02:04:06 CST 2018}
Account{id=3, uid=41, money=2000.0}
User{id=41, username='老王', address='北京', sex='男', birthday=Wed Feb 28 07:47:08 CST 2018}

三、mybatis一对多查询

一对多查询和多对多查询的步骤非常类似，具体如下：

1.在主表实体中添加从表实体的引用

在domain包下的User类中添加如下代码，由于是一对多的关系，所以添加的是列表引用：

private List<Account> accounts;

public List<Account> getAccounts() {
    return accounts;
}

public void setAccounts(List<Account> accounts) {
    this.accounts = accounts;
}

2.添加查询方法

在dao包下的IUserDao接口中添加对应的查询方法：

/**
 * 查询带有账户信息的用户信息
 * @return
 */
List<User> findUserWithAccounts();

3.配置对应关系

在resources下的映射配置文件IUserDao.xml文件中添加如下代码，同样需要解决重名问题：

<resultMap type="domain.User" id="userAccountsMap">
    <id column="id" property="id"></id>
    <result column="username" property="username"/>
    <result column="address" property="address"/> <result column="sex" property="sex"/>
    <result column="birthday" property="birthday"/>
    <!-- collection是用于建立一对多中集合属性的对应关系 -->
    <!-- property是用于指定主表实体对从表实体引用的名称, ofType 用于指定集合元素的数据类型 -->
    <collection property="accounts" ofType="domain.Account">
        <id column="aid" property="id"/>
        <result column="uid" property="uid"/>
        <result column="money" property="money"/>
    </collection>
</resultMap>

4.配置查询方法

在resources下的映射配置文件IUserDao.xml文件中添加如下代码:

<!-- 配置查询带有账户信息的用户信息 -->
<select id="findUserWithAccounts" resultMap="userAccountsMap">
     select user.*, account.id aid, account.uid, account.money from user left outer join account on user.id = account.uid
</select>

5.测试

在test目录下的MybatisTest类中，添加测试方法：

@Test
public void testFindUserWithAccounts() {
    //6.执行操作
    List<User> users = userDao.findUserWithAccounts();
    for(User user : users) {
        System.out.println("-------每个用户的内容---------");
        System.out.println(user);
        System.out.println(user.getAccounts());
    }
}

查询结果如下：

mybatis进阶案例之多表查询

四、mybatis多对多查询

1.在数据库中建立角色表role和关联表user_role

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (
  `ID` int(11) NOT NULL COMMENT '编号',
  `ROLE_NAME` varchar(30) default NULL COMMENT '角色名称',
  `ROLE_DESC` varchar(60) default NULL COMMENT '角色描述',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `role`(`ID`,`ROLE_NAME`,`ROLE_DESC`) values (1,'院长','管理整个学院'),(2,'总裁','管理整个公司'),(3,'校长','管理整个学校');

DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role` (
  `UID` int(11) NOT NULL COMMENT '用户编号',
  `RID` int(11) NOT NULL COMMENT '角色编号',
  PRIMARY KEY  (`UID`,`RID`),
  KEY `FK_Reference_10` (`RID`),
  CONSTRAINT `FK_Reference_10` FOREIGN KEY (`RID`) REFERENCES `role` (`ID`),
  CONSTRAINT `FK_Reference_9` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `user_role`(`UID`,`RID`) values (41,1),(45,1),(41,2);

2.建立实体类Role

在domain包下新建实体类Role，注意在该类中添加另一实体类的列表引用，代码如下：

public class Role implements Serializable {
    private Integer roleId;
    private String roleName;
    private String roleDesc;
  	//对user对象集合的引用
    private List<User> users;

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

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

    public Integer getRoleId() {
        return roleId;
    }

    public void setRoleId(Integer roleId) {
        this.roleId = roleId;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    @Override
    public String toString() {
        return "Role{" +
                "roleId=" + roleId +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }
}

3.新建接口文件

在dao包下新建接口IRoleDao，代码如下：

public interface IRoleDao {
    /**
     * 查询所有带有用户信息的角色信息
     * @return
     */
    List<Role> findRolesWithUsers();
}

4.编辑配置文件

在resources目录下新建映射配置文件IRoleDao.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.whu.cs.dao.IRoleDao">
    <!--定义 role 表的 ResultMap-->
    <resultMap id="roleUserMap" type="domain.Role">
        <id property="roleId" column="rid"></id>
        <result property="roleName" column="role_name"></result>
        <result property="roleDesc" column="role_desc"></result>
        <collection property="users" ofType="domain.User">
            <id column="id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="address" property="address"></result>
            <result column="sex" property="sex"></result>
            <result column="birthday" property="birthday"></result>
        </collection>
    </resultMap>

    <!-- 配置查询带有用户信息的角色信息 -->
    <select id="findRolesWithUsers" resultMap="roleUserMap">
        select user.*, role.id as rid, role.role_name, role.role_desc
        from role left outer join user_role on role.id = user_role.rid
        left outer join user on user.id = user_role.uid
    </select>
</mapper>

由于是根据角色信息查询带有用户信息，所以查询的sql语句中，连接的顺序是role left join user_role left join user。不能更改顺序。

别忘了在主配置文件SqlMapConfig.xml文件中的mappers标签中导入：

<mapper resource="com/whu/cs/dao/IRoleDao.xml"></mapper>

5.测试

在test目录下新建测试类RoleTest，代码如下：

public class RoleTest {
    private InputStream in;
    private SqlSession sqlSession;
    private IRoleDao roleDao;


    @Before//用于在测试方法执行之前运行
    public void init() throws IOException {
        //1.读取配置文件，生成字节输入流
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.获取SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //3.获取SqlSession对象
        sqlSession = factory.openSession();
        //4.获取dao的代理对象
        roleDao = sqlSession.getMapper(IRoleDao.class);
    }

    @After//用于在测试方法执行之后运行
    public void close() throws IOException {
        //5.提交事务
        sqlSession.commit();
        //6.释放资源
        sqlSession.close();
        in.close();
    }

    /**
     * 测试查询所有
     */
    @Test
    public void testFindRolesWithUsers(){
        List<Role> roles = roleDao.findRolesWithUsers();
        for(Role role : roles){
            System.out.println("---每个角色的信息----");
            System.out.println(role);
            System.out.println(role.getUsers());
        }
    }
}

查询结果如下：

mybatis进阶案例之多表查询

mybatis进阶案例之多表查询

mybatis进阶案例之多表查询

一、mybatis中表之间的关系

二、mybatis一对一查询

1.在数据库中新建account表

2.在入门案例的基础上，新建maven工程

3.开始一对一多表查询

1.添加实体类引用

2.添加查询方法

3.配置查询结果和实体类属性的对应关系

4.配置查询函数

5.进行测试

三、mybatis一对多查询

1.在主表实体中添加从表实体的引用

2.添加查询方法

3.配置对应关系

4.配置查询方法

5.测试

四、mybatis多对多查询

1.在数据库中建立角色表role和关联表user_role

2.建立实体类Role

3.新建接口文件

4.编辑配置文件

5.测试

ASP编程入门进阶（二十）：ADO组件之查询数据记录

mybatis查询语句的背后之参数解析

python 之数据库（多表查询之连接查询、子查询、pymysql模块的使用）

数据库知识学习之SQL语句多表联合查询

Oracle数据库之七多表查询

MyBatis之自查询使用递归实现 N级联动效果(两种实现方式)

Mybatis基于注解实现多表查询功能

MyBatis从入门到精通(三)：MyBatis XML方式的基本用法之多表查询

oracle—SQL技巧之(一)连续记录查询sql案例测试

mybatis Plus 多表联合查询

mybatis进阶案例之多表查询

mybatis进阶案例之多表查询

一、mybatis中表之间的关系

二、mybatis一对一查询

1.在数据库中新建account表

2.在入门案例的基础上，新建maven工程

3.开始一对一多表查询

1.添加实体类引用

2.添加查询方法

3.配置查询结果和实体类属性的对应关系

4.配置查询函数

5.进行测试

三、mybatis一对多查询

1.在主表实体中添加从表实体的引用

2.添加查询方法

3.配置对应关系

4.配置查询方法

5.测试

四、mybatis多对多查询

1.在数据库中建立角色表role和关联表user_role

2.建立实体类Role

3.新建接口文件

4.编辑配置文件

5.测试

ASP编程入门进阶（二十）：ADO组件之查询数据记录

mybatis查询语句的背后之参数解析

python 之 数据库（多表查询之连接查询、子查询、pymysql模块的使用）

数据库知识学习之SQL语句多表联合查询

Oracle数据库之七 多表查询

MyBatis之自查询使用递归实现 N级联动效果(两种实现方式)

Mybatis基于注解实现多表查询功能

MyBatis从入门到精通(三)：MyBatis XML方式的基本用法之多表查询

oracle—SQL技巧之(一)连续记录查询sql案例测试

mybatis Plus 多表联合查询

python 之数据库（多表查询之连接查询、子查询、pymysql模块的使用）

Oracle数据库之七多表查询