一对一
的查询( <assocation>
)
创建Java类
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
+ "]";
}
}
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
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 + "]";
}
}
创建表
create table student(id int primary key auto_increment,name varchar(10) not null unique,age int,dormitory_id int);
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之多表查询/