Mybatis关联查询之一对多和多对一XML配置详解

2017年12月21日 10:53:05 esinsis 阅读数：72642

平时在开发过程中dao、bean和XML文件都是自动生成的，很少写XML的配置关系，今天记录一下mybatis的关联查询中的多对一和一对多的情况。

首先是有两张表(学生表Student和老师Teacher表)，为了更易懂，这里只设置了最简单的几个必要字段。表结构如下图

Student表：

Teacher表：

创建实体bean

Teacher.java:

 
import java.util.List;

/**

* TODO

* @version 创建时间：2017年12月21日上午9:02:45

*/

public class Teacher {

private Integer id;

private String name;

private String className;

private List<Student> students;

public List<Student> getStudents() {

return students;

}

public void setStudents(List<Student> students) {

this.students = students;

}

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 String getClassName() {

return className;

}

public void setClassName(String className) {

this.className = className;

}

}

Sfudent.java
 
/**

* TODO

*

* @author 作者 E-mail:2332999366@qq.com

* @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"/>

<result column="sname" property="name"/>

<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">



</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

public void getStudents() {

SqlSession session = sqlSessionFactory.openSession();

List<Student> list = session.selectList("com.tz.mybatis.dao.studentDao.getStudent");

System.out.println(list);

}

最后：当然如果不想配置这么麻烦的信息，可以直接写一个关联查询的SQL语句，返回结果直接由Map接受即可。不过这样就不太符合面向对象的理念了。