背景
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;
/**
* @author zhang kun
* @Classname User
* @Description TODO
* @Date 2022/4/30 10:44
*/
@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;
/**
* @author zhang kun
* @Classname User
* @Description TODO
* @Date 2022/4/30 10:44
*/
@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;
/**
* @author zhang kun
* @Classname accountUserMap
* @Description TODO
* @Date 2022/4/30 10:47
*/
@Mapper
public interface accountUserMap {
List<Account> findAll();
}
accountUserMap.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="SpringBoot.dao.accountUserMap">
<resultMap id="accountUserMap" type="SpringBoot.entity.Account">
<id property="id" column="aid"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
<!--一对一的关系映射,配置封装user的内容-->
<association property="User" column="uid">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
</association>
</resultMap>
<select id="findAll" resultMap="accountUserMap">
SELECT u.*,a.id as aid,a.uid,a.money FROM ACCOUNT a, user u WHERE a.uid=u.id;
</select>
</mapper>
SpringBoot 测试:
package SpringBoot.Dao;
import SpringBoot.dao.accountUserMap;
import SpringBoot.entity.Account;
import com.sun.el.stream.Stream;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.List;
/**
* @author zhang kun
* @Classname TestUserMapper
* @Description TODO
* @Date 2022/4/30 10:51
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class TestUserMapper {
@Autowired
private accountUserMap accountUserMaps;
@Test
public void test1(){
List<Account> all = accountUserMaps.findAll();
System.out.println(all.size());
}
}
注解方式
userMapper:
package SpringBoot.dao;
import SpringBoot.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
/**
* @author zhang kun
* @Classname UserMapper
* @Description TODO
* @Date 2022/4/30 15:47
*/
@Mapper
public interface UserMapper {
@Select("select * from user where id=#{uid}")
User findById(int uid);
}
AccountMapper::
package SpringBoot.dao;
import SpringBoot.entity.Account;
import SpringBoot.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
/**
* @author zhang kun
* @Classname AccountMapper
* @Description TODO
* @Date 2022/4/30 15:46
*/
@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 = "uid",
javaType = User.class,
one = @One(select = "SpringBoot.dao.UserMapper.findById"))
})
List<Account> selectAll();
}
测试:
package SpringBoot.Dao;
import SpringBoot.dao.AccountMapper;
import SpringBoot.dao.accountUserMap;
import SpringBoot.entity.Account;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.List;
/**
* @author zhang kun
* @Classname TestUserMapper
* @Description TODO
* @Date 2022/4/30 10:51
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class TestUserMapper1 {
@Autowired
private AccountMapper accountMapper;
@Test
public void test1(){
List<Account> all = accountMapper.selectAll();
System.out.println(all.size());
}
}
2. 一对多的情况
配置方式
UserMapper:
<?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="SpringBoot.dao.UserMapper">
<resultMap id="userMap" type="SpringBoot.entity.User">
<result column="id" property="id"></result>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
<collection property="accountList" ofType="SpringBoot.entity.Account">
<result column="id" property="id"></result>
<result column="uid" property="uid"></result>
<result column="money" property="money"></result>
</collection>
</resultMap>
<select id="findOnAccount" resultMap="userMap">
select *,o.id oid from user u left join account o on u.id=o.uid
</select>
</mapper>
注解方式
UserMapper:
package SpringBoot.dao;
import SpringBoot.entity.Account;
import SpringBoot.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
/**
* @author zhang kun
* @Classname UserMapper
* @Description TODO
* @Date 2022/4/30 15:47
*/
@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<User> findOnAccount();
}
AccountMapper:
package SpringBoot.dao;
import SpringBoot.entity.Account;
import SpringBoot.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
/**
* @author zhang kun
* @Classname AccountMapper
* @Description TODO
* @Date 2022/4/30 15:46
*/
@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<Account> selectAll();
@Select("select * from account where uid = #{uid}")
Account selectById(int uid);
}
3. 多对多的情况
配置方式
增加用户角色表
Role:
package SpringBoot.entity;
/**
* @author zhang kun
* @Classname Role
* @Description TODO
* @Date 2022/5/2 11:34
*/
public class Role {
private int id;
private String rolename;
}
UserMapper:
<?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="SpringBoot.dao.UserMapper">
<resultMap id="userRoleMap" type="SpringBoot.entity.User">
<result column="id" property="id"></result>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
<collection property="roleList" ofType="SpringBoot.entity.Role">
<result column="id" property="id"></result>
<result column="rolename" property="rolename"></result>
</collection>
</resultMap>
<select id="findAllUserAndRole" resultMap="userRoleMap">
select u.*,r.*,r.id rid from user u left join user_role ur on u.id=ur.user_id
inner join role r on ur.role_id=r.id
</select>
</mapper>
注解方式
roleMapper:
package SpringBoot.dao;
import SpringBoot.entity.Role;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
* @author zhang kun
* @Classname RoleMapper
* @Description TODO
* @Date 2022/5/2 12:00
*/
@Mapper
public interface RoleMapper {
@Select("select * from role r,user_role ur where r.id=ur.role_id and ur.user_id=#{uid}")
List<Role> findByUid(int uid);
}
UserMapper:
package SpringBoot.dao;
import SpringBoot.entity.Account;
import SpringBoot.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
/**
* @author zhang kun
* @Classname UserMapper
* @Description TODO
* @Date 2022/4/30 15:47
*/
@Mapper
public interface UserMapper {
@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 = "roleList",column = "id",
javaType = List.class,
many = @Many(select = "SpringBoot.dao.RoleMapper.findByUid"))
})
List<User> findAllUserAndRole();
}
测试:
@Test
public void test3(){
List<User> allUserAndRole = userMapper.findAllUserAndRole();
System.out.println(allUserAndRole.size());
}
}