第七讲 关于联表的处理

一、多对一的处理（学生找老师）

1. 数据库表的设计

2. 实体类

public class Student {

     

     private int id;

     private String name;

     //多个学生对应一个老师

     private Teacher teacher;

     

     //下面省略getter、setter方法

public class Teacher {

     

     private int id;

     private String name;

     

     //下面省略getter、setter方法

3. 编写映射文件（student.mapper.xml）

          两种方式：

          结果嵌套处理（student.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.liujie.model.StudentMapper">

     <!--

           多对一处理有两种方式：

           1. 按结果嵌套处理

           2. 按查询嵌套处理

     -->

     <!-- 按结果嵌套处理 -->

     <select id="getStudents" resultMap="StudentTeacher">

     SELECT s.id sid, s.name sname, s.tid stid, t.id tid, t.name tname FROM student s, teacher t WHERE s.tid = t.id

     </select>

     <resultMap type="Student" id="StudentTeacher">

           <id column="sid" property="id"/>

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

           <!-- association标签，关联对象 。

                property，关联对象在Student实体类中的属性。

            -->

           <association property="teacher" javaType="Teacher">

                <id column="tid" property="id"/>

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

           </association>

     </resultMap>

</mapper>

          查询嵌套处理（student.mapper.xml 加上teacher.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.liujie.model.StudentMapper">

     <!--

           多对一处理有两种方式：

           1. 按结果嵌套处理

           2. 按查询嵌套处理

     -->

     <!-- 按查询嵌套处理 -->

     <select id="getStudents" resultMap="StudentTeacher">

           select * from student

     </select>

     <resultMap type="Student" id="StudentTeacher">

           <association property="teacher" column="tid" javaType="Teacher"

            select="com.liujie.model.TeacherMapper .getTeacher" >

           </association>

     </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.liujie.model.TeacherMapper ">

     <select id="getTeacher" resultType="Teacher">

           select * from teacher where id=#{id}

     </select>

</mapper>

4. 编写dao

public class StudentDao {

     

     public List<Student> getAll() throws IOException {

           SqlSession session = MyBatisUtil.getSqlSession();

           List<Student> list = session.selectList("com.liujie.model.StudentMapper.getStudents");

           session.close();

           return list;

     }

     

}

5. 测试

public class Test {

     

     public static void main(String[] args) throws IOException {

           

           StudentDao studentDao = new StudentDao();

           List<Student> list = studentDao.getAll();

           for(Student student : list) {

                System.out.println("student name=" + student.getName()

                + "  student's teacher name=" + student.getTeacher().getName());

           }

     }

     

}

二、一对多的处理（老师找学生）

1. 数据库表的设计（不变）

2. 实体类（改变）

public class Student {

     

     private int id;

     private String name;

     

     //下面省略getter、setter方法

public class Teacher {

     

     private int id;

     private String name;

     //一个老师对应多个学生

     private List<Student> students;

     

     //下面省略getter、setter方法

3. 编写映射文件（teacher.mapper.xml）

          两种方式：

          

          第一种（teacher.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.liujie.model.TeacherMapper">

     <select id="getTeacher" resultMap="TeacherStudent">

     SELECT s.id sid, s.name sname, s.tid stid, t.id tid, t.name tname FROM student s, teacher t WHERE s.tid = t.id

     AND t.id=#{id}

     </select>

     <resultMap type="Teacher" id="TeacherStudent">

           <id column="tid" property="id"/>

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

           <!-- collection标签中

                ofType：属性类型，如：Student student;

                javaType：集合类型，如：ArrayList，List;

                

                association标签中

                javaType：属性类型，如：Student student;

                没有javaType。

            -->

           <collection property="students" ofType="Student">

                <id column="sid" property="id"/>

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

           </collection>

     </resultMap>

</mapper>

          第二种（teacher.mapper.xml加上student.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.liujie.model.TeacherMapper">

     <select id="getTeacher" resultMap="TeacherStudent">

           select * from teacher where id = #{id}

     </select>

     <resultMap type="Teacher" id="TeacherStudent">

           <!-- column是所查列的名字，这里*表示id, name -->

           <collection property="students" javaType="ArrayList" ofType="Student" column="id"

            select="com.liujie.model.StudentMapper.getStudentByTid">

           </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.liujie.model.StudentMapper">

     <select id="getStudentByTid" resultType="Student">

           select * from student where tid = #{tid}

     </select>

</mapper>

4. 编写dao

public class TeacherDao {

     

     public Teacher getTeacher(int id) throws IOException {

           SqlSession session = MyBatisUtil.getSqlSession();

           Teacher teacher = session.selectOne("com.liujie.model.TeacherMapper.getTeacher", id);

           session.close();

           return teacher;

     }

     

}

5. 测试

public class Test {

     

     public static void main(String[] args) throws IOException {

           

           TeacherDao teacherDao = new TeacherDao();

           Teacher teacher = teacherDao.getTeacher(1);

           System.out.println("teacher name=" + teacher.getName());

           

           List<Student> list = teacher.getStudents();

           for(Student student : list) {

                System.out.println("student name=" + student.getName());

           }

     }

     

}

---

数据库中的表不管有没有关联，一段多或者多对一都能用以上的方法查询。