9 一對多和多對一處理
多對一處理
多個學生對應一個老師
資料庫設計
CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO teacher(`id`, `name`) VALUES (1, '曾老師');
CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小紅', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小張', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');
搭建測驗環境
1、撰寫物體類
Student.java
package com.zzb.pojo;
public class Student {
private int id;
private String name;
private Teacher teacher;
public Student() {
}
public Student(int id, String name, Teacher teacher) {
this.id = id;
this.name = name;
this.teacher = teacher;
}
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 Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", teacher=" + teacher +
'}';
}
}
Teacher.java
package com.zzb.pojo;
public class Teacher {
private int id;
private String name;
public Teacher() {
}
public Teacher(int id, String name) {
this.id = id;
this.name = name;
}
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;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
2、撰寫物體類對應的Mapper介面
package com.zzb.dao;
public interface StudentMapper {
}
package com.zzb.dao;
public interface TeacherMapper {
}
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="com.zzb.dao.StudentMapper">
</mapper>
<?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="com.zzb.dao.TeacherMapper">
</mapper>
按查詢嵌套處理
1、給 StudentMapper 介面增加方法
// 獲取學生串列及對應老師的資訊
public List<Student> getStudents();
2、撰寫對應的Mapper組態檔
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zzb.dao.StudentMapper">
<!--按查詢嵌套-->
<select id="getStudents" resultMap="StudentTeacher">
select * from student;
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--association關聯屬性 property屬性名 javaType屬性型別 column在多的一方的表中的列名-->
<association property="teacher" column="tid" javaType="Teacher" select = "getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{tid}
</select>
</mapper>
3、MyBatis組態檔中注冊Mapper
<!--系結介面-->
<mappers>
<mapper />
</mappers>
注意：
<resultMap id="StudentTeacher" type="Student">
<!--association關聯屬性 property屬性名 javaType屬性型別 column在多的一方的表中的列名-->
<association property="teacher" column="{id = tid, name = name}" javaType="Teacher" select="getTeacher"/>
</resultMap>
<!--
這里傳遞過來的id，只有一個屬性的時候，下面可以寫任何值
association中column多引數配置：
column="{key=value,key=value}"
其實就是鍵值對的形式，key是傳給下個sql的取值名稱，value是sql查詢的欄位名，
-->
<select id="getTeacher" resultType="teacher">
select * from teacher where id = #{id} and name = #{name}
</select>
4、測驗
@Test
public void getStudent(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = studentMapper.getStudents();
for(Student student : studentList){
System.out.println(student);
}
sqlSession.close();
}
測驗結果：
Student{id=1, name='小明', teacher=Teacher{id=1, name='曾老師'}}
Student{id=2, name='小紅', teacher=Teacher{id=1, name='曾老師'}}
Student{id=3, name='小張', teacher=Teacher{id=1, name='曾老師'}}
Student{id=4, name='小李', teacher=Teacher{id=1, name='曾老師'}}
Student{id=5, name='小王', teacher=Teacher{id=1, name='曾老師'}}
按結果嵌套查詢
1、StudentMapper介面方法撰寫
public List<Student> getStudents2();
2、撰寫對應的StudentMapper.xml組態檔
<!--按結果嵌套-->
<select id="getStudents2" resultMap="StudentTeacher2">
SELECT S.id AS sid, s.name AS sname, t.id AS tid, t.name AS tname
FROM student AS s, teacher AS t
WHERE s.tid = t.id;
</select>
<resultMap id="StudentTeacher2" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
3、MyBatis組態檔中注冊Mapper
<mapper />
4、測驗
@Test
public void getStudent2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = studentMapper.getStudents2();
for(Student student : studentList){
System.out.println(student);
}
sqlSession.close();
}
測驗結果：
Student{id=1, name='小明', teacher=Teacher{id=1, name='曾老師'}}
Student{id=2, name='小紅', teacher=Teacher{id=1, name='曾老師'}}
Student{id=3, name='小張', teacher=Teacher{id=1, name='曾老師'}}
Student{id=4, name='小李', teacher=Teacher{id=1, name='曾老師'}}
Student{id=5, name='小王', teacher=Teacher{id=1, name='曾老師'}}
總結：
按照查詢進行嵌套處理就像SQL中的子查詢
按照結果進行嵌套處理就像SQL中的聯表查詢
一對多處理
module：mybatis-06
一個老師擁有多個學生
1、物體類撰寫
Student.java
package com.zzb.pojo;
// pom.xml中引入lombok包，簡化物體類的撰寫
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int tid;
}
Teacher.java
package com.zzb.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
和上述多對一一樣，搭建測驗環境！
按結果嵌套查詢
1、TeacherMapper介面撰寫方法
package com.zzb.dao;
import com.zzb.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
public interface TeacherMapper {
// 獲取指定老師下的所有學生
Teacher getTeacher2(@Param("tid") int id);
}
2、撰寫介面對應的Mapper組態檔
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--configuration-->
<mapper namespace="com.zzb.dao.TeacherMapper">
<!--按結果嵌套-->
<select id="getTeacher2" resultMap="TeacherStudent2">
SELECT S.id AS sid, s.name AS sname, t.id AS tid, t.name AS tname
FROM student AS s, teacher AS t
WHERE s.tid = t.id;
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
</mapper>
3、mybatis組態檔中注冊mapper
<!--系結介面-->
<mappers>
<mapper />
</mappers>
4、測驗
@Test
public void getTeacher2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(1);
System.out.println(teacher);
sqlSession.close();
}
測驗結果：
Teacher(id=1, name=曾老師, students=[Student(id=1, name=小明, tid=1), Student(id=2, name=小紅, tid=1), Student(id=3, name=小張, tid=1), Student(id=4, name=小李, tid=1), Student(id=5, name=小王, tid=1)])
按查詢嵌套
1、TeacherMapper介面撰寫方法
// 獲取指定老師下的所有學生
Teacher getTeacher(@Param("tid") int id);
2、撰寫介面對應的Mapper組態檔
<!--按查詢嵌套-->
<select id="getTeacher" resultMap="TeacherStudent">
select * from teacher
</select>
<resultMap id="TeacherStudent" type="teacher">
<result property="id" column="id"/>
<result property="name" column="name"/>
<collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudent"/>
</resultMap>
<select id="getStudent" resultType="Student">
select * from student where tid = #{id}
</select>
3、mybatis組態檔中注冊mapper
<!--系結介面-->
<mappers>
<mapper />
</mappers>
4、測驗
@Test
public void getTeacher(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = teacherMapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
測驗結果：
Teacher(id=1, name=曾老師, students=[Student(id=1, name=小明, tid=1), Student(id=2, name=小紅, tid=1), Student(id=3, name=小張, tid=1), Student(id=4, name=小李, tid=1), Student(id=5, name=小王, tid=1)])
總結：
1、關聯-association
2、集合-collection
3、所以association是用于一對一和多對一，而collection是用于一對多的關系
4、JavaType和ofType都是用來指定物件型別的
- JavaType是用來指定pojo中屬性的型別
- ofType指定的是映射到List集合屬性中pojo的型別，
注意說明：
1、保證SQL的可讀性，盡量通俗易懂
2、根據實際要求，盡量撰寫性能更高的SQL陳述句
3、注意屬性名和欄位不一致的問題
4、注意一對多和多對一 中：欄位和屬性對應的問題
5、盡量使用Log4j，通過日志來查看自己的錯誤
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/232910.html
標籤：其他
下一篇：10 動態SQL