一、⼀对一查询
1、⼀对一的查询模型MapperScannerConfigurer
⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只属于⼀个⽤户
一对一的查询需求：查询一个订单，与此同时查询出该订单所属的用户信息
用户订单关系模型.png
创建用户表sql脚本
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`birthday` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'lucy', '123', '2019-12-12');
INSERT INTO `user` VALUES ('2', 'tom','123', '2019-12-12');
创建订单表脚本
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ordertime` varchar(255) DEFAULT NULL,
`total` double DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '2019-12-12', '3000', '1');
INSERT INTO `orders` VALUES ('2', '2019-12-12', '4000', '1');
INSERT INTO `orders` VALUES ('3', '2019-12-12', '5000', '2');
2、一对一查询的语句
对应的sql语句：
SELECT * FROM `orders` A LEFT JOIN `user` B ON A.uid = B.id
对应的查询结果
订单用户一对一查询结果.png
新建Orders类，注意，在Orders类当中我们添加了user属性
package study.lagou.com.pojo;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-25 22:15
*/
public class Orders {
/**
* 订单ID
*/
private Integer id;
/**
* 下单时间
*/
private String ordertime;
/**
* 订单金额
*/
private Double total;
/**
*订单所属用户信息
*/
private User user;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrdertime() {
return ordertime;
}
public void setOrdertime(String ordertime) {
this.ordertime = ordertime;
}
public Double getTotal() {
return total;
}
public void setTotal(Double total) {
this.total = total;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", ordertime='" + ordertime + '\'' +
", total=" + total +
", user=" + user +
'}';
}
}
新建User类
package study.lagou.com.pojo;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-25 22:13
*/
public class User {
/**
* 用户ID
*/
private Integer id;
/**
* 用户名称
*/
private String username;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
'}';
}
}
创建IOrderMapper接口
package study.lagou.com.mapper;
import study.lagou.com.pojo.Orders;
import java.util.List;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-25 22:30
*/
public interface IOrderMapper {
/**
* 查询订单及订单所属的用户信息
* @return
*/
List<Orders> findOrdersAndUser();
}
接着编写IOrderMapper.xml配置文件，注意在编写配置文件的时候findOrdersAndUser方法，我们resultType变更成resultMap的形式处理了，IOrderMapper.xml存放的路径需要和IOrderMapper接口存放的路径一致，即IOrderMapper.xml配置文件与IOrderMapper接口保持同包同名，这样处理是我们后面在sqlMapConfig核心配置文件当中将以package的方式来引入Mapper配置文件
<?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="study.lagou.com.mapper.IOrderMapper">
<resultMap id="orderMaps" type="study.lagou.com.pojo.Orders">
<result property="id" column="id"></result>
<result property="ordertime" column="ordertime"></result>
<result property="total" column="total"></result>
<association property="user" javaType="study.lagou.com.pojo.User">
<result property="id" column="uid"></result>
<result property="username" column="username"></result>
</association>
</resultMap>
<select id="findOrdersAndUser" resultMap="orderMaps">
SELECT * FROM `orders` A LEFT JOIN `user` B ON A.uid = B.id
</select>
</mapper>
jdbc.properties配置文件
jdbc.driver = com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mybatis?useSSL=false&serverTimezone=Hongkong&characterEncoding=utf-8&autoReconnect=true
jdbc.username = root
jdbc.password = 111111
sqlMapConfig配置文件,注意我们采用了package的方式引入了Mapper配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="jdbc.properties"/>
<typeAliases>
<typeAlias type="study.lagou.com.pojo.User" alias="user"></typeAlias>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="study.lagou.com.mapper"/>
</mappers>
</configuration>
接着编写MyBatisMulitiTableTest测试类
package test;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import study.lagou.com.mapper.IOrderMapper;
import study.lagou.com.pojo.Orders;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-25 22:51
*/
public class MyBatisMulitiTableTest {
@Test
public void test01() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
IOrderMapper mapper = sqlSession.getMapper(IOrderMapper.class);
List<Orders> ordersAndUser = mapper.findOrdersAndUser();
for (Orders orders : ordersAndUser) {
System.out.println(orders);
}
}
}
测试结果
MyBatis一对一查询测试结果.png
二、一对多的查询
1、⼀对多的查询模型
⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只属于⼀个⽤户
一对多的查询需求：查询所有用户，与此同时查询出用户所拥有的订单信息
用户订单关系模型.png
2、一对多查询的语句
对应的sql语句：
SELECT A.*,B.id oid,B.ordertime,B.total FROM user A LEFT JOIN orders B on A.id = B.uid
对应的查询结果
Mybatis一对多查询结果.png
修改User实体类，在User实体类添加List<Orders> orderList属性
package study.lagou.com.pojo;
import java.util.List;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-25 22:13
*/
public class User {
/**
* 用户ID
*/
private Integer id;
/**
* 用户名称
*/
private String username;
/**
* 用户拥有的订单信息
*/
private List<Orders> orderList;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public List<Orders> getOrderList() {
return orderList;
}
public void setOrderList(List<Orders> orderList) {
this.orderList = orderList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", orderList=" + orderList +
'}';
}
}
编写IUserMapper接口
package study.lagou.com.mapper;
import study.lagou.com.pojo.User;
import java.util.List;
public interface IUserMapper {
List<User> findAll();
}
编写IUserMapper.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="study.lagou.com.mapper.IUserMapper">
<resultMap id="userMap" type="study.lagou.com.pojo.User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<collection property="orderList" ofType="study.lagou.com.pojo.Orders">
<id property="id" column="oid"></id>
<result property="ordertime" column="ordertime"></result>
<result property="total" column="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT A.*,B.id oid,B.ordertime,B.total FROM user A LEFT JOIN orders B on A.id = B.uid
</select>
</mapper>
在MyBatisMulitiTableTest中编写测试方法
@Test
public void test02() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
List<User> users = mapper.findAll();
for (User user : users) {
System.out.println(user);
}
}
测试结果
MyBatis一对多查询测试结果.png
三、多对多查询
1、多对多的查询模型
⽤户表和⻆⾊表的关系为，⼀个⽤户有多个⻆⾊，⼀个⻆⾊被多个⽤户使⽤
多对多查询的需求：查询⽤户同时查询出该⽤户的所有⻆⾊
用户角色多对多查询模型.png
角色信息表脚本信息
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rolename` varchar(255) DEFAULT NULL,
`roleDesc` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES ('1', 'CTO', 'CTO');
INSERT INTO `sys_role` VALUES ('2', 'CEO', 'CEO');
用户角色信息关联表脚本信息
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
`userid` int(11) NOT NULL,
`roleid` int(11) NOT NULL,
PRIMARY KEY (`userid`,`roleid`),
KEY `roleid` (`roleid`),
CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`),
CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `sys_role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
INSERT INTO `sys_user_role` VALUES ('1', '1');
INSERT INTO `sys_user_role` VALUES ('2', '1');
INSERT INTO `sys_user_role` VALUES ('1', '2');
INSERT INTO `sys_user_role` VALUES ('2', '2');
2、对多查询的语句
对应的sql语句：
SELECT * FROM user A LEFT JOIN sys_user_role B ON A.id = B.userid LEFT JOIN sys_role C ON B.roleid = C.id
对应的查询结果
Mybatis一对多查询结果.png
创建Role实体类
package study.lagou.com.pojo;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-26 0:54
*/
public class Role {
/**
* 角色ID
*/
private Integer id;
/**
* 角色名称
*/
private String rolename;
/**
* 角色描述信息
*/
private String roleDesc;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getRolename() {
return rolename;
}
public void setRolename(String rolename) {
this.rolename = rolename;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
@Override
public String toString() {
return "Role{" +
"id=" + id +
", rolename='" + rolename + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
}
修改User实体类，在User实体类添加List<Orders> orderList属性
package study.lagou.com.pojo;
import java.util.List;
/**
* @Description: 功能描述
* @Author houjh
* @Email: happyxiaohou@gmail.com
* @Date: 2021-3-25 22:13
*/
public class User {
/**
* 用户ID
*/
private Integer id;
/**
* 用户名称
*/
private String username;
/**
* 用户拥有的订单信息
*/
private List<Orders> orderList;
/**
* 用户角色信息
*/
private List<Role> roleList;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public List<Orders> getOrderList() {
return orderList;
}
public void setOrderList(List<Orders> orderList) {
this.orderList = orderList;
}
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", orderList=" + orderList +
", roleList=" + roleList +
'}';
}
}
在IUserMapper接口中添加List<User> findUserAndRole()方法
package study.lagou.com.mapper;
import study.lagou.com.pojo.User;
import java.util.List;
public interface IUserMapper {
List<User> findAll();
List<User> findUserAndRole();
}
在IUserMapper.xml配置文件中添加findUserAndRole方法
<?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="study.lagou.com.mapper.IUserMapper">
<resultMap id="userMap" type="study.lagou.com.pojo.User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<collection property="orderList" ofType="study.lagou.com.pojo.Orders">
<id property="id" column="oid"></id>
<result property="ordertime" column="ordertime"></result>
<result property="total" column="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT A.*,B.id oid,B.ordertime,B.total FROM user A LEFT JOIN orders B on A.id = B.uid
</select>
<resultMap id="userRoleMap" type="study.lagou.com.pojo.User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<collection property="roleList" ofType="study.lagou.com.pojo.Role">
<id property="id" column="oid"></id>
<result property="rolename" column="rolename"></result>
<result property="roleDesc" column="roleDesc"></result>
</collection>
</resultMap>
<select id="findUserAndRole" resultMap="userRoleMap">
SELECT * FROM user A LEFT JOIN sys_user_role B ON A.id = B.userid LEFT JOIN sys_role C ON B.roleid = C.id
</select>
</mapper>
在MyBatisMulitiTableTest中编写测试方法
@Test
public void test03() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
List<User> users = mapper.findUserAndRole();
for (User user : users) {
System.out.println(user);
}
}
测试结果
MyBatis多对多测试结果.png
上一篇笔记地址:https://www.jianshu.com/p/200d11349f8d
下一篇笔记地址:https://www.jianshu.com/p/2c6f6392d716
具体代码对应下载地址:https://gitee.com/happymima/mybatis.git
