欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MyBatis学习5之三表查询

程序员文章站 2022-07-12 17:57:44
...

紧接上一篇多表查询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:lisi@163.com	group-id:1	groupName:Java开发部	description:Java项目组开发	uid:2	userName:lisipassword:123456