多表查询练习（一）

最新推荐文章于 2023-11-16 08:37:55 发布

翎刖

最新推荐文章于 2023-11-16 08:37:55 发布

阅读量133

点赞数

文章标签： java mybatis mysql

本文链接：https://blog.csdn.net/weixin_44434232/article/details/129766443

版权

文章展示了如何在Java中使用MyBatis框架来设计User类和BookHistory类，以及对应的Mapper接口，实现查询所有用户及其借阅记录的功能。User类包含一个BookHistory的List集合，通过@Many注解关联历史记录。BookHistoryMapper接口则用于根据用户ID获取借阅历史。

摘要由CSDN通过智能技术生成

一、查询所有用户，以及用户的借阅记录

创建admin表和history表
admin（用户表）
在这里插入图片描述

User类

package com.nkk.demo_echarts.Bean;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

import java.util.List;

@TableName("admin") //类名和数据表名不一致
public class User {

//     aid是主键，且自增
//     添加数据时，可以自动添加aid，到数据表中
//     如果不加，数据库中aid虽然是自增的，但代码中查询的aid值为0
    @TableId(type = IdType.AUTO)
    private int aid;

//     @TableField(value = "username", exist = false)
    // 如果类里的变量名和数据表字段名不一致，或者数据表中不存在该字段，可以用@TableField注解
    private String username;
    private String password;

    @TableField(exist = false)
    private List<BookHistory> bookHistoryList;

    @Override
    public String toString() {
        return "User{" +
                "aid=" + aid +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", bookHistoryList=" + bookHistoryList +
                '}';
    }

    public List<BookHistory> getBookHistoryList() {
        return bookHistoryList;
    }

    public void setBookHistoryList(List<BookHistory> bookHistoryList) {
        this.bookHistoryList = bookHistoryList;
    }

    public int getAid() {
        return aid;
    }

    public void setAid(int aid) {
        this.aid = aid;
    }

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

在User类中创建了一个List变量，用来存放用户的借阅记录@TableField(exist = false)表明该变量不在数据表admin中

BookHistory类

package com.nkk.demo_echarts.Bean;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

import java.util.Date;

@TableName("history")
public class BookHistory {
    @TableId(type = IdType.AUTO)
    private int hid;

    private String bookname;
    private String card;
    private String username;
    private Date begintime;
    private Date endtime;
    private int aid;

    public int getHid() {
        return hid;
    }

    public void setHid(int hid) {
        this.hid = hid;
    }

    public String getBookname() {
        return bookname;
    }

    public void setBookname(String bookname) {
        this.bookname = bookname;
    }

    public String getCard() {
        return card;
    }

    public void setCard(String card) {
        this.card = card;
    }

    public String getUsername() {
        return username;
    }

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

    public Date getBegintime() {
        return begintime;
    }

    public void setBegintime(Date begintime) {
        this.begintime = begintime;
    }

    public Date getEndtime() {
        return endtime;
    }

    public void setEndtime(Date endtime) {
        this.endtime = endtime;
    }

    public int getAid() {
        return aid;
    }

    public void setAid(int aid) {
        this.aid = aid;
    }

    @Override
    public String toString() {
        return "BookHistory{" +
                "hid=" + hid +
                ", bookname='" + bookname + '\'' +
                ", card='" + card + '\'' +
                ", username='" + username + '\'' +
                ", begintime=" + begintime +
                ", endtime=" + endtime +
                ", aid=" + aid +
                '}';
    }
}

UserMapper
在UserMapper接口中使用MyBatis提供的@Results注解和@Result注解实现对admin表和history表的查询

@Select("select * from admin")
    @Results(
            {
                    @Result(column = "aid", property = "aid"),
                    @Result(column = "username", property = "username"),
                    @Result(column = "password", property = "password"),
                    // 一个用户可以有多条借阅记录，所以用@Many注解
                    // 将aid值传到BookHistoryMapper的selectByAid()方法中，然后再将得到的数据传回到bookHistoryList中
                    @Result(column = "aid", property = "bookHistoryList", javaType = List.class,
                    many = @Many(select = "com.nkk.demo_echarts.mapper.BookHistoryMapper.selectByAid")
                    )
            }

    )
    List<User> selectAllUserAndHistory();

BookHistoryMapper
通过传过来的aid值，查询用户的借阅记录

@Mapper
public interface BookHistoryMapper extends BaseMapper<BookHistory> {
    @Select("select * from history where aid = #{aid}")
    List<BookHistory> selectByAid(int aid);
}

Controller

    //查询所有用户，和用户的借阅记录
    @GetMapping("/user/findAllUserAndBookHistory")
    public List findAllUserAndBookHistory() {
        List<User> userList = userMapper.selectAllUserAndHistory();
        return userList;
    }