一、關聯查詢
在使用到多表關聯查詢時,經常會碰到多對多,一對多和一對一的級聯操作,在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', '[email protected]', 'lison的備注', '1');
INSERT INTO `user` VALUES ('2', 'james', '陳大雷', '1', '18454656125', '[email protected]', 'james的備注', '2');
INSERT INTO `user` VALUES ('3', 'cindy', '王美麗', '0', '14556656512', '[email protected]', 'cindy\'s note', '1');
INSERT INTO `user` VALUES ('126', 'mark', '毛毛', '0', '18635457815', '[email protected]', '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);
}
}