Mybatis表联合查询（一对一）

Mybatis表联合查询（一对一）

一对一表联合查询：

1.先创建两个表的包装类User类和Country类：

User类：

public class User {
	/*
	 * `u_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `u_username` varchar(64) NOT NULL COMMENT '用户名',
  `u_password` varchar(64) DEFAULT NULL COMMENT '用户密码',
  `u_sex` varchar(16) DEFAULT NULL COMMENT '用户性别',
  `u_createTime` datetime DEFAULT NULL COMMENT '用户创建时间',
  `u_cid` int(11) DEFAULT NULL COMMENT '用户国家id',
	 */
	private Integer u_id;
	private String u_username;
	private String u_password;
	private String u_sex;
	private Date u_createTime;
	private Integer u_cid;
	
	public User() {
		super();
		// TODO Auto-generated constructor stub
	}
	public User(Integer u_id, String u_username, String u_password, String u_sex, Date u_createTime, Integer u_cid) {
		super();
		this.u_id = u_id;
		this.u_username = u_username;
		this.u_password = u_password;
		this.u_sex = u_sex;
		this.u_createTime = u_createTime;
		this.u_cid = u_cid;
	}
	public Integer getU_id() {
		return u_id;
	}
	public void setU_id(Integer u_id) {
		this.u_id = u_id;
	}
	public String getU_username() {
		return u_username;
	}
	public void setU_username(String u_username) {
		this.u_username = u_username;
	}
	public String getU_password() {
		return u_password;
	}
	public void setU_password(String u_password) {
		this.u_password = u_password;
	}
	public String getU_sex() {
		return u_sex;
	}
	public void setU_sex(String u_sex) {
		this.u_sex = u_sex;
	}
	public Date getU_createTime() {
		return u_createTime;
	}
	public void setU_createTime(Date u_createTime) {
		this.u_createTime = u_createTime;
	}
	public Integer getU_cid() {
		return u_cid;
	}
	public void setU_cid(Integer u_cid) {
		this.u_cid = u_cid;
	}
	@Override
	public String toString() {
		return "User [u_id=" + u_id + ", u_username=" + u_username + ", u_password=" + u_password + ", u_sex=" + u_sex
				+ ", u_createTime=" + u_createTime + ", u_cid=" + u_cid + "]";
	}
	
}

Country类：

public class Country {
	private Integer id;
	private String c_countryname;
	private String c_capital;
	
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getC_countryname() {
		return c_countryname;
	}
	public void setC_countryname(String c_countryname) {
		this.c_countryname = c_countryname;
	}
	public String getC_capital() {
		return c_capital;
	}
	public void setC_capital(String c_capital) {
		this.c_capital = c_capital;
	}
	@Override
	public String toString() {
		return "Country [id=" + id + ", c_countryname=" + c_countryname + ", c_capital=" + c_capital + "]";
	}
	
}

2.创建一个UserVao类继承User类：

public class UserVao extends User {
	private Country country;

	public Country getCountry() {
		return country;
	}

	public void setCountry(Country country) {
		this.country = country;
		
	}
	@Override
	//将你说需要查询的User表数据写入toString方法
	public String toString() {
		return "UserVao [country=" + country + ", getU_id()=" + getU_id() + ", getU_username()=" + getU_username()
				+ ", getU_sex()=" + getU_sex() + "]";
	}
	
}

3.修改Mapper.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="mapper.UserMapper">
<!-- 创建resultMap封装，type为UserVao的全类名 -->
<resultMap type="ssm_mybatis.UserVao" id="uservao">
<!-- property为对应类中的参数，column为这些参数在sql表中对应的名称 -->
<id property="u_id" column="u_id"/>
<result property="u_username" column="u_username"/>
<result property="u_sex" column="u_sex"/>
<!-- javaType为Country类的全类名 -->
<association property="country" javaType="ssm_mybatis.Country">
<id property="id" column="c_id"/>
<result property="c_countryname" column="c_countryname"/>
</association>
</resultMap>
<!-- resultMap为配置的resultMap的id-->
<select id="selectAllUserVo" resultMap="uservao">
SELECT u.u_id,u.u_username,u.u_sex,c.c_id,c.c_countryname from user u LEFT JOIN country c on u.u_cid=c.c_id
</select>
</mapper>

4.创建一个mapper接口实现动态代理

public interface Mapper {
	public List<UserVao> selectAllUserVo();
}

5.创建测试类

@Test
	public void test05() throws Exception {
		String resource="sqlMapConfig.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
		SqlSessionFactory build = builder.build(inputStream);
		SqlSession openSession = build.openSession();
		UserMapper mapper = openSession.getMapper(UserMapper.class);
		List<UserVao> userVo = mapper.selectAllUserVo();
		for (UserVao userVao : userVo) {
			System.out.println(userVao);
		}
	}