平時在開發過程中dao、bean和XML文件都是自動生成的，很少寫XML的配置關係，今天記錄一下mybatis的關聯查詢中的多對一和一對多的情況。 首先是有兩張表(學生表Student和老師Teacher表)，為了更易懂，這裡只設置了最簡單的幾個必要欄位。表結構如下圖: Student表： Te ...
平時在開發過程中dao、bean和XML文件都是自動生成的，很少寫XML的配置關係，今天記錄一下mybatis的關聯查詢中的多對一和一對多的情況。
首先是有兩張表(學生表Student和老師Teacher表)，為了更易懂，這裡只設置了最簡單的幾個必要欄位。表結構如下圖:
Student表：
Teacher表：
1 import java.util.List; 2 3 /** 4 * TODO 5 * @version 創建時間：2017年12月21日 上午9:02:45 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 作者 E-mail:[email protected] * @version 創建時間：2017年12月21日 上午9:01:17 */ 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+"}"; } }
下麵重點來了：配置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.tz.mybatis.dao.studentDao"> <!-- /////////////////////////////////一對多的第一種寫法，一般考慮到性能問題，不會這麼實現//////////////////////// --> <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"/><!-- 這裡的column對應的是下麵查詢的別名，而不是表欄位名 --> <result column="sname" property="name"/><!-- property對應JavaBean中的屬性名 --> <result column="className" property="className"/> </collection> </resultMap> <!-- 查詢所有的老師級各自的所有學生 --> <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>
請註意看註釋，很重要哦~
測試類：
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); } }
下麵給出第二種寫法：
<!-- //////////////////////////////////////////////一對多的第二種寫法///////////////////////////////////////////////////// --> <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> <!-- 查詢所有的老師級各自的所有學生 --> <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 public void getTeachers2() { SqlSession session = sqlSessionFactory.openSession(); List<Teacher> list = session.selectList("com.tz.mybatis.dao.studentDao.getAllTeacher"); System.out.println(list); }
查詢學生信息(多對一)：
首先還是配置文件：
<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"> <!-- 這裡要註意的是column對應的是student中的外鍵，而且需是表欄位名 --> </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>
測試類：
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 }
最後：當然如果不想配置這麼麻煩的信息，可以直接寫一個關聯查詢的SQL語句，返回結果直接由Map接受即可。不過這樣就不太符合面向對象的理念了。
