MyBaits复杂查询（一对多）

随便1007

于 2024-02-21 18:17:04 发布

阅读量443

点赞数 16

文章标签： vue.js maven spring boot 数据库

本文链接：https://blog.youkuaiyun.com/fyr6103032003/article/details/136217727

版权

本文介绍了如何在SpringBoot项目中创建数据库表，执行SQL查询，并通过结果映射（resultMap和collection）实现用户和他们借阅的书籍的复合查询，展示了实体类的定义以及接口和配置文件的编写过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

resultMap标签和collection标签

7.测试查询结果

1.新建数据库

建表时，要较少两个表耦合度，用id将两个表联系起来

create database if not exists my_Sql;
use my_Sql;
create table if not exists mytable
(
    userID   int primary key auto_increment not null,
    clazz    varchar(20),
    email    varchar(20),
    username varchar(20)                    not null,
    password varchar(20)                    not null,
    type     varchar(10) default 'guest'    not null
);

insert into mytable value
    (1, '2201', '34518868659', 'sean', '123456', 'admin'),
    (2, '2202', '34518868659', '李四', '123456', 'guest'),
    (3, '2201', '34518868659', '王五', '123456', 'guest');

create table if not exists mybook
(
    bookID   int primary key not null,
    name     varchar(20) comment '书名',
    img      varchar(30) comment '图书照片',
    press    varchar(30)     not null comment '出版社',
    username varchar(50)     not null comment '作者',
    number   int             not null comment '库存',
    tid      int             not null comment '借书人'
);

insert into mybook
values (1, '西游记', '', '北京出版社', '张三', 8, 1),
       (2, '水浒传', '', '北京出版社', '张三', 3, 3),
       (3, '三国志', '', '北京出版社', '张三', 4, 2),
       (4, 'Java', '', '北京出版社', '张三', 2, 1),
       (5, 'python', '', '北京出版社', '张三', 5, 3);

2.通过数据库语言查询数据

通过数据库的左连接查询，查到所有数据，但无法达成页面显示一对多的效果，所以，为了做成更直观的显示，进行以下步骤

3.新建实体类

@Data
public class BookEntity {
   private Integer bookID ;
   private String name;
   private String img;
   private String press;
   private String username;
   private Integer number;
   private Integer tid;
}

@Data
public class UserEntity {
    private Integer userID;
    private String username;
    private String clazz;
    private String email;
    private String password;
    private String type;
    private List<BookEntity> getBookList;
}

4.新建v层

@Autowired  //从Spring中取出
    UserMapper userMapper;
 
    //复合查询
    @RequestMapping("/getUserAndBook")
    public ResultMap<List<UserEntity>> getUserAndBook() {
        ResultMap<List<UserEntity>> resultMap = new ResultMap<>();
        List<UserEntity> userList = userMapper.getUserAndBook();
        resultMap.setCode(200);
        resultMap.setMsg("查询成功");
        resultMap.setResult(userList);
        return resultMap;
    }

5.新建接口

@Mapper  //存进Spring容器中
public interface UserMapper {
//复合查询
    public List<UserEntity> getUserAndBook();
}

6.新建配置文件

需要在配置文件中写明查询条件，先查询到所需要的数据

<!--复合查询-->
    <!-- 由于返回的实体类需要进入复合类中处理数据所以要用 resultMap(结果映射)-->
    <select id="getUserAndBook" resultMap="getUserMap">
        select t.userID,
               t.username,
               t.clazz,
               t.email,
               t.password,
               t.type,
               m.bookID,
               m.name,
               m.img,
               m.press,
               m.username,
               m.press,
               m.number,
               m.tid
        from mytable t
                 left join mybook m on t.userID = m.tid;
    </select>

resultMap标签和collection标签

在标签内写映射部分

<resultMap id="getUserMap" type="UserEntity">
        <!-- 先处理一的部分 -->
        <!--先处理主键-->
        <!-- 主键用id 属性用result-->
        <!-- column 是指sql 查询的列的名字-->
        <id column="userID" property="userID"></id>
        <result column="username" property="username"></result>
        <result column="clazz" property="clazz"></result>
        <result column="email" property="email"></result>
        <result column="password" property="password"></result>
        <result column="type" property="type"></result>
        <!-- 处理多的部分-->
        <!-- <collection 表示集合 property="commentEntityList"
        指的是 在复合类中的名字 ofType="CommentEntity" 自身的类型 >-->
        <collection property="getBookList" ofType="BookEntity">
            <id column="bookID" property="bookID"></id>
            <result column="name" property="name"></result>
            <result column="img" property="img"></result>
            <result column="press" property="press"></result>
            <result column="number" property="number"></result>
            <result column="username" property="username"></result>
            <result column="tid" property="tid"></result>
        </collection>
    </resultMap>

7.测试查询结果

三个用户，每个用户借多本书，显示出想要的效果

{
    "code": 200,
    "msg": "查询成功",
    "result": [
        {
            "userID": 1,
            "username": "sean",
            "clazz": "2201",
            "email": "34518868659",
            "password": "123456",
            "type": "admin",
            "getBookList": [
                {
                    "bookID": 1,
                    "name": "西游记",
                    "img": "",
                    "press": "北京出版社",
                    "username": "sean",
                    "number": 8,
                    "tid": 1
                },
                {
                    "bookID": 4,
                    "name": "Java",
                    "img": "",
                    "press": "北京出版社",
                    "username": "sean",
                    "number": 2,
                    "tid": 1
                }
            ]
        },
        {
            "userID": 3,
            "username": "王五",
            "clazz": "2201",
            "email": "34518868659",
            "password": "123456",
            "type": "guest",
            "getBookList": [
                {
                    "bookID": 2,
                    "name": "水浒传",
                    "img": "",
                    "press": "北京出版社",
                    "username": "王五",
                    "number": 3,
                    "tid": 3
                },
                {
                    "bookID": 5,
                    "name": "python",
                    "img": "",
                    "press": "北京出版社",
                    "username": "王五",
                    "number": 5,
                    "tid": 3
                }
            ]
        },
        {
            "userID": 2,
            "username": "李四",
            "clazz": "2202",
            "email": "34518868659",
            "password": "123456",
            "type": "guest",
            "getBookList": [
                {
                    "bookID": 3,
                    "name": "三国志",
                    "img": "",
                    "press": "北京出版社",
                    "username": "李四",
                    "number": 4,
                    "tid": 2
                }
            ]
        }
    ]
}