转载

Mybatis之多表查询

一对一 的查询( <assocation> )

创建Java类

  • 创建 User 类(用户类)
public class Userimplements Serializable{
	private static final long serialVersionUID = 6716332190979093860L;
	private Integer id;
	private String username;
	private String password;
	private Integer age;
	private Department department;   //Department对象
	

	public Department getDepartment(){
		return department;
	}

	public void setDepartment(Department department){
		this.department = department;
	}

	public Integer getAge(){
		return age;
	}

	public void setAge(Integer age){
		this.age = age;
	}

	public Integer getId(){
		return id;
	}

	public void setId(Integer 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;
	}

	@Override
	public int hashCode(){
		final int prime = 31;
		int result = 1;
		result = prime * result + ((age == null) ? 0 : age.hashCode());
		result = prime * result
				+ ((department == null) ? 0 : department.hashCode());
		result = prime * result + ((id == null) ? 0 : id.hashCode());
		result = prime * result
				+ ((password == null) ? 0 : password.hashCode());
		result = prime * result
				+ ((username == null) ? 0 : username.hashCode());
		return result;
	}

	@Override
	public boolean equals(Object obj){
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		User other = (User) obj;
		if (age == null) {
			if (other.age != null)
				return false;
		} else if (!age.equals(other.age))
			return false;
		if (department == null) {
			if (other.department != null)
				return false;
		} else if (!department.equals(other.department))
			return false;
		if (id == null) {
			if (other.id != null)
				return false;
		} else if (!id.equals(other.id))
			return false;
		if (password == null) {
			if (other.password != null)
				return false;
		} else if (!password.equals(other.password))
			return false;
		if (username == null) {
			if (other.username != null)
				return false;
		} else if (!username.equals(other.username))
			return false;
		return true;
	}

	@Override
	public String toString(){
		return "User [id=" + id + ", username=" + username + ", password="
				+ password + ", age=" + age + ", department=" + department
				+ "]";
	}
}
  • 创建 Department 类(部门类)
public class Department{
	private Integer id;
	private String name;

	public Integer getId(){
		return id;
	}

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

	public String getName(){
		return name;
	}

	public void setName(String name){
		this.name = name;
	}

	@Override
	public int hashCode(){
		final int prime = 31;
		int result = 1;
		result = prime * result + ((id == null) ? 0 : id.hashCode());
		result = prime * result + ((name == null) ? 0 : name.hashCode());
		return result;
	}

	@Override
	public boolean equals(Object obj){
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Department other = (Department) obj;
		if (id == null) {
			if (other.id != null)
				return false;
		} else if (!id.equals(other.id))
			return false;
		if (name == null) {
			if (other.name != null)
				return false;
		} else if (!name.equals(other.name))
			return false;
		return true;
	}

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

分析

  • 我们知道 一个用户只能属于一个部门 ,因此这里在 User 类中只是使用了 Department 对象,而不是个 集合
  • 那么我们想要查询所有的用户信息和其所在的部门信息,此时的 sql 语句为: select * from user u left join department d on u.department_id=d.id; 。但是我们在mybaits中如果使用这条语句查询,那么返回的结果类型是什么呢?如果是 User 类型的,那么查询结果返回的还有 Department 类型的数据,那么肯定会对应不上的。
  • 我们可以使用 resultMap 解决对应问题

实现

  • 使用 resultMap 解决查询结果的对应问题
    • 这里一定要在 resultMap 将每一个字段和查询结果返回的字段对应上,否则此时的结果就为 null
<!-- 定义resultMap -->
<resultMaptype="cn.tedu.spring.entity.User"id="UserDepartment">
	<!-- 配置id的对应 -->
	<idcolumn="id"property="id"/>
	
	<!-- 配置其他字段的对应关系
		column: 查询结果中的列名字,如果没有起别名,那么就是表中的字段名
		property: java类中的属性名称
	 -->
	<resultcolumn="password"property="password"/>
	<resultcolumn="age"property="age"/>
	<resultcolumn="username"property="username"/>
	
	<!-- 配置对1的数据类型,即User类中的Department对象是单一的具体类型
		property: 这个是在User类中的字段名称
		javaType: 这个是java类的全名,是Department类的全名
	 -->
	<associationproperty="department"javaType="cn.tedu.spring.entity.Department">
		<idcolumn="id"property="id"/>
		<resultcolumn="name"property="name"/>
	</association>
    
</resultMap>

<!--
	User findUserAndDepartment();
	resultMap: 指定上面resultMap的id的值
 -->
 <selectid="findUserAndDepartment"resultMap="UserDepartment">
 	select *  from user u left join department d on u.department_id=d.id
 </select>
  • 测试方法
@Test
	public void testFindUserAndDepartment(){
		//加载Spring的配置文件
		AbstractApplicationContext ac
			= new ClassPathXmlApplicationContext(
				"spring-mvc.xml",
				"spring-dao.xml");
		
		//获取UserMapper的bean,这个是spring通过扫描mapper.xml文件自动为mybatis自动创建的,首字母小写
		UserMapper userMapper
			= ac.getBean(
				"userMapper", UserMapper.class);
		List<User> users=userMapper.findUserAndDepartment();
		for (User user : users) {
			System.out.println(user);
		}
		ac.close();
	}

一对多 的查询( <collection> )

  • 一个宿舍可以住多个学生,那么我们通过宿舍查询学生,那么就是 一对多 查询

创建Java

  • 创建 Student
public class Studentimplements Serializable{
	private static final long serialVersionUID = 8673238196042278929L;
	private Integer id;
	private String name;
	private Integer age;
	public Integer getId(){
		return id;
	}
	public void setId(Integer id){
		this.id = id;
	}
	public String getName(){
		return name;
	}
	public void setName(String name){
		this.name = name;
	}
	public Integer getAge(){
		return age;
	}
	public void setAge(Integer age){
		this.age = age;
	}
	@Override
	public int hashCode(){
		final int prime = 31;
		int result = 1;
		result = prime * result + ((age == null) ? 0 : age.hashCode());
		result = prime * result + ((id == null) ? 0 : id.hashCode());
		result = prime * result + ((name == null) ? 0 : name.hashCode());
		return result;
	}
	@Override
	public boolean equals(Object obj){
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Student other = (Student) obj;
		if (age == null) {
			if (other.age != null)
				return false;
		} else if (!age.equals(other.age))
			return false;
		if (id == null) {
			if (other.id != null)
				return false;
		} else if (!id.equals(other.id))
			return false;
		if (name == null) {
			if (other.name != null)
				return false;
		} else if (!name.equals(other.name))
			return false;
		return true;
	}
	@Override
	public String toString(){
		return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
	}
	
}
  • 创建 Dormitory
    • 其中有一个 Set<Student> 集合用来存储学生对象
public class Dormitoryimplements Serializable{
	private static final long serialVersionUID = 1359749532219773083L;
	private Integer id;
	private String number;  //编号
	private Set<Student> students;  //学生集合,一个宿舍可以住多个学生
	public Integer getId(){
		return id;
	}
	public void setId(Integer id){
		this.id = id;
	}
	public String getNumber(){
		return number;
	}
	public void setNumber(String number){
		this.number = number;
	}
	public Set<Student> getStudents(){
		return students;
	}
	public void setStudents(Set<Student> students){
		this.students = students;
	}
	@Override
	public int hashCode(){
		final int prime = 31;
		int result = 1;
		result = prime * result + ((id == null) ? 0 : id.hashCode());
		result = prime * result + ((number == null) ? 0 : number.hashCode());
		result = prime * result
				+ ((students == null) ? 0 : students.hashCode());
		return result;
	}
	@Override
	public boolean equals(Object obj){
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Dormitory other = (Dormitory) obj;
		if (id == null) {
			if (other.id != null)
				return false;
		} else if (!id.equals(other.id))
			return false;
		if (number == null) {
			if (other.number != null)
				return false;
		} else if (!number.equals(other.number))
			return false;
		if (students == null) {
			if (other.students != null)
				return false;
		} else if (!students.equals(other.students))
			return false;
		return true;
	}
	@Override
	public String toString(){
		return "Dormitory [id=" + id + ", number=" + number + ", students="
				+ students + "]";
	}
}

创建表

  • student
create table student(id int primary key auto_increment,name varchar(10) not null unique,age int,dormitory_id int);
  • dormitory
create table dormitory(id int primary key auto_increment,number varchar(20) not null unique);

创建DormitoryMapper.java(接口)

public interface DormitoryMapper{
	//查询所有的宿舍信息
	List<Dormitory>findDormitories();
	//根据id查询宿舍信息
	DormitoryfindDormitory(Integer id);
}

创建DormitoryMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
 "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<!-- MyBatis的接口映射文件,根节点是mapper -->
<!-- 接口映射文件是与Java接口文件(interface)相对应的 -->
<!-- 根节点的namespace属性用于指定Java接口文件 -->
<mappernamespace="cn.tedu.spring.mapper.DormitoryMapper">
	
	<!-- 定义resultMap
	 -->
	<resultMaptype="cn.tedu.spring.entity.Dormitory"id="DormitoryStudentRs">
		<idcolumn="id"property="id"/>
		<resultcolumn="number"property="number"/>
		
		<!-- 因为Dormitoy中的Student使用set集合存储的,因此这里使用collection标签
			property : Java类中的集合对象
			ofType: 集合对象的泛型类型
		 -->
		<collectionproperty="students"ofType="cn.tedu.spring.entity.Student">
			
			<idcolumn="id"property="id"/>
			<resultcolumn="name"property="name"/>
			<resultcolumn="age"property="age"/>
		</collection>
	</resultMap>
	
	
	<!-- List<Dormitory> findDormitories();
			查询所有的宿舍信息
			resultMap: 指定前面定义的resultMap
	 -->
	 
	 <selectid="findDormitories"resultMap="DormitoryStudentRs">
	 	select * from student s left join dormitory d on s.dormitory_id=d.id
	 </select>
	 
	 <!--
	 	Dormitory findDormitory(Integer id);
	  -->
	  <selectid="findDormitory"resultType="cn.tedu.spring.entity.Dormitory">
	  	select * from dormitory where id=#{id}
	  </select>
</mapper>

spring 配置文件中添加 DormitoryMapper.xml 文件

<!-- 配置SqlSessionFactoryBean -->
<beanclass="org.mybatis.spring.SqlSessionFactoryBean">
	<!-- 配置数据源:如何连接数据库等 -->
	<propertyname="dataSource"
		ref="dataSource" />
	<!-- 配置XML文件的位置,其中的值是一个数组 -->
	<propertyname="mapperLocations">
			<array>	
				<value>classpath:mappers/UserMapper.xml</value>
				<value>classpath:mappers/DormitoryMapper.xml</value>
			</array>
		</property>
</bean>

测试

@Test
public void testFindDormitory(){
	//加载Spring的配置文件
	AbstractApplicationContext ac
		= new ClassPathXmlApplicationContext(
			"spring-mvc.xml",
			"spring-dao.xml");
	//获取DormitoryMapper对象
	DormitoryMapper dormitoryMapper=ac.getBean("dormitoryMapper",DormitoryMapper.class);
	
       //执行查询方法
	List<Dormitory> dormitories=dormitoryMapper.findDormitories();
	for (Dormitory dormitory : dormitories) {
		System.out.println(dormitory);
	}
	
	ac.close();
}
原文  https://chenjiabing666.github.io/2018/05/04/Mybatis之多表查询/
正文到此结束
Loading...