Mybatis多表查询

缺乏、安全感 2024-03-24 13:45 49阅读 0赞

## 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">
            
            
            <id column="oid" property="id"></id>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
            
            
            
    
            
            <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;`

查询结果如下：

##### 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>
    
            
            <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">
            
            <id column="userId" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
            <result column="birthday" property="birthday"></result>
            
            <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();
        }

tMap>

<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 测试代码
    
    ```java
    	@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][]

[image-20220730150122609]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/3250559adb6847a09d5cb5a8f1ad4fee.png
[image-20220730150236442]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/e85709088757468da788583e12e9101c.png
[image-20220730152740774]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/d80553ec64164dfb98495f0881ce7002.png
[image-20220730205035399]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/7be604e757f54dbbbac95acc4c47239e.png
[image-20220730153834287]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/319a1fec9333441eb3d3ee5fad6c70d4.png
[image-20220730154003218]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/11257b2d6cc74fff99ff5682f862022e.png
[image-20220730155123871]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/095e71b87d8b4afe927434f24e097e63.png