转载

Mybatis一对多、多对一处理

数据库设计

我们用学生和老师的例子来说明：

一对多：一个老师有多个学生，转换为程序设计语言即为一个老师对象下有一个学生对象集合。

多对一：多个学生有同一个老师，学生对象里有一个属性关联着老师。

创建学生、教师表

CREATE TABLE `teacher`(
    `id` int(20) not null,
    `name` VARCHAR(30) DEFAULT NULL,
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO `teacher`(`id`,`name`) VALUES(1,'hresh');


CREATE TABLE `student`(
    `id` int(20) not null,
    `name` VARCHAR(30) DEFAULT null,
    `tid` int(20) default null,
    PRIMARY KEY (`id`),
    KEY `ftid` (`tid`),
    CONSTRAINT `ftid` 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);
复制代码

多对一处理

1、Student

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}
复制代码

Teacher

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;
}
复制代码

2、编写 Mapper 接口

StudentMapper.java 定义了两个查询方法，基于不同的实现方式。

public interface StudentMapper {
    List<Student> getStudent();
    List<Student> getStudent2();
}
复制代码

public interface TeacherMapper {

}
复制代码

3、编写 Mapper 接口对应的配置文件

关于 StudentMapper.xml 的书写有两种方式。

按查询嵌套处理

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.msdn.dao.StudentMapper">

    <select id="getStudent" resultMap="studentTeacher">
        select * from student
    </select>

     <resultMap id="studentTeacher" type="student">
         <id property="id" column="id" />
         <result property="name" column="name" />
         <!--复杂的属性需要单独处理，对象：association；集合：collection-->
         <!--association关联属性  property属性名 javaType属性类型 column在多的一方的表（即学生表）中的列名-->
         <association property="teacher" javaType="Teacher" column="tid" select="getTeacher" />
     </resultMap>

    <!--
       这里传递过来的id，只有一个属性的时候，下面可以写任何值
       association中column多参数配置：
       column="{key=value,key=value}"
       其实就是键值对的形式，key是传给下个sql的取值名称，value是片段一中sql查询的字段名。
   -->
     <select id="getTeacher" resultType="teacher">
         select * from teacher where id = #{tid}
     </select>

</mapper>
复制代码

关于 getTeacher 查询中的 tid，是随便命名的，如果该查询 SQL 有多个查询条件，则必须一致，如下所示：

<resultMap id="StudentTeacher" type="Student">
    <!--association关联属性  property属性名 javaType属性类型 column在多的一方的表中的列名-->
    <association property="teacher"  column="{id=tid,name=tid}" javaType="Teacher" select="getTeacher"/>
</resultMap>
<!--
这里传递过来的id，只有一个属性的时候，下面可以写任何值
association中column多参数配置：
    column="{key=value,key=value}"
    其实就是键值对的形式，key是传给下个sql的取值名称，value是片段一中sql查询的字段名。
-->
<select id="getTeacher" resultType="teacher">
    select * from teacher where id = #{id} and name = #{name}
</select>
复制代码

测试代码

    @Test
    public void getStudent(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = studentMapper.getStudent();
        for (Student student : students) {
            System.out.println(student);
        }
        sqlSession.close();
    }
复制代码

执行结果为：

Student(id=1, name=张三, teacher=Teacher(id=1, name=hresh))
Student(id=2, name=李四, teacher=Teacher(id=1, name=hresh))
Student(id=3, name=王武, teacher=Teacher(id=1, name=hresh))
Student(id=4, name=张散散, teacher=Teacher(id=1, name=hresh))
复制代码

按结果嵌套处理

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.msdn.dao.StudentMapper">


    <select id="getStudent2" resultMap="studentMap">
        select s.id sid,s.name sname,t.id ttid,t.name tname
        from student s,teacher t
        where s.tid=t.id;
    </select>

    <resultMap id="studentMap" type="student">
        <id property="id" column="sid" />
        <result property="name" column="sname" />
        <!--关联对象property 关联对象在Student实体类中的属性-->
        <association property="teacher" javaType="Teacher" >
            <id property="id" column="ttid" />
            <result property="name" column="tname" />
        </association>
    </resultMap>

</mapper>
复制代码

测试代码

    @Test
    public void getStudent2(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = studentMapper.getStudent2();
        for (Student student : students) {
            System.out.println(student);
        }
        sqlSession.close();
    }
复制代码

上述两种方式的执行效果是一致的，选择哪种方式根据个人理解，觉得哪种简单就使用哪种。

一对多的处理

1、实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    private int tid;
}
复制代码

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;
    private List<Student> students;
}
复制代码

2、Mapper 接口

public interface TeacherMapper {

    Teacher getTeacherById(@Param("id") int id);
    Teacher getTeacherById2(@Param("id") int id);
}
复制代码

3、TeacherMapper.xml

同多对一处理一样，这里也有两种实现方式。

按查询嵌套处理

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.msdn.dao.TeacherMapper">

    <select id="getTeacherById2" resultMap="TeacherStudent2">
        select * from mybatis.teacher where id = #{id}
    </select>

    <resultMap id="TeacherStudent2" type="Teacher">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudents" />
    </resultMap>

    <select id="getStudents" resultType="Student">
        select * from mybatis.student where tid = #{tid}
    </select>
</mapper>
复制代码

测试

    @Test
    public void getTeacher(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = teacherMapper.getTeacherById2(1);
        System.out.println(teacher);

        sqlSession.close();
    }
复制代码

执行结果为：

Teacher(id=1, name=hresh, students=[Student(id=1, name=张三, tid=1), Student(id=2, name=李四, tid=1), Student(id=3, name=王武, tid=1), Student(id=4, name=张散散, tid=1)])
复制代码

按结果嵌套处理

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.msdn.dao.TeacherMapper">

    <select id="getTeacherById" resultMap="TeacherStudent">
        select s.id sid,s.name sname,t.id tid,t.name tname
        from mybatis.student s,mybatis.teacher t
        where s.tid = t.id and t.id =#{id}
    </select>

    <resultMap id="TeacherStudent" type="Teacher">
        <id property="id" column="tid" />
        <result property="name" column="tname" />
        <collection property="students" ofType="Student" >
            <result property="id" column="sid" />
            <result property="name" column="sname" />
            <result property="tid" column="tid" />
        </collection>
    </resultMap>

</mapper>
复制代码

测试

    @Test
    public void getTeacher(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = teacherMapper.getTeacherById(1);
        System.out.println(teacher);

        sqlSession.close();
    }
复制代码