多表查询练习（二）

二、查询所有借阅记录以及每条借阅记录的所属用户

BookHistory
在BookHistory中添加了user变量，用来存放某条查阅记录的用户信息（一对一）

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;

    private User user;

    public User getUser() {
        return user;
    }

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

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

    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;
    }

}

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;
    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;
    }

}

BookHistoryMapper

 @Select("select * from history")
    @Results(
            {
                    @Result(column = "hid", property = "hid"),
                    @Result(column = "bookname", property = "bookname"),
                    @Result(column = "card", property = "card"),
                    @Result(column = "username", property = "username"),
                    @Result(column = "begintime", property = "begintime"),
                    @Result(column = "endtime", property = "endtime"),
                    // 将aid传到UserMapper.selectByAid()方法，查询这条借阅记录的借阅者，再将数据返回到user，一对一用@One注解
                    @Result(column = "aid", property = "user", javaType = User.class,
                    one = @One(select = "com.nkk.demo_echarts.mapper.UserMapper.selectByAid")),
            }
    )
    List<BookHistory> selectAllBookHistoryAndUser();

UserMapper

   @Select("select * from admin where aid = #{aid}")
    List<User> selectByAid(int aid);

Controller

   //查询借阅记录以及每条借阅记录的所属用户
    @GetMapping("/user/findAllBookHistoryAndUser")
    public List findAllBookHistoryAndUser() {
        List<BookHistory> bookHistory = bookHistoryMapper.selectAllBookHistoryAndUser();
        return bookHistory;
    }

运行结果