MyBatis基础篇之使用association解决一对一关联查询

一、前言

上一章节<<MyBatis基础篇之结果映射ResultMap级联属性封装>>,我们通过级联属性的方式,将角色中的数据查出来封装到用户User的role属性里面,我们还有另外一种方式来实现数据的封装。接下来我们将介绍一下association标签的相关用法,包括嵌套查询分段查询两种方式。

二、案例

♦参照前面介绍的相关章节,如<<MyBatis基础篇之简单入门>>等,搭建工程MyBatisAssociationDemo

♦在MySQL数据库新建两张数据库表t_user和t_role,并插入若干条数据

CREATE TABLE t_user (
  id int(10) NOT NULL AUTO_INCREMENT,
  loginId varchar(20) DEFAULT NULL,
  userName varchar(100) DEFAULT NULL,
  roleId int(10),
  note varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

INSERT INTO t_user(loginId,userName,roleId,note) VALUES ('queen', '奎恩', 1, '专门负责提鞋的。。。');
INSERT INTO t_user(loginId,userName,roleId,note) VALUES ('king', '金狮子', 2, '磁性果实能力');
INSERT INTO t_user(loginId,userName,roleId,note) VALUES ('Lucy', '路西', 3, '打败多弗朗明哥。。。');
=========================================================
CREATE TABLE t_role (
  id int(10) NOT NULL AUTO_INCREMENT,
  roleName varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

INSERT INTO t_role(roleName) VALUES ('自定义角色');
INSERT INTO t_role(roleName) VALUES ('前海贼');
INSERT INTO t_role(roleName) VALUES ('未来海贼王');

♦新建JavaBean类 User

/**
 * @since 2017-08-08
 * @author queen 
 * 定义一个Java类
 *
 */
public class User {
	// ID,唯一性
	private int id;
	// 登录ID
	private String loginId;
	// 用户名
	private String userName;
	// 角色
	private Role role;
	// 备注
	private String note;

        public User(){
 
        }
         
	public User(int id, String loginId, String userName, Role role, String note) {
		this.id = id;
		this.loginId = loginId;
		this.userName = userName;
		this.role = role;
		this.note = note;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getLoginId() {
		return loginId;
	}

	public void setLoginId(String loginId) {
		this.loginId = loginId;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public Role getRole() {
		return role;
	}

	public void setRole(Role role) {
		this.role = role;
	}

	public String getNote() {
		return note;
	}

	public void setNote(String note) {
		this.note = note;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", loginId=" + loginId + ", userName="
				+ userName + ", role=" + role + ", note=" + note + "]";
	}

}

♦新建JavaBean类 Role

/**
 * @since 2017-08-08
 * @author queen 
 * 定义一个Java类Role
 *
 */
public class Role {
	private int id;
	private String roleName;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getRoleName() {
		return roleName;
	}

	public void setRoleName(String roleName) {
		this.roleName = roleName;
	}

        public Role(){
 
        }
        
	public Role(int id, String roleName) {
		this.id = id;
		this.roleName = roleName;
	}

	@Override
	public String toString() {
		return "Role [id=" + id + ", roleName=" + roleName + "]";
	}

}

♦新建接口类UserMapper.java,增加接口方法getUserById

import com.queen.mybatis.bean.User;
/**
 * @since 2017-08-08
 * @author queen 
 * 定义一个UserMapper接口
 *
 */
public interface UserMapper {
	
	/**
	 * 根据ID查询User信息,返回一条记录User
	 * @param id
	 * @return
	 */
	public User getUserById(int id);
	
}

♦核心配置文件db.properties,log4j.properties,mybatis-config.xml可以直接从<<MyBatis基础篇之简单入门>>拷贝过来

主要的核心文件都已写好,接下来我们将分步介绍嵌套查询分段查询两种方式

♦首先我们来看一下嵌套查询,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.queen.mybatis.mapper.UserMapper">

	<resultMap type="com.queen.mybatis.bean.User" id="userResultMap">
		<id property="id" column="id"/>
	    <result property="loginId" column="loginId" />
	    <result property="userName" column="userName"/>
	    <result property="note" column="note"/>
            <!--assocication可以指定联合的JavaBean对象 
                property="role"指定哪个属性是联合的对象
                javaType:指定这个属性对象的类型
            -->
	    <association property="role" javaType="com.queen.mybatis.bean.Role">
	    	<id column="role_id" property="id"/>
	    	<result column="roleName" property="roleName"/>
	    </association>
	</resultMap>
	
	<select id="getUserById" resultMap="userResultMap">
		select m.id id, m.loginId loginId, m.userName userName, m.roleId roleId,m.note note, n.id role_id, n.roleName roleName 
			from t_user m left join t_role n on m.roleId=n.id
			where m.id=#{id}
	</select>
	
</mapper>

♦新增测试类MyBatisTest,添加测试方法testGetUserByAssocication

@Test
public void testGetUserByAssocication() throws IOException {
	SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
	SqlSession openSession = sqlSessionFactory.openSession();
	try {
		UserMapper mapper = openSession.getMapper(UserMapper.class);
		User user = mapper.getUserById(1);
		System.out.println(user);
		System.out.println(user.getRole());
	} finally {
		openSession.close();
	}
}

♦测试,控制台打印结果如下:

2017-08-08 22:46:52,470 [main] [com.queen.mybatis.mapper.UserMapper.getUserById]-[DEBUG] ==>  Preparing: select m.id id, m.loginId loginId, m.userName userName, m.roleId roleId,m.note note, n.id role_id, n.roleName roleName from t_user m left join t_role n on m.roleId=n.id where m.id=? 
2017-08-08 22:46:52,558 [main] [com.queen.mybatis.mapper.UserMapper.getUserById]-[DEBUG] ==> Parameters: 1(Integer)
2017-08-08 22:46:52,597 [main] [com.queen.mybatis.mapper.UserMapper.getUserById]-[DEBUG] <==      Total: 1
User [id=1, loginId=queen, userName=奎恩, role=Role [id=1, roleName=自定义角色], note=专门负责提鞋的。。。]
Role [id=1, roleName=自定义角色]

这样我们可以通过association也能够实现关联属性的查询,使用association定义关联的单个对象的封装结果。

接下来我们介绍第二种:使用association进行分步查询,那么分步查询是什么意思呢?用我们上述例子,即我们查完User表数据后,我们在查询Role表数据。分多条SQL执行。

废话不多说了,直接上代码

♦定义一个接口类RoleMapper,新增接口方法getRoleById

import com.queen.mybatis.bean.Role;

/**
 * @since 2017-08-08
 * @author queen 
 * 定义一个RoleMapper接口
 *
 */
public interface RoleMapper {
	/**
	 * 根据ID查询Role信息,返回一条记录Role
	 * @param id
	 * @return
	 */
	public Role getRoleById(int id);
}

♦定义一个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.queen.mybatis.mapper.RoleMapper">
	<select id="getRoleById" resultType="com.queen.mybatis.bean.Role">
		select id, roleName from t_role where id=#{id}
	</select>
</mapper>

♦修改UserMapper的Java文件,新增一个方法getUserByIdStep

/**
 * 根据ID查询User信息,返回一条记录User,分步查询
 * @param id
 * @return
 */
public User getUserByIdStep(int id);

♦修改UserMapper.xml文件,增加如下配置

<resultMap type="com.queen.mybatis.bean.User" id="userResultMapStep">
	<id property="id" column="id"/>
	<result property="loginId" column="loginId" />
	<result property="userName" column="userName"/>
	<result property="note" column="note"/>
	<!-- association定义关联对象的封装规则 
	    select:表明当前属性是调用select指定的方法查询出结果
	    column:指定将那一列的值传递给这个方法
	    整个流程:使用Select指定的方法(传入column指定的这列参数的值)查出对象,并封装给property指定的属性
	 -->
	<association property="role" select="com.queen.mybatis.mapper.RoleMapper.getRoleById"
	    	column="roleId">
	</association>
</resultMap>
<select id="getUserByIdStep" resultMap="userResultMapStep">
	select * from t_user where id=#{id}
</select>

♦修改测试类MyBatisTest,添加测试方法testGetUserByAssocicationStep

@Test
public void testGetUserByAssocicationStep() throws IOException {
	SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
	SqlSession openSession = sqlSessionFactory.openSession();
	try {
		UserMapper mapper = openSession.getMapper(UserMapper.class);
		User user = mapper.getUserByIdStep(1);
		System.out.println(user);
		System.out.println(user.getRole());
	} finally {
		openSession.close();
	}
}

♦测试方法,控制台打印结果如下:

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalArgumentException: Mapped Statements collection does not contain value for com.queen.mybatis.mapper.RoleMapper.getRoleById
### The error may exist in UserMapper.xml
### The error may involve com.queen.mybatis.mapper.UserMapper.getUserByIdStep
### The error occurred while handling results
### SQL: select * from t_user where id=?
### Cause: java.lang.IllegalArgumentException: Mapped Statements collection does not contain value for com.queen.mybatis.mapper.RoleMapper.getRoleById
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:66)

结果事与愿违,报错了,这是为什么呢?注意我们一定要将新增的RoleMapper.xml映射文件配置到mybatis-config.xml文件中,配置如下:

<mappers>
	<mapper resource="UserMapper.xml" />
	<mapper resource="RoleMapper.xml" />
</mappers>

重新测试一下

2017-08-08 23:26:04,799 [main] [com.queen.mybatis.mapper.UserMapper.getUserByIdStep]-[DEBUG] ==>  Preparing: select * from t_user where id=? 
2017-08-08 23:26:04,853 [main] [com.queen.mybatis.mapper.UserMapper.getUserByIdStep]-[DEBUG] ==> Parameters: 1(Integer)
2017-08-08 23:26:04,909 [main] [com.queen.mybatis.mapper.RoleMapper.getRoleById]-[DEBUG] ====>  Preparing: select id, roleName from t_role where id=? 
2017-08-08 23:26:04,909 [main] [com.queen.mybatis.mapper.RoleMapper.getRoleById]-[DEBUG] ====> Parameters: 1(Integer)
2017-08-08 23:26:04,911 [main] [com.queen.mybatis.mapper.RoleMapper.getRoleById]-[DEBUG] <====      Total: 1
2017-08-08 23:26:04,911 [main] [com.queen.mybatis.mapper.UserMapper.getUserByIdStep]-[DEBUG] <==      Total: 1
User [id=1, loginId=queen, userName=奎恩, role=Role [id=1, roleName=自定义角色], note=专门负责提鞋的。。。]
Role [id=1, roleName=自定义角色]

从上述打印结果,可以看到控制台打印了两段SQL语句,一句用来查询User信息,另外一句用来查询Role信息,实现了分步查询的效果,且控制台打印结果正常。

♦完整工程结构目录如下:

MyBatis基础篇之使用association解决一对一关联查询的照片 - 1

至此,我们关于MyBatis基础篇之使用association解决一对一关联查询介绍完毕。

博客地址:http://www.marsitman.com/mybatis/mybatis-assocication.html
版权声明:本文为博主原创文章,允许转载,但转载必须标明出处。

 

 

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!






文件名称:MyBatis基础篇之使用association解决一对一关联查询

更新日期:2017-08-08

作者信息:榕瑾

提示:下载后请检查MD5值,欢迎捐赠本站以及广告合作!

下载地址:点击下载 【文件大小:20 KB】

点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注