MyBatis学习5之多表查询OneToOne

在进行项目开发时，多表嵌套查询是非常多的，今天我们就来讲解下mybatis的多表查询使用.

一、数据库表准备，本篇博客使用了三张表(t_group(组信息)、t_person(人员信息)、t_user(人员对应的用户信息))

t_group表结构及测试数据

 t_person表结构及测试数据

t_user表结构及测试数据

二、对应的表的实体类

第一种情况：人员与之对应用户是1:1关系

1）Group.java

public class Group {
	
	private int id;
	private String groupName;
	private String description;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getGroupName() {
		return groupName;
	}
	public void setGroupName(String groupName) {
		this.groupName = groupName;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
}

 Person.java

public class Person {
	
	private int id;
	private String name;
	private String address;
	private String telphone;
	private String email;
	private int gid;
	private User user;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getTelphone() {
		return telphone;
	}
	public void setTelphone(String telphone) {
		this.telphone = telphone;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public int getGid() {
		return gid;
	}
	public void setGid(int gid) {
		this.gid = gid;
	}
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
}

 User.java

public class User {
	
	private int id;
	private String userName;
	private String password;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}	
}

 2）新建sql映射文件

<?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.zlt.mybatis.model.Group">
	<!-- 
	1:1查询人员与之对应的用户信息
	select 	p.id ,p.name,p.telphone,p.address,p.email,p.gid,
		u.id uid,u.user_name,u.password, u.pid
		from t_person p left join t_user  u on u.pid = p.id
		where p.id = 1
	 -->
	 <select id="findPersonByPID" parameterType="int" resultMap="PersonAndUser">
	 	select 	p.id,p.name,p.telphone,p.address,p.email,p.gid,
		u.id uid,u.user_name,u.password, u.pid
		from t_person p left join t_user  u on u.pid = p.id
		where p.id = #{id}
	 </select>
        <!-- 第一种写法 -->
	 <resultMap type="com.zlt.mybatis.model.Person" id="PersonAndUser">
	 	<id property="id" column="id"/>
	 	<result property="name" column="name"/>
	 	<result property="telphone" column="telphone" />
	 	<result property="address" column="address"/>
	 	<result property="email" column="email"/>
	 	<result property="gid" column="gid"/>
	 	<association property="user" javaType="com.zlt.mybatis.model.User">
	 		<id property="id" column="id"/>
	 		<result property="userName" column="user_name"/>
	 		<result property="password" column="password"/>
	 	</association>
	 </resultMap>
         <!-- 第二种写法-->
         <!--
         <resultMap type="com.zlt.mybatis.model.Person" id="person">
	 	<id property="id" column="id"/>
	 	<result property="name" column="name"/>
	 	<result property="telphone" column="telphone" />
	 	<result property="address" column="address"/>
	 	<result property="email" column="email"/>
	 	<result property="gid" column="gid"/>
	 </resultMap>
	 
	 <resultMap type="com.zlt.mybatis.model.Person" id="PersonAndUser" extends="person">
	 	<association property="user" javaType="com.zlt.mybatis.model.User">
	 		<id property="id" column="id"/>
	 		<result property="userName" column="user_name"/>
	 		<result property="password" column="password"/>
	 	</association>
	 </resultMap>
         -->
</mapper>

 3)在 mybatis配置文件中注册sql映射文件

<mappers>
         <mapper resource="com/zlt/mybatis/mapping/GroupPersonUserMapper.xml" />
</mappers>

 4）单元测试

public void testPersonAndUserByPID(){
		SqlSession session = null;
		try{
			session = sqlSessionFactory.openSession();
			String statement = "com.zlt.mybatis.model.Group.findPersonByPID";
			Person person = session.selectOne(statement, 3);
			if(null != person) {
				StringBuffer sb = new StringBuffer();
				sb.append("id:" + person.getId() + "\t")
				  .append("name:" + person.getName() +"\t")
				  .append("telphone:" + person.getTelphone() +"\t")	
				  .append("email:" + person.getEmail() + "\t")
				  .append("gid:" + person.getGid() + "\t");
				User user = person.getUser();
				sb.append("id:" + user.getId() + "\t")
				  .append("userName:" + user.getUserName() + "\t")
				  .append("password:" + user.getPassword() + "\t");
				System.out.println(sb.toString());
			}
		}finally{
			if(null != session) session.close();
		}
	}

 5）运行结果

id:3	name:王二	telphone:10001	email:[email protected]	gid:6	id:3	userName:wanger	password:123456

 注：这是从人员的角度去维护用户信息，反之也可以从用户的角度维护人员信息