Mybatis 实现多对一和一对多的数据库操作
Mybatis 实现多对一和一对多的数据库操作
第一步:建立对应数据库表:
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');
建立一个学生表并插入五个学生(注意创建和插入要分开执行)
第二步:建立pojo实体类:
Teacher:
package com.xiaofu.pojo; public class Teacher { private int id; private String name; //无参构造 public Teacher() { } //有参构造 public Teacher(int id, String name) { this.id = id; this.name = name; } //get set 方法 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; } //重写toString @Override public String toString() { return "Teacher{" + "id=" + id + ", name='" + name + '\'' + '}'; } }
Student:
package com.xiaofu.pojo; public class Student { private int id; private String name; //学生需要关联一个老师 所以这里封装一个Teacher类型 private Teacher teacher; //无参构造 public Student() { } //有参构造 public Student(int id, String name, Teacher teacher) { this.id = id; this.name = name; this.teacher = teacher; } //get set 方法 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; } //重写toString @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", teacher=" + teacher + '}'; } }
第三步:建立接口:
第四步:建立实现接口的Mapper.xml:
第五步:在核心配置文件中注册 写好的Mapper.xml:
第六步:实现多对一:
package com.xiaofu.dao; import com.xiaofu.pojo.Student; import java.util.List; public interface StudentMapper { //查询所有的学生信息及对应老师的信息(多对一) List<Student> getStudent(); }
在StudentMapper中编写一个接口来完成多对一的查询
然后在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.xiaofu.dao.StudentMapper"> <!-- 查询所有的学生信息及对应老师的信息(多对一)--> <!-- 第一种解决方式:按照查询嵌套处理--> <!-- 先查出所有的学生--> <select id="getStudent" resultMap="StudentTeacher"> select * from student </select> <!-- resultMap 结果集映射 这里的id 对应上面的resultMap --> <resultMap id="StudentTeacher" type="com.xiaofu.pojo.Student"> <result property="id" column="id"/> <!--模型类中的id(property)对应查出来的id(column)--> <result property="name" column="name"/><!--模型类中的name(property)对应查出来的name(column)--> <!--模型类中的teacher对应下面的查询语句 --> <!--为什么呢?因为column="tid" 被传到了下面查询语句的#{tid} javaType 确定了字段的类型 select="getTeacher" 执行了下面的查询语句 --> <association property="teacher" column="tid" javaType="com.xiaofu.pojo.Teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="com.xiaofu.pojo.Teacher"> select * from teacher where id = #{tid} </select> </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.xiaofu.dao.StudentMapper"> <!-- 查询所有的学生信息及对应老师的信息(多对一)--> <!-- 第二种解决方式:按照结果嵌套处理--> <select id="getStudent" resultMap="StudentTeacher"> select s.id sid,s.name sname,t.name tname from student s,teacher t where s.tid = t.id </select> <!-- resultMap 结果集映射 这里的id 对应上面的resultMap --> <resultMap id="StudentTeacher" type="com.xiaofu.pojo.Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <!-- 因为teacher对应的是一个对象 所以在映射对应的内容 --> <association property="teacher" javaType="com.xiaofu.pojo.Teacher"> <result property="name" column="tname"/> </association> </resultMap> </mapper>
编写测试类:
@Test public void getlist(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> list = mapper.getStudent(); for (Student student : list) { System.out.println(student); } sqlSession.close(); }
分别执行一下:
都查了出来。
第七步:一对多:
做一对多需要先更改一下实体类:
Sutdent:
package com.xiaofu.pojo; public class Student { private int id; private String name; //学生需要关联一个老师 所以这里封装一个Teacher类型 private int tid; public Student() { } public Student(int id, String name, int tid) { this.id = id; this.name = name; this.tid = tid; } 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 int getTid() { return tid; } public void setTid(int tid) { this.tid = tid; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", tid=" + tid + '}'; } }
Teacher:
package com.xiaofu.pojo; import java.util.List; public class Teacher { private int id; private String name; //一个老师拥有多个学生 private List<Student> students; public Teacher() { } public Teacher(int id, String name, List<Student> students) { this.id = id; this.name = name; this.students = students; } 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 List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } @Override public String toString() { return "Teacher{" + "id=" + id + ", name='" + name + '\'' + ", students=" + students + '}'; } }
在TecherMapper下写一个接口:
package com.xiaofu.dao; import com.xiaofu.pojo.Teacher; public interface TeacherMapper { //获取指定老师下的所有学生(及老师的信息和学生的信息) Teacher getTeacher(int id); }
在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.xiaofu.dao.TeacherMapper"> <select id="getTeacher" resultMap="TeacherStudent"> select s.id sid, s.name sname,t.name tname,t.id tid from student s, teacher t where s.tid = t.id and t.id = #{tid} </select> <resultMap id="TeacherStudent" type="com.xiaofu.pojo.Teacher"> <result property="id" column="tid"/> <result property="name" column="tname"/> <!--复杂的属性,我们需要单独处理 对象:association 集合:collection javaType=“ ” 指定属性类型! 集合中的泛型信息,我们使用ofType获取 --> <collection property="students" ofType="com.xiaofu.pojo.Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <result property="tid" column="tid"/> </collection> </resultMap> </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.xiaofu.dao.TeacherMapper"> <select id="getTeacher" resultMap="TeacherStudent"> select * from teacher where id = #{tid} </select> <resultMap id="TeacherStudent" type="com.xiaofu.pojo.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="com.xiaofu.pojo.Student"> select * from student where tid = #{tid}; </select> </mapper>
编写测试类:
@Test public void getlist(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); }
分别执行一下:
都查出来了