需求缘起
之前几节的示例,都是单表操作,但实际项目中,还有很多时候会用到多表查询,主要体现在一对一,一对多和多对多查询。这一节就来介绍下多表查询的用法。
本节将在上一节的基础上开发。
本小节代码
mybatis-demo-article(5)
1.一对一查询
1.1 创建一个订单表
# 创建表
CREATE TABLE orders
(
id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
user_id bigint(20) NOT NULL,
number int(11) DEFAULT '1' NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户表';
# 向表中添加数据:
insert into `orders`(user_id,number) values
(2,'2'),
(4,'4'),
(2,'3');
1.2 创建订单实体类
@Data
public class Orders {
//订单ID
private int id;
//用户ID
private int userId;
//订单数量
private String number;
//和用户表构成一对一的关系,即一个订单只能由一个用户创建
private UserInfo user;
}
1.3 编写接口 OrdersMapper.java
package com.mybatis.demo.mapper;
import com.mybatis.demo.model.Orders;
import com.mybatis.demo.model.UserInfo;
/**
* @auther kklu
* @date 2019/10/15 15:44
* @describe
*/
public interface OrdersMapper {
/**
* 方式一:嵌套结果
* select * from orders o,user u where o.user_id=u.id and o.id=#{id}
*
* @param orderId
* @return
*/
//根据订单ID查询订单和用户信息
Orders selectOrdersAndUserInfoByOrderID(int orderId);
/**
* 方式二:嵌套查询
* select * from order WHERE id=1;//得到user_id
* select * from user WHERE id=1 //1 是上一个查询得到的user_id的值
*
* @param orderId
* @return
*/
//根据订单ID得到订单信息(包含user_id)
Orders getOrdersByOrderId(int orderId);
//根据用户ID查询用户信息
UserInfo getUserByUserId(int userID);
}
1.3 编写 OrdersMapper.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="com.mybatis.demo.mapper.OrdersMapper">
<!--
嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集
封装联表查询的数据(去除重复的数据)
select * from order o,user_inf u where o.user_id=u.id and o.id=#{id}
-->
<select id="selectOrderAndUserInfoByOrderID" resultMap="getOrderAndUserInfo">
select * from order o,user_info u where o.user_id=u.id and o.id=#{id}
</select>
<resultMap type="com.mybatis.demo.model.Order" id="getOrderAndUserInfo">
<!--
id:指定查询列表唯一标识,如果有多个唯一标识,则配置多个id
column:数据库对应的列
property:实体类对应的属性名
-->
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<!--association:用于映射关联查询单个对象的信息
property:实体类对应的属性名
javaType:实体类对应的全类名
-->
<association property="userInfo" javaType="com.mybatis.demo.model.UserInfo">
<!--
id:指定查询列表唯一标识,如果有多个唯一标识,则配置多个id
column:数据库对应的列
property:实体类对应的属性名
-->
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="high" property="high"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
</association>
</resultMap>
<!--
方式二:嵌套查询:通过执行另外一个SQL映射语句来返回预期的复杂类型
select user_id from order WHERE id=1;//得到user_id
select * from user WHERE id=1 //1 是上一个查询得到的user_id的值
property:别名(属性名) column:列名 -->
<select id="getOrderByOrderId" resultMap="getOrderMap">
select * from order where id=#{id}
</select>
<resultMap type="com.mybatis.demo.model.Order" id="getOrderMap">
<id column="id" property="id"/>
<result column="number" property="number"/>
<!--property="userInfo" : orders 中要查询的属性-->
<!--column="user_id" : user_id 要查询的条件属性-->
<association property="userInfo" column="user_id" select="getUserInfoByUserId">
</association>
</resultMap>
<select id="getUserInfoByUserId" resultType="com.mybatis.demo.model.UserInfo">
select * from user where id=#{id}
</select>
</mapper>
1.4 在配置文件 mybatis-configuration.xml 中注册 OrderMapper.xml 文件
<mappers>
<!--注册多个接口的方式,该包名下所有的接口-->
<package name="com.mybatis.demo.mapper"/>
</mappers>
1.5 测试
package com.mybatis.demo.mapper;
import com.mybatis.demo.model.Orders;
import com.mybatis.demo.model.UserInfo;
import lombok.extern.slf4j.Slf4j;
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.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.io.IOException;
import java.io.Reader;
/**
* @auther kklu
* @date 2019/10/15 16:18
* @describe
*/
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class OrdersMapperTest {
private static SqlSessionFactory sqlSessionFactory;
private static SqlSession sqlSession = null;
private static OrderMapper orderMapper;
@BeforeClass
public static void init() {
try {
//将工具类读入 reader
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
//创建 SqlSessionFactory 对象,该对象包含了mybatis-config.xml相关配置信息
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
sqlSession = sqlSessionFactory.openSession();
orderMapper = sqlSession.getMapper(OrderMapper.class);
reader.close();
} catch (IOException ignore) {
ignore.printStackTrace();
}
}
@Test
public void selectOrderAndUserInfoByOrderID() {
try {
Orders result = orderMapper.selectOrdersAndUserInfoByOrderID(1);
log.info("result={}", result);
} catch (Exception e) {
e.printStackTrace();
} finally {
//不要忘记关闭 sqlSession
sqlSession.close();
}
}
@Test
public void getOrdersByOrderId() {
try {
Orders result = orderMapper.getOrdersByOrderId(1);
log.info("result={}", result);
} catch (Exception e) {
e.printStackTrace();
} finally {
//不要忘记关闭 sqlSession
sqlSession.close();
}
}
@Test
public void getUserInfoByUserId() {
try {
UserInfo result = orderMapper.getUserInfoByUserId(2);
log.info("result={}", result);
} catch (Exception e) {
e.printStackTrace();
} finally {
//不要忘记关闭 sqlSession
sqlSession.close();
}
}
}
2.一对多查询
2.1 添加 orders 属性
以用户表 user_info 和 订单表 orders 为例,一个用户能创建多个订单。故用户和订单构成一对多的关联。
我们在 userInfo.java 中添加一个属性 public List orders;
@Data
public class UserInfo {
private Long id;
private String username;
private String password;
private Integer high;
private Integer age;
private Integer sex;
//一个用户能创建多个订单,用户和订单构成一对多的关系
private List<Orders> orders;
}
2.2 创建 UserInfoMapper.java 接口
将上一节的接口 UserInfoMapper.java 中的方法全部删除,然后添加新方法如下:
public interface UserInfoMapper {
//根据用户id查询用户信息,以及用户下面的所有订单信息
UserInfo selectUserInfoAndOrdersByUserId(int UserId);
}
2.3 修改 UserInfoMapper.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="com.mybatis.demo.mapper.UserInfoMapper">
<!--
方式一:嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集
封装联表查询的数据(去除重复的数据)
select * from user u,orders o where u.id=o.user_id and u.id=#{id}
-->
<select id="selectUserInfoAndOrdersByUserId" resultMap="getUserInfoAndOrders">
select u.*,o.id oid,o.number number,o.user_id userId from user_info u,orders o where u.id=o.user_id and u.id=#{id}
</select>
<resultMap type="com.mybatis.demo.model.UserInfo" id="getUserInfoAndOrders">
<!--id:指定查询列表唯一标识,如果有多个唯一标识,则配置多个id
column:数据库对应的列
property:实体类对应的属性名 -->
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="high" property="high"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<!--
property:实体类中定义的属性名
ofType:指定映射到集合中的全类名
-->
<collection property="orders" ofType="com.mybatis.demo.model.Orders">
<id column="oid" property="id"/>
<result column="number" property="number"/>
<result column="userId" property="userId"/>
</collection>
</resultMap>
<!--
方式二:嵌套查询:通过执行另外一个SQL映射语句来返回预期的复杂类型
-->
<select id="getUserInfoAndOrdersByUserInfoId" parameterType="int" resultMap="getOrderMap">
select * from user_info where id=#{id}
</select>
<resultMap type="com.mybatis.demo.model.UserInfo" id="getOrderMap">
<id column="id" property="id"/>
<id column="username" property="username"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<!--property="orders" : userInfo 被查询表中要查询的属性-->
<!--column="id" : id 被查询表中的条件属性-->
<collection property="orders" column="id" select="getOrdersByOrderId">
</collection>
</resultMap>
<select id="getOrdersByOrderId" parameterType="int" resultType="com.mybatis.demo.model.Orders">
select * from orders where user_id=#{id}
</select>
</mapper>
2.4 测试
package com.mybatis.demo.mapper;
import com.mybatis.demo.model.UserInfo;
import com.mybatis.demo.form.UserInfoForm;
import lombok.extern.slf4j.Slf4j;
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.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
/**
* @auther kklu
* @date 2019/9/29 15:49
* @describe
*/
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserInfoMapperTest {
private static SqlSessionFactory sqlSessionFactory;
private static SqlSession sqlSession = null;
private static UserInfoMapper userInfoMapper;
@BeforeClass
public static void init() {
try {
//将工具类读入 reader
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
//创建 SqlSessionFactory 对象,该对象包含了mybatis-config.xml相关配置信息
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
sqlSession = sqlSessionFactory.openSession();
userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);
reader.close();
} catch (IOException ignore) {
ignore.printStackTrace();
}
}
@Test
public void testSelectUserInfoByUsernameAndSex() {
try {
UserInfo result = userInfoMapper.selectUserInfoAndOrdersByUserId(2);
log.info("result={}", result);
} catch (Exception e) {
e.printStackTrace();
} finally {
//不要忘记关闭 sqlSession
sqlSession.close();
}
}
}
3.多对多查询
为了更好的演示示例,我们增加一个 角色表 sys_role,一个用户可以有多个角色(比如管理员,会员等),同时一个角色也可以属于多个用户(比如一个admin角色可有属于多个用户),这就是多对多的关系。
需求:
- 给定角色id,查询这个角色所属的所有用户信息
- 给定用户id,查询这个用户所拥有的角色信息
3.1 创建角色表 sys_role 和用户角色关系表
用户表接着使用上面的表。
# 创建角色表
CREATE TABLE sys_role
(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(64) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '角色表';
# 创建用户角色关系表
CREATE TABLE user_role
(
id int PRIMARY KEY AUTO_INCREMENT,
user_id int NOT NULL,
role_id int NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户角色关系表';
# 添加角色信息
insert into sys_role(name) values
('admin1'),
('admin2'),
('member1'),
('member2'),
('member3');
# 添加用户和角色关系信息
insert into user_role(user_id,role_id) values
(2,'1'),
(2,'3'),
(4,'3'),
(4,'4');
3.2 创建实体类
- 创建角色实体
@Data
public class SysRole {
private Long id;
private String name;
//一个角色可以包含多个用户,即一个角色可以属于多个用户
private List<UserInfo> userInfoList;
}
- 创建用户实体
在上一节的 UserInfo.java 中添加角色信息
@Data
public class UserInfo {
private Long id;
private String username;
private String password;
private Integer high;
private Integer age;
private Integer sex;
//一个用户能创建多个订单,用户和订单构成一对多的关系
private List<Orders> orders;
//一个用户可以有多个角色,即一个用户可以是会员同时也是管理员
private List<SysRole> sysRoleList;
}
3.3 添加接口
public interface UserInfoMapper {
//多对多查询--给定角色id,查询这个角色所属的所有用户信息
List<UserInfo> selectUserInfoByRoleId(int roleId);
//多对多查询--给定用户id,查询这个用户所拥有的角色信息
List<SysRole> selectSysRoleByUserInfoId(int userId);
}
3.4 编写 UserInfoMapper.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="com.mybatis.demo.mapper.UserInfoMapper">
<!--多对多查询:给定角色id,查询这个角色所属的所有用户信息-->
<select id="selectUserInfoByRoleId" resultMap="getUserInfoMap">
select u.id,u.username,u.age,u.sex from user_role ur,user_info u where ur.user_id=u.id and ur.role_id=#{id}
</select>
<resultMap type="com.mybatis.demo.model.UserInfo" id="getUserInfoMap">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
</resultMap>
<!--多对多查询:给定用户id,查询这个用户所拥有的角色信息-->
<select id="selectSysRoleByUserInfoId" resultMap="getSysRoleMap">
select r.id,r.name from user_role ur,sys_role r where ur.role_id=r.id and ur.user_id=#{id}
</select>
<resultMap type="com.mybatis.demo.model.SysRole" id="getSysRoleMap">
<id column="id" property="id"/>
<result column="name" property="name"/>
</resultMap>
</mapper>
3.5 测试
package com.mybatis.demo.mapper;
import com.mybatis.demo.model.SysRole;
import com.mybatis.demo.model.UserInfo;
import com.mybatis.demo.form.UserInfoForm;
import lombok.extern.slf4j.Slf4j;
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.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
/**
* @auther kklu
* @date 2019/9/29 15:49
* @describe
*/
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserInfoMapperTest {
private static SqlSessionFactory sqlSessionFactory;
private static SqlSession sqlSession = null;
private static UserInfoMapper userInfoMapper;
@BeforeClass
public static void init() {
try {
//将工具类读入 reader
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
//创建 SqlSessionFactory 对象,该对象包含了mybatis-config.xml相关配置信息
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
sqlSession = sqlSessionFactory.openSession();
userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);
reader.close();
} catch (IOException ignore) {
ignore.printStackTrace();
}
}
//多对多查询--给定角色id,查询这个角色所属的所有用户信息
@Test
public void testSelectUserInfoByRoleId() {
try {
List<UserInfo> result = userInfoMapper.selectUserInfoByRoleId(3);
log.info("result={}", result);
} catch (Exception e) {
e.printStackTrace();
} finally {
//不要忘记关闭 sqlSession
sqlSession.close();
}
}
//多对多查询--给定用户id,查询这个用户所拥有的角色信息
@Test
public void testSelectSysRoleByUserInfoId() {
try {
List<SysRole> result = userInfoMapper.selectSysRoleByUserInfoId(4);
log.info("result={}", result);
} catch (Exception e) {
e.printStackTrace();
} finally {
//不要忘记关闭 sqlSession
sqlSession.close();
}
}
}
4. 总结
在大型系统上,由于分库分表的原因,由逻辑来控制比价好,减少表之间的关联查询,方便系统进行扩展。
但是在一般的企业级应用中,可使用MyBatis的高级映射结果来处理一对一,一对多和多对多的关系。