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();
}
正文到此结束
- 本文标签： list Word IO apache 数据库 IDE value http sqlsession java find 数据 key entity mapper spring UI XML dataSource SqlSessionFactory equals rmi https 配置 Select Java类 ORM session git map final bean id tab sql App 一对多 Property iBATIS classpath cat CTO mybatis ACE Collection 测试 学生 GitHub
- 版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
- 本文海报： 生成海报一 生成海报二
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
