目录
- 1. 数据准备
- 数据库
- 实体类
- SQL语句
- 实体接口
- mapper.XML
- 2.一对多的关系
- 2.1 按查询嵌套处理
- 2.2 按结果嵌套处理
- 3. 多对一的关系
- 数据准备
- 3.1 按结果嵌套处理
- Mapper接口
- 3.2 按查询嵌套处理
1. 数据准备
数据库
student 数据库字段：
id int(10)
name varchar(30)
tid int(10)
teacher 数据库字符：
id int(10)
name varchar(30)
实体类
Student:
@Data
public class Student {
private int id;
private String name;
private Teacher teacher;
}
Teacher:
@Data
public class Teacher {
private int id;
private String name;
}
SQL语句
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`) HvbYeAotZzVALUES ('5', '小王', '1');
实体接口
编写实体类对应的Mapper接口 【两个】
public interface StudentMapper {
}
public interface TeacherMapper {
}
mapper.xml
编写Mapper接口对应的 mapper.xml配置文件
StudentMapper.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.kuang.mapper.StudentMapper">
</mapper>
TeacherMapper.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.kuang.mapper.TeacherMapper">
</mapper>
2.一对多的关系
多个学生对应一个老师.
2.1 按查询嵌套处理
1、给StudentMapper接口增加方法:
//获取所有学生及对应老师的信息 public List<Student> getStudents();
- 编写对应的Mapper文件:
<mapper namespace="com.dao.StudentMapper">
<!--
需求：获取所有学生及对应老师的信息
思路：
1. 获取所有学生的信息
2. 根据获取的学生信息的老师ID->获取该老师的信息
3. 思考问题，这样学生的结果集中应该包含老师，该如何处理呢，数据库中我们一般使用关联查询？
1. 做一个结果集映射：StudentTeacher
2. StudentTeacher结果集的类型为 Student
3. 学生中老师的属性为teacher，对应数据库中为tid。
多个 [1,...）学生关联一个老师=> 一对一，一对多
4. 查看官网找到：association – 一个复杂类型的关联；使用它来处理关联查询
-->
<select id="getStudents" resultMap="studentAllInfo" >
select * from student
</select>
<resultMap id="studentAllInfo" type="com.pojo.Student">
<result column="id" property="id" />
<result column="name" property="name" />
<!--association关联属性 property属性名 JavaType属性类型 column在多的一方的表中的列名-->
<association property="teacher" column="tid" javaType="com.pojo.Teacher" select="getTeacher" />
</resultMap>
<!--
这里传递过来的id，只有一个属性的时候，下面可以写任何值
association中column多参数配置：
column="{key=value,key=value}"
其实就是键值对的形式，key是传给下个sql的取值名称，value是片段一中sql查询的字段名。
-->
<select id="getTeacher" resultType="com.pojo.Teacher">
select * from teacher where id = #{tid}
</select>
</mapper>
2.2 按结果嵌套处理
- 接口方法编写:
public List<Student> getStudents2(); - StudentMapper.xml:
<select id="getStudents2" resultMap="STInfo">
select s.id sid, s.name sname, t.id tid, t.name tname
from student s, teacher t
where s.tid = t.id
</select>
<resultMap id="STInfo" type="com.pojo.Student">
<result property="id" column="sid"/>
<result property="name" column="sname" />
<asjavascriptsociation property="teacher" javaType="com.pojo.Teacher">
<result property="id" column="tid" />
<result property="name" column="tname" />
</association>
</resultMap>
Test:
@Test
public void testStudent01(){
SqlSession sqlsession = MyBatisUtils.getSqlSession();
StudentMapper studentMapper = sqlsession.getMapper(StudentMapper.class);
List<Student> listStudent = studentMapper.getStudents2();
for (Student student : listStudent) {
System.out.println(student);
}
}
3. 多对一的关系
一个老师对应多个学生
数据准备
Student
@Data
public class Student {
private int id;
private String name;
// www.devze.comprivate Teacher teacher;
private int tid;
}
Teacher
@Data
public class Teacher {
private int id;
private String name;
// 一个老师拥有多个学生
private List<Student> studentList;
}
3.1 按结果嵌套处理
Mapper接口
TeacherMapper接口编写方法：
//获取指定老师，及老师下的所有学生 public Teacher getTeacher(int id);
编写接口对应的Mapper配置文件
<mapper namespace="com.dao.TeacherMapper">
<!--
思路:
1. 从学生表和老师表中查出学生id，学生姓名，老师姓名
2. 对查询出来的操作做结果集映射
* 集合的话，使用collection！
* JavaType和ofType都是用来指定对象类型的
* JavaType是用来指定pojo中属性的类型
* ofType指定的是映射到list集合属性中pojo的类型。
-->
<select id="getTeacher" resultMap="TeacherStuent" >
select s.id sid, s.name sname, t.id tid, t.name tname from student s, teacher t
where s.tid = t.id and t.id = #{tid}
</select>
<resultMap id="TeacherStuent" type="com.pojo.Teacher">
<result property="id" column="tid" />
<result property="name" column="tname" />
<!--集合的话，使用collection
property : 对应着 Teacher类中的属性；
javaType: 指定属性的类型；
集合中的泛型，使用ofType获取
！-->
<collection property="studentList" ofType="com.pojo.Student">
<result property="id" column="sid" />
<result property="name" column="sname" />
<result property="tid" column="tid" />
</collection>
</resultMap>
</mapper>
Test:
@Test
public void testgetTeacher(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = teacherMapper.getTeacher(1);
System.out.println( teacher );
sqlSession.close();
}
}
3.2 按查询嵌套处理
数据准备
@Data
public class Student {
private int id;
private String name;
private int tid;
}
@Data
public class Teacher {
private int id;
private String name;
//一个老师多个学生
private List<Student> students;
}
TeacherMapper接口编写方法 :public Teacher getTeacher2(int id);
编写接口对应的Mapper配置文件
<mapper namespace="com.dao.TeacherMapper">
<select id="getTeacher2" resultMap="TeacherStuent">
select * from teacher where id = #{tid}
</select>
<resultMap id="TeacherStuent" type="com.pojo.Teacher">
<result property="id" column="id" />
<result property="name" column="name" />python
<!--column是一对多的外键 , 写的是一的主键的列名-->
<collection property="studentList" javaType="ArrayList" ofType="com.pojo.Student" select="getStudentByTId" column="id"/>
</resultMap>
<select id="getStudentByTId" resultType="com.pojo.Student">
select * from student where tid = #{id}
</select>
</mapper>
Test:
@TestHVbYeAotZz
public void testgetTeacher2(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = teacherMapper.getTeacher2(1);
System.out.println(teacher);
sqlSession.close();
}
以上就是MyBatis多对多一对多关系查询嵌套处理的详细内容，更多关于MyBatis多对多一对多嵌套查询的资料请关注编程客栈(www.devze.com)其它相关文章！
加载中,请稍侯......
精彩评论