MyBatis多对多一对多关系查询嵌套处理
目录
- 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)其它相关文章!
精彩评论