Mybatis的多表查询

背景

mybatis-plus可以帮助我们生成代码，但是当涉及到多表查询时候，还是需要自己写sql

多表查询，可以分为两类

基于xml文件进行配置
基于注解进行配置

场景

用户表（user）:

create table user(
	id int(11) not null auto_increment,
	username varchar(32) not null comment '用户名称',
	birthday datetime default null comment '生日',
	sex char(1) default null comment '性别',
	address varchar(256) default null comment '地址',
	primary key (id)
)engine=innoDB default charset=utf8;

账户表（account）:

DROp TABLE IF EXISTS ACCOUNT;
CREATE TABLE ACCOUNT(
	id INT(11) NOT NULL COMMENT '编号',
	uid INT(11) DEFAULT NULL COMMENT '用户编号',
	money DOUBLE DEFAULT NULL COMMENT '金额',
	PRIMARY KEY (id),
	KEY FK_Reference_8 (uid),
	CONSTRAINT FK_Reference_8 FOREIGN KEY (uid) REFERENCES mybatisuser (id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

1. 一对一的情况

一个账号只对应一个用户

配置方式

user类：

package SpringBoot.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.sql.Timestamp;


@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String userName;
    private Timestamp birthday;
    private Integer sex;
    private String address;
    private Account account;
}

account类：

package SpringBoot.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.sql.Timestamp;


@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String userName;
    private Timestamp birthday;
    private Integer sex;
    private String address;
    private Account account;
}

accountUserMap接口

package SpringBoot.dao;

import SpringBoot.entity.Account;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;


@Mapper
public interface accountUserMap {
    List findAll();
}

accountUserMap.xml文件：




    
        
        
        
        
        
            
            
            
            
            
        
    
    
        select *,o.id oid from user u left join account o on u.id=o.uid

注解方式

UserMapper:

package SpringBoot.dao;

import SpringBoot.entity.Account;
import SpringBoot.entity.User;
import org.apache.ibatis.annotations.*;

import java.util.List;


@Mapper
public interface UserMapper {

    @Select("select * from user where id=#{id}")
    User findById(int id);

    @Select("select * from user")
    @Results({
            @Result(id=true,property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "birthday",column = "birthday"),
            @Result(property = "sex",column = "sex"),
            @Result(property = "address",column = "address"),
            @Result(property = "accountList",column = "id",
                    javaType = List.class,
                    many = @Many(select = "SpringBoot.dao.AccountMapper.selectById"))
    })
    List findOnAccount();
}

AccountMapper:

package SpringBoot.dao;

import SpringBoot.entity.Account;
import SpringBoot.entity.User;
import org.apache.ibatis.annotations.*;

import java.util.List;


@Mapper
public interface AccountMapper {
    @Select("select * from account")
    @Results({
            @Result(id=true,property = "id",column = "id"),
            @Result(property = "uid",column = "uid"),
            @Result(property = "money",column = "money"),
            @Result(property = "user",column = "user",
                    javaType = User.class,
                    one = @One(select = "SpringBoot.dao.AccountMapper.findById"))
    })
    List selectAll();

    @Select("select * from account where uid = #{uid}")
    Account selectById(int uid);
}

3. 多对多的情况配置方式

增加用户角色表
Role：

package SpringBoot.entity;


public class Role {
    private int id;
    private String rolename;
}

UserMapper: