一、关联查询
在使用到多表关联查询时,经常会碰到多对多,一对多和一对一的级联操作,在Mybatis中通过resultMap标签下的association和collection解决级联操作的问题。
级联操作分为:关联查询和分步关联查询,本文介绍关联查询,如需学习分步查询,请看下一篇博客。
二、表关系说明
user表结构和数据:
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`user_name` varchar(60) DEFAULT NULL COMMENT '用户名称',
`real_name` varchar(60) DEFAULT NULL COMMENT '真实名称',
`sex` char(1) NOT NULL DEFAULT '1' COMMENT '性别',
`mobile` varchar(20) DEFAULT NULL COMMENT '电话',
`email` varchar(60) DEFAULT NULL COMMENT '邮箱',
`note` varchar(200) DEFAULT NULL COMMENT '备注',
`position_id` int(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_4` (`position_id`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('1', 'lison', '李小宇', '1', '18232344223', 'lison@qq.com', 'lison的备注', '1');
INSERT INTO `user` VALUES ('2', 'james', '陈大雷', '1', '18454656125', 'james@qq.com', 'james的备注', '2');
INSERT INTO `user` VALUES ('3', 'cindy', '王美丽', '0', '14556656512', 'xxoo@163.com', 'cindy\'s note', '1');
INSERT INTO `user` VALUES ('126', 'mark', '毛毛', '0', '18635457815', 'xxoo@163.com', 'mark\'s note', '1');
position表结构和数据:
DROP TABLE IF EXISTS `position`;
CREATE TABLE `position` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`posname` varchar(20) DEFAULT NULL,
`note` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `position` VALUES ('1', '总经理', '负责公司日常事务');
INSERT INTO `position` VALUES ('2', '零时工', '背锅的');
job_history表结构和数据:
DROP TABLE IF EXISTS `job_history`;
CREATE TABLE `job_history` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`user_id` int(20) DEFAULT NULL,
`comp_name` varchar(50) DEFAULT NULL,
`years` int(3) DEFAULT NULL,
`title` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_5` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `job_history` VALUES ('1', '1', '阿里', '2', '程序员');
INSERT INTO `job_history` VALUES ('2', '2', '百度', '4', '项目经理');
INSERT INTO `job_history` VALUES ('3', '2', '腾讯', '1', '程序员');
INSERT INTO `job_history` VALUES ('4', '3', '京东', '1', '测试');
INSERT INTO `job_history` VALUES ('5', '3', '网易', '2', '测试主管');
INSERT INTO `job_history` VALUES ('6', '3', '享学', '1', '讲师');
user_role表结构和数据:
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
`user_id` int(20) NOT NULL DEFAULT '0',
`role_id` int(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`role_id`,`user_id`),
KEY `fk_1` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `user_role` VALUES ('1', '1');
INSERT INTO `user_role` VALUES ('1', '2');
INSERT INTO `user_role` VALUES ('2', '1');
INSERT INTO `user_role` VALUES ('3', '2');
role表结构和数据:
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`role_name` varchar(60) DEFAULT NULL COMMENT '角色名称',
`note` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `role` VALUES ('1', '业务人员', '办理日常业务');
INSERT INTO `role` VALUES ('2', '管理员', '超级管理员');
三、association和collection标签
association和collection标签都是属于resultMap下的子标签,两者的属性相同如下表格:
序号 | 属性名 | 说明 |
1 | | |
2 | | |
3 | | |
4 | | |
5 | | |
6 | | |
7 | | |
8 | | |
association和collection标签的子标签说明:
序号 | 标签名 | 说明 |
1 | | |
2 | | |
3 | | |
4 | | |
5 | | |
6 | | |
三、一对一关联查询
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="com.my.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.my.entity.User">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="user_name" jdbcType="VARCHAR" property="userName" />
<result column="real_name" jdbcType="VARCHAR" property="realName" />
<result column="sex" jdbcType="CHAR" property="sex" />
<result column="mobile" jdbcType="VARCHAR" property="mobile" />
<result column="email" jdbcType="VARCHAR" property="email" />
<result column="note" jdbcType="VARCHAR" property="note" />
<result column="position_id" jdbcType="INTEGER" property="positionId" />
</resultMap>
<resultMap id="UserAndPositionResultMap" type="com.my.entity.User" extends="BaseResultMap">
<!-- 一对一级联操作标签 -->
<association property="position" javaType="com.my.entity.Position" columnPrefix="p_">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="posname" jdbcType="VARCHAR" property="posname" />
<result column="note" jdbcType="VARCHAR" property="note" />
</association>
</resultMap>
<!-- 一对一关联查询 -->
<select id="selectUserAndPositionById" parameterType="java.lang.Integer" resultMap="UserAndPositionResultMap">
select
u.id, u.user_name, u.real_name, u.sex, u.mobile, u.email, u.note, u.position_id,
p.id p_id,p.posname p_posname,p.note p_note
from user u left join position p
on u.position_id = p.id
where u.id = #{id,jdbcType=INTEGER}
</select>
</mapper>
测试代码:
public class MybatisTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws IOException {
String resource = "mybatis-config.xml";
//1.使用mybatis的工具读取配置文件
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//2.创建sqlSessionFactory
sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
inputStream.close();
}
/**
* 测试一对一
*/
@Test
public void testOneToOne() {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.selectUserAndPositionById(1);
System.out.println(user.getPosition());
}
}
说明:一对一关联查询使用association 标签,通过设置property和javaType即可完成操作,columnPrefix根据自己情况可有可无。
四、一对多关联查询:
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="com.my.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.my.entity.User">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="user_name" jdbcType="VARCHAR" property="userName" />
<result column="real_name" jdbcType="VARCHAR" property="realName" />
<result column="sex" jdbcType="CHAR" property="sex" />
<result column="mobile" jdbcType="VARCHAR" property="mobile" />
<result column="email" jdbcType="VARCHAR" property="email" />
<result column="note" jdbcType="VARCHAR" property="note" />
<result column="position_id" jdbcType="INTEGER" property="positionId" />
</resultMap>
<resultMap id="UserAndJobHistorysResultMap" type="com.my.entity.User" extends="BaseResultMap">
<!-- 一对多级联操作标签 -->
<collection property="jobHistoryList" ofType="com.my.entity.JobHistory" columnPrefix="jh_">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="user_id" jdbcType="INTEGER" property="userId" />
<result column="comp_name" jdbcType="VARCHAR" property="compName" />
<result column="years" jdbcType="INTEGER" property="years" />
<result column="title" jdbcType="VARCHAR" property="title" />
</collection>
</resultMap>
<!-- 一对多关联查询 -->
<select id="selectUserAndJobHistorysById" parameterType="java.lang.Integer" resultMap="UserAndJobHistorysResultMap">
select
u.id, u.user_name, u.real_name, u.sex, u.mobile, u.email, u.note, u.position_id,
jh.id jh_id, jh.user_id jh_user_id,jh.comp_name jh_comp_name,jh.years jh_years,jh.title jh_title
from user u left join job_history jh
on u.id = jh.user_id
where u.id = #{id,jdbcType=INTEGER}
</select>
</mapper>
测试代码:
public class MybatisTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws IOException {
String resource = "mybatis-config.xml";
//1.使用mybatis的工具读取配置文件
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//2.创建sqlSessionFactory
sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
inputStream.close();
}
/**
* 测试一对多
*/
@Test
public void testOneToMany() {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.selectUserAndJobHistorysById(2);
System.out.println(user.getJobHistoryList());
}
}
说明:一对多关联查询使用collection 标签,通过设置property和ofType即可完成操作,columnPrefix根据自己情况可有可无。
三、多对多关联查询
其实多对多的关联不过是两个一对多的关联分别操作,熟悉了一对多之后,多对多也很简单。
mapper映射文件:
RoleMapper.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.my.mapper.RoleMapper">
<resultMap id="BaseResultMap" type="com.my.entity.Role">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="role_name" jdbcType="VARCHAR" property="roleName" />
<result column="note" jdbcType="VARCHAR" property="note" />
</resultMap>
<resultMap id="RoleAndUserResultMap" type="com.my.entity.Role" extends="BaseResultMap">
<!-- 多对多级联操作标签 -->
<collection property="userList" resultMap="com.my.mapper.UserMapper.BaseResultMap" columnPrefix="u_">
</collection>
</resultMap>
<!-- 多对多直接关联查询 -->
<select id="selectRoleAndUserAll" resultMap="RoleAndUserResultMap">
select
u.id u_id, u.user_name u_user_name, u.real_name u_real_name, u.sex u_sex,
u.mobile u_mobile, u.email u_email, u.note u_note, u.position_id u_position_id,
r.id,r.role_name,r.note
from role r left join user_role ur
on r.id = ur.role_id
left join user u
on u.id = ur.user_id
</select>
</mapper>
UserMapper.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.my.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.my.entity.User">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="user_name" jdbcType="VARCHAR" property="userName" />
<result column="real_name" jdbcType="VARCHAR" property="realName" />
<result column="sex" jdbcType="CHAR" property="sex" />
<result column="mobile" jdbcType="VARCHAR" property="mobile" />
<result column="email" jdbcType="VARCHAR" property="email" />
<result column="note" jdbcType="VARCHAR" property="note" />
<result column="position_id" jdbcType="INTEGER" property="positionId" />
</resultMap>
<resultMap id="UserAndRoleResultMap" type="com.my.entity.User" extends="BaseResultMap">
<!-- 多对多级联操作标签 -->
<collection property="roleList" resultMap="com.my.mapper.RoleMapper.BaseResultMap" columnPrefix="r_">
</collection>
</resultMap>
<!-- 多对多关联查询 -->
<select id="selectUserAndRoleAll" resultMap="UserAndRoleResultMap">
select
u.id, u.user_name, u.real_name, u.sex, u.mobile, u.email, u.note, u.position_id,
r.id r_id,r.role_name r_role_name,r.note r_note
from user u left join user_role ur
on u.id = ur.user_id
left join role r
on r.id = ur.role_id
</select>
</mapper>
测试代码:
public class MybatisTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws IOException {
String resource = "mybatis-config.xml";
//1.使用mybatis的工具读取配置文件
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//2.创建sqlSessionFactory
sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
inputStream.close();
}
/**
* 测试多对多
*/
@Test
public void testManyToMany() {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//所有用户下的权限信息
List<User> userList = userMapper.selectUserAndRoleAll();
System.out.println(userList);
RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
//所有权限信息下的用户
List<Role> roleList = roleMapper.selectRoleAndUserAll();
System.out.println(roleList);
}
}