MyBatis学习5之三表查询

紧接上一篇多表查询OneToMany,本篇是三表查询(t_person,t_group,t_user)具体表结构以及测试数据请参看MyBatis多表查询5第一篇。

需求：根据人员的ID查询人员的信息以及所在组和对应的用户登录信息

1、新建一个查询结果实体类

public class BaseInfoVo {
	
	private int id;
	private String name;
	private String telphone;
	private String address;
	private String email;
	private int uid;
	private String userName;
	private String password;
	private int gid;
	private String groupName;
	private String description;
	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 getTelphone() {
		return telphone;
	}
	public void setTelphone(String telphone) {
		this.telphone = telphone;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public int getUid() {
		return uid;
	}
	public void setUid(int uid) {
		this.uid = uid;
	}
	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;
	}
	public int getGid() {
		return gid;
	}
	public void setGid(int gid) {
		this.gid = gid;
	}
	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;
	}	
}

 

2、添加对应的sql查询

<select id="personInfo" resultMap="personInfo">
	 	<!--查询某个人的登录用户以及所在组的信息-->
		select p.id,p.name,p.telphone,p.address,p.email,
		u.id uid,u.user_name,u.password,
		g.id gid,g.group_name,g.description
		from t_person p left join t_user u on p.id = u.pid
		left join t_group g on g.id = p.gid
		where p.id = #{id}
	 </select>
	 <resultMap type="com.zlt.mybatis.vo.BaseInfoVo" id="personInfo">
	 	<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="uid" column="uid"/>
	 	<result property="userName" column="user_name"/>
	 	<result property="password" column="password"/>
	 	<result property="gid" column="gid"/>
	 	<result property="groupName" column="group_name"/>
	 	<result property="description" column="description" />
	 </resultMap>

 3、编写单元测试

public void testQuery(){
		SqlSession session = null;
		try{
				session = sqlSessionFactory.openSession();
				String statement = "com.zlt.mybatis.model.Group.personInfo";
				BaseInfoVo person = session.selectOne(statement, 2);
				if(null != person) {
						StringBuffer sb = new StringBuffer();
							sb.append("id:" + person.getId() + "\t")
							.append("name:" + person.getName() + "\t")
							.append("telphone:" + person.getTelphone() + "\t")
							.append("address:" +person.getAddress() + "\t")
							.append("email:" + person.getEmail() + "\t");
							
							sb.append("group-id:" + person.getGid() + "\t")
							  .append("groupName:" + person.getGroupName() +"\t")
							  .append("description:" + person.getDescription() +"\t");
						 
							sb.append("uid:" + person.getUid() + "\t")
							  .append("userName:" + person.getUserName())
							  .append("password:" + person.getPassword());
							System.out.println(sb.toString());
				}
			}finally{
			if(null != session) session.close();
		}

 4、运行结果

id:2	name:李四	telphone:10000	address:浦东新区广兰路	email:[e-mail]	group-id:1	groupName:Java开发部	description:Java项目组开发	uid:2	userName:lisipassword:123456