CodeVault文档站
  • 后端
  • Java
  • Spring
Yyzop
2022-08-19
目录

Mybatis多表查询

# Mybatis多表查询

# 1.多表查询操作

# 1.1 什么是多表查询

多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

  • 一对多(多对一)
  • 多对多
  • 一对一

# 1.2 多表查询的环境

1.配置数据库的环境

使用多表查询，我们需要设计一个数据库，设计几个表单，分别涉及到单表查询和多表查询的问题，如下是我设计好的数据库以及表单：

DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;

-- 创建用户的表单
DROP TABLE IF EXISTS user;
CREATE TABLE user(
    id int PRIMARY KEY AUTO_INCREMENT NOT NULL COMMENT '用户ID',
    username varchar(50) NOT NULL COMMENT '用户名',
    password varchar(50) NOT NULL COMMENT '密码',
    birthday bigint NOT NULL COMMENT '生日'
) COMMENT '用户表单';

-- 创建订单的表单
DROP TABLE IF EXISTS orders;
CREATE TABLE orders(
    id int PRIMARY KEY AUTO_INCREMENT NOT NULL COMMENT '订单ID',
    ordertime date NOT NULL COMMENT '订单时间',
    total double NOT NULL COMMENT '总共订单数量',
    uuid int NOT NULL COMMENT '用户的ID',
    constraint user_orders foreign key orders(uuid) references user(id)
) COMMENT '订单表';

-- 创建角色的表单
DROP TABLE IF EXISTS role;
CREATE TABLE role(
    id int PRIMARY KEY AUTO_INCREMENT NOT NULL COMMENT '角色ID',
    roleName varchar(50) NOT NULL COMMENT '角色名称',
    roleDesc varchar(100) NOT NULL COMMENT '角色的描述'
    ) COMMENT '角色表';
insert into role values(null,'商家','负责卖商品的商家');
insert into role values(null,'买家','负责买商品的顾客');
insert into role values(null,'管理员','负责商品的管理问题');

-- 创建用户和角色的中间表
DROP TABLE IF EXISTS user_role;
CREATE TABLE user_role(
    userId int NOT NULL COMMENT '用户id',
    roleId int NOT NULL COMMENT '角色id',
    constraint user_userId foreign key (userId) references user(id),
    constraint role_roleId foreign key (roleId) references role(id)
) COMMENT '中间表';

insert into user_role values(1,2);
insert into user_role values(2,1);
insert into user_role values(3,1);
insert into user_role values(4,2);
insert into user_role values(5,2);

image-20220730150122609

2.配置测试环境

测试环境的准备如下：

  • 在测试环境我们需要创建好三个实体类分别是如上的Role、User、Order。
  • 导入相关的坐标以及配置好log4和jdbc的properties配置文件
  • 需要创建sqlMapConfig.xml和UserMapper.xml以及OrderMapper.xml的数据库配置文件
  • 使用接口代理的方式去调用对数据库的增删改查
  • 添加Date类型转换

1.三个实体类

public class User {
    private Integer id;
    private String username;
    private String password;
    private Date birthday;
    ...
}

public class Role {
    private Integer id;
    private String roleName;
    private String roleDesc;
	...
}

public class Order {
    private Integer id;
    private Date ordertime;
    private double total;
    ...
}

2.两个接口的代理配置

public interface UserMapper {
}

public interface OrderMapper {
}

3.jdbc的数据库连接配置信息省略，如下是log4j的

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=c:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### set log levels - for more verbose logging change 'info' to 'debug' ###

log4j.rootLogger=debug, stdout

4.sqlMapConfig的核心配置文件

<?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>
    <!--配置引入数据源-->
    <properties resource="jdbc.properties"/>

    <!--配置别名-->
    <typeAliases>
        <typeAlias type="com.example.domain.User" alias="user"></typeAlias>
        <typeAlias type="com.example.domain.Order" alias="order"></typeAlias>
        <typeAlias type="com.example.domain.Role" alias="role"></typeAlias>
    </typeAliases>
    
    <!--注册类型处理器-->
    <typeHandlers>
        <typeHandler handler="com.example.handler.DateTypeHandler"></typeHandler>
    </typeHandlers>


    <!--配置数据源-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--引入映射文件-->
    <mappers>
        <mapper resource="com/example/mapper/userMapper.xml"/>
        <mapper resource="com/example/mapper/OrderMapper.xml"/>
    </mappers>
</configuration>

5.配置BaseTypeHandler用来将java类型转换数据库类型。

public class DateTypeHandler extends BaseTypeHandler<Date> {
    // 将java类型转换成数据库需要的类型
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        long time = date.getTime();
        preparedStatement.setLong(i,time);
    }

    // 将数据库中的类型 转换为java类型
    // string 要转换的字符串
    // resultSet 查询出的结果
    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        // 获得结果中需要的数据(long) 转换为Date类型
        long along = resultSet.getLong(s);
        Date date = new Date(along);
        return  date;

    }

    // 将数据库中的类型 转换为java类型
    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        long along = resultSet.getLong(i);
        Date date = new Date(along);
        return  date;
    }

    // 将数据库中的类型 转换为java类型
    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        long aLong = callableStatement.getLong(i);
        Date date = new Date(aLong);
        return date;
    }
}

# 1.3 一对一查询

一对一查询的模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

image-20220730150236442

# 1.3.1 一对一的查询语句

对应的sql语句：select *,o.id oid from user u,orders o where u.id=o.uuid;

查询的结果如下：

mysql> select *,o.id oid from  user u,orders o where u.id=o.uuid;
+----+----------+----------+---------------+----+-----------+-------+------+-----+
| id | username | password | birthday      | id | ordertime | total | uuid | oid |
+----+----------+----------+---------------+----+-----------+-------+------+-----+
|  1 | zhangsan | 000000   |          NULL |  1 |  20190211 |  2500 |    1 |   1 |
|  1 | zhangsan | 000000   |          NULL |  2 |  20190215 |  3000 |    1 |   2 |
|  2 | lisi     | 123456   |          NULL |  3 |  20200411 |   200 |    2 |   3 |
|  4 | test3    | 000000   |          NULL |  4 |  20210411 |   200 |    4 |   4 |
|  3 | test1    | 111111   | 1659149215839 |  5 |  20180411 |  1400 |    3 |   5 |
+----+----------+----------+---------------+----+-----------+-------+------+-----+
5 rows in set (0.03 sec)

# 1.3.2 创建Order的User实体

在Order中创建User的实体，记得重写toString的方法。

public class Order {
    private Integer id;
    private Date ordertime;
    private double total;

    // 当前订单输入哪一个用户
    private User user;
    ...
}

# 1.3.3 OrderMapper配置

1.创建OrderMapper的接口

public interface OrderMapper {

    public List<Order> findAll();
}

2.配置OrderMapper.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.example.dao.OrderMapper">
    <resultMap id="orderMap" type="order">
        <!--手动去指定字段与实体属性的映射关系-->
        <!--
            column: 数据表的字段名称
            property: 实体属性的名称
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        
        <!--这里是resultMap的第一种配置方法-->
        <!--<result column="uuid" property="user.id"></result>
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>-->

        <!--
            property: 当前实体(order)中的属性名称(private User user)
            javaType: 当前实体(order)中的属性类型(User)
        -->
        <association property="user" javaType="user">
            <id column="uuid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
            <result column="birthday" property="birthday"></result>
        </association>
    </resultMap>

    <!--数据库的一对一查询-->
    <select id="findAll" resultMap="orderMap">
        select *,o.id oid  from orders o, user u where o.uuid = u.id
    </select>

</mapper>

# 1.3.4 测试结果

@Test
public void test4() throws IOException {
    SqlSession sqlSession = getSqlSession();   //提取的方法
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    List<Order> userList = orderMapper.findAll();
    for (Order order : userList) {
        System.out.println(order);
    }
    sqlSession.close();
}

image-20220730152740774

# 1.4 一对多查询

一对多查询模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单

image-20220730205035399

# 1.4.1 一对多查询语句

对应的sql语句： select *,o.id oid from user u,orders o where u.id=o.uuid;

查询结果如下：

mysql> select *,o.id oid from  user u,orders o where u.id=o.uuid;
+----+----------+----------+---------------+----+-----------+-------+------+-----+
| id | username | password | birthday      | id | ordertime | total | uuid | oid |
+----+----------+----------+---------------+----+-----------+-------+------+-----+
|  1 | zhangsan | 000000   |          NULL |  1 |  20190211 |  2500 |    1 |   1 |
|  1 | zhangsan | 000000   |          NULL |  2 |  20190215 |  3000 |    1 |   2 |
|  2 | lisi     | 123456   |          NULL |  3 |  20200411 |   200 |    2 |   3 |
|  4 | test3    | 000000   |          NULL |  4 |  20210411 |   200 |    4 |   4 |
|  3 | test1    | 111111   | 1659149215839 |  5 |  20180411 |  1400 |    3 |   5 |
+----+----------+----------+---------------+----+-----------+-------+------+-----+
5 rows in set (0.00 sec)

# 1.4.2 修改User实体

需要在User的实体类，添加Order的实体类，重写toString的方法。

public class User {
    private Integer id;
    private String username;
    private String password;
    private Date birthday;

    // 描述是当前用户存在哪些订单
    private List<Order> orderList;
    ...
}

# 1.4.3 UserMapper配置

1.创建UserMapper接口

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

2.配置UserMaper.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.example.dao.UserMapper">
    <resultMap id="userMap" type="user">
        <id column="uid" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>

        <!--配置集合信息
            property: 集合名称
            ofType: 当前集合中的数据类型
        -->
        <collection property="orderList" ofType="order">
            <id column="oid" property="id"></id>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>

	<!--数据库一对多的查询-->
    <select id="findAll" resultMap="userMap">
        select *,o.id oid from  user u,orders o where u.id=o.uuid
    </select>

# 1.4.4 测试结果

	@Test
    public void test5() throws IOException {
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = userMapper.findAll();
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();

    }

image-20220730153834287

# 1.5 多对多查询

多对多查询的模型

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用

多对多查询的需求：查询用户同时查询出该用户的所有角色

image-20220730154003218

# 1.5.1 多对多查询的语句

对应的sql语句： select * from user u,user_role ur,role r where u.id=ur.userId and r.id = ur.roleId;

查询结果如下

mysql> select * from user u,user_role ur,role r where u.id=ur.userId and r.id = ur.roleId;
+----+----------+----------+---------------+--------+--------+----+----------+--------------------------+
| id | username | password | birthday      | userId | roleId | id | roleName | roleDesc                 |
+----+----------+----------+---------------+--------+--------+----+----------+--------------------------+
|  1 | zhangsan | 000000   |          NULL |      1 |      2 |  2 | 买家     | 负责买商品的顾客         |
|  2 | lisi     | 123456   |          NULL |      2 |      1 |  1 | 商家     | 负责卖商品的商家         |
|  3 | test1    | 111111   | 1659149215839 |      3 |      1 |  1 | 商家     | 负责卖商品的商家         |
|  4 | test3    | 000000   |          NULL |      4 |      2 |  2 | 买家     | 负责买商品的顾客         |
|  5 | test4    | 000000   |          NULL |      5 |      2 |  2 | 买家     | 负责买商品的顾客         |
+----+----------+----------+---------------+--------+--------+----+----------+--------------------------+
5 rows in set (0.01 sec)

# 1.5.2 修改Role和User实体

Role的实体创建好了，不需要修改只需要修改User的实体，重写toString

public class User {
    private Integer id;
    private String username;
    private String password;
    private Date birthday;

    // 描述是当前用户存在哪些订单
    private List<Order> orderList;
    
    // 描述的是当前用户存在哪些角色
    private List<Role> roleList;
	...
}

# 1.5.3 UserMapper配置

1.添加方法到UserMapper接口

public interface UserMapper {
    public void save(User user);

    public List<User> findAll();

    public List<User> findUserAndRoleAll();

}

2.配置UserMapper.xml配置文件

	 <resultMap id="userRoleMap" type="user">
        <!--user的信息-->
        <id column="userId" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <!--user内部的roleList信息-->
        <collection property="roleList" ofType="role">
            <id column="roleId" property="id"></id>
            <result column="roleName" property="roleName"></result>
            <result column="roleDesc" property="roleDesc"></result>
        </collection>
    </resultMap>

    <select id="findUserAndRoleAll" resultMap="userRoleMap">
        select * from user u,user_role ur,role r where u.id=ur.userId and r.id = ur.roleId
    </select>

# 1.5.4 测试代码

	@Test
    public void test6() throws IOException {
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userAndRolAll = userMapper.findUserAndRoleAll();
        for (User user : userAndRolAll) {
            System.out.println(user);
        }
        sqlSession.close();
    }

image-20220730155123871

上次更新: 2023/11/28, 22:03:59
Mybatis入门
Mybatis注解开发

← Mybatis注解开发→

最近更新
01
学习文档
11-28
02
如何成为FISCO社区的MVP 总结篇
11-28
03
一文搞懂Truffle测试
11-28
更多文章>
Theme by Vdoing | Copyright © 2023-2023 YYzop & Hior | MIT License