Generally, dao, bean and XML files are generated automatically in the development process, and rarely write the configuration relationship of XML. Today, record the many to one and one to many situations in the associated query of mybatis.
First, there are two tables (Student table and Teacher table). In order to be more understandable, only the simplest necessary fields are set here. The table structure is as follows:
Student table:
Teacher table:
Create entity bean:
Teacher.java:
1 import java.util.List; 2 3 /** 4 * TODO 5 * @version Creation time: 9:02:45 am, December 21, 2017 6 */ 7 public class Teacher { 8 9 private Integer id; 10 private String name; 11 private String className; 12 private List<Student> students; 13 14 public List<Student> getStudents() { 15 return students; 16 } 17 18 public void setStudents(List<Student> students) { 19 this.students = students; 20 } 21 22 public Integer getId() { 23 return id; 24 } 25 26 public void setId(Integer id) { 27 this.id = id; 28 } 29 30 public String getName() { 31 return name; 32 } 33 34 public void setName(String name) { 35 this.name = name; 36 } 37 38 public String getClassName() { 39 return className; 40 } 41 42 public void setClassName(String className) { 43 this.className = className; 44 } 45 46 }
Student.java
/** * TODO * * @author Author e-mail: 233299366@qq.com * @version Creation time: 9:01:17 am, December 21, 2017 */ public class Student { private Integer id; private String name; private Integer teacherId; private String className; private Teacher teacher; public Teacher getTeacher() { return teacher; } public void setTeacher(Teacher teacher) { this.teacher = teacher; } 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 getTeacherId() { return teacherId; } public void setTeacherId(Integer teacherId) { this.teacherId = teacherId; } public String getClassName() { return className; } public void setClassName(String className) { this.className = className; } @Override public String toString() { return "{id:"+this.id+",name:"+this.name+",className:"+this.className+",teacherId:"+this.teacherId+"}"; } }
Here's the point: configure the Mapper.xml file
<?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.tz.mybatis.dao.studentDao"> <!-- /////////////////////////////////The first way to write one to many, generally considering performance problems, will not be implemented in this way//////////////////////// --> <resultMap type="Teacher" id="teacherMap"> <id column="id" property="id"/> <result column="name" property="name"/> <collection property="students" ofType="Student" column="id"> <id column="sid" property="id"/><!-- There column It corresponds to the alias of the following query, not the table field name --> <result column="sname" property="name"/><!-- property Corresponding JavaBean Property name in --> <result column="className" property="className"/> </collection> </resultMap> <!-- Query all teachers and students --> <select id="getTeachers" parameterType="Teacher" resultMap="teacherMap"> SELECT t.id, t.NAME, t.class_Name, s.id AS sid, s. NAME AS sname, s.class_name as className FROM teacher t LEFT JOIN student s ON t.id = s.teacher_id </select> </mapper>
Please pay attention to the notes. It's very important~
Test class:
package com.tz.test; import java.io.IOException; import java.io.InputStream; import java.util.List; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Before; import org.junit.Test; import com.tz.mybatis.bean.Student; import com.tz.mybatis.bean.Teacher; public class TeacherTest { private SqlSessionFactory sqlSessionFactory; @Before public void init() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } @Test public void getTeachers() { SqlSession session = sqlSessionFactory.openSession(); List<Teacher> list = session.selectList("com.tz.mybatis.dao.studentDao.getTeachers"); System.out.println(list); } }
Here is a second way to write:
<!-- //////////////////////////////////////////////The second way of writing one to many///////////////////////////////////////////////////// --> <resultMap type="Teacher" id="teacherMaps"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="class_name" property="className"/> <collection property="students" ofType="Student" select="getStudents" column="id"> </collection> </resultMap> <!-- Query all teachers and students --> <select id="getAllTeacher" parameterType="Teacher" resultMap="teacherMaps"> SELECT t.id, t.NAME, t.class_name FROM teacher t </select> <select id="getStudents" parameterType="int" resultType="Student"> select s.id, s. NAME, s.class_name as className from student s where teacher_id = #{id} </select>
Test class:
@Test public void getTeachers2() { SqlSession session = sqlSessionFactory.openSession(); List<Teacher> list = session.selectList("com.tz.mybatis.dao.studentDao.getAllTeacher"); System.out.println(list); }
Query student information (many to one):
First, the configuration file:
<resultMap type="Student" id="studentMap"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="class_name" property="className"/> <result column="teacher_id" property="teacherId"/> <association property="teacher" select="getTeacher" column="teacher_id" javaType="Teacher"> <!-- What we should pay attention to here is column The corresponding is student Foreign key in, and it needs to be a table field name --> </association> </resultMap> <select id="getStudent" resultMap="studentMap"> SELECT s.id, s.name, s.class_name, s.teacher_id FROM student s </select> <select id="getTeacher" resultType="Teacher" parameterType="int"> SELECT t.id, t.name, t.class_name as className FROM teacher t where id = #{teacher_id} </select>
Test class:
1 @Test 2 public void getStudents() { 3 SqlSession session = sqlSessionFactory.openSession(); 4 List<Student> list = session.selectList("com.tz.mybatis.dao.studentDao.getStudent"); 5 System.out.println(list); 6 }
Finally: of course, if you don't want to configure such troublesome information, you can write a SQL statement of the associated query directly, and the return result can be directly accepted by Map. However, this is not in line with the concept of object-oriented.