mybatis OneToMany(一对多)查询

实际开发应用中存在很多一对多的关系，例如班级和学生。班级与学生是一对多的关系，学生与班级是多对一的关系。数据库中常使用多表关联的方式存储信息，方便多维维护。

由于配置文件以及封装获取SqlSession对象的FKSqlSessionFactory.java文件相同，可参考之前实例配置。

第一步：创建数据库表

CREATE TABLE tb_clazz(
   id INT PRIMARY KEY,
   code VARCHAR(18)
)

CREATE TABLE tb_student(
   id INT PRIMARY KEY,
   name VARCHAR(18),
   sex VARCHAR(18),
   age INT,
   clazz_id INT,
   FOREIGN KEY (clazz_id) REFERENCES tb_clazz(id)
)

第二步：创建与数据库表映射的对象类

/MyBatisRelationMapping/src/com/web/mybatis/domain/Clazz.java

 private Integer id;
    private String code;
    private List<Student> clazzlist;

/MyBatisRelationMapping/src/com/web/mybatis/domain/Student.java

private Integer id;
    private String name;
    private String sex;
    private Integer age;
    private Clazz clazz;

第三步：写mapper文件

/MyBatisRelationMapping/src/com/web/mybatis/mapper/ClazzMapper.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.web.mybatis.mapper.ClazzMapper">
    <!-- 根据id查询班级信息，返回resultMap -->
    <select id="selectClazzById" parameterType="int" resultMap="clazzResultMap">
        SELECT * FROM tb_clazz WHERE id=#{id}
    </select>
    <!-- 映射clazz对象的resultMap -->
    <resultMap type="com.web.mybatis.domain.Clazz" id="clazzResultMap">
        <id property="id" column="id"/>
        <result property="code" column="code"/>
        <!-- 一对多关联映射：collection fetchType="lazy"表示懒加载 -->
        <collection property="clazzlist" javaType="ArrayList" 
            column="id" ofType="com.web.mybatis.domain.Student"
            select="com.web.mybatis.mapper.StudentMapper.selectStudentByClazzId">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="sex" column="sex"/>
            <result property="age" column="age"/>
        </collection>
    </resultMap>
</mapper>

/MyBatisRelationMapping/src/com/web/mybatis/mapper/StudentMapper.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.web.mybatis.mapper.StudentMapper">
    <!-- 根据班级id查询学生信息，返回resultMap -->
    <select id="selectStudentByClazzId" parameterType="int" resultMap="studentResultMap">
        SELECT * FROM tb_student WHERE clazz_id=#{id}
    </select>
    <!-- 根据id查询学生信息，多表连接，返回resultMap -->
    <select id="selectStudentById" parameterType="int" resultMap="studentResultMap">
        SELECT * FROM tb_clazz c,tb_student s WHERE c.id=s.clazz_id AND s.id=#{id}
    </select>
    <!-- 映射Student对象的resultMap -->
    <resultMap type="com.web.mybatis.domain.Student" id="studentResultMap">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
        <!-- 多对一关联映射：association -->
        <association property="clazz" javaType="com.web.mybatis.domain.Clazz">
            <id property="id" column="id"/>
            <result property="code" column="code"/>
        </association>
    </resultMap>
</mapper>

第四步：创建与mapper文件对应的接口类

/MyBatisRelationMapping/src/com/web/mybatis/mapper/ClazzMapper.java

package com.web.mybatis.mapper;

import com.web.mybatis.domain.Clazz;

public interface ClazzMapper {
    Clazz selectClazzById(Integer id);
}

/MyBatisRelationMapping/src/com/web/mybatis/mapper/StudentMapper.java

package com.web.mybatis.mapper;

import com.web.mybatis.domain.Student;

public interface StudentMapper {
    Student selectStudentById(Integer id);
}

第五步：测试代码

package com.web.mybatis.test;
import org.apache.ibatis.session.SqlSession;
import com.web.mybatis.domain.Clazz;
import com.web.mybatis.domain.Student;
import com.web.mybatis.factory.FKSqlSessionFactory;
import com.web.mybatis.mapper.ClazzMapper;
import com.web.mybatis.mapper.StudentMapper;

public class Test {
    /**一对多
     * 查询班级级联查询学生
     * */
    public static void oneToManyTest() {
        SqlSession session = FKSqlSessionFactory.getSqlSession();
        //获得mapper接口的代理对象
        ClazzMapper cm = session.getMapper(ClazzMapper.class);
        Clazz clazz = cm.selectClazzById(1);
        System.out.println("班级："+clazz.toString());
        for(Student stu : clazz.getClazzlist()) {
            System.out.println("--学生："+stu.toString());
        }
        session.commit();
        session.close();
    }
    /**多对一
     * 查询学生级联查询班级
     * */
    public static void manyToOneTest() {
        SqlSession session = FKSqlSessionFactory.getSqlSession();
        //获得mapper接口的代理对象
        StudentMapper sm = session.getMapper(StudentMapper.class);
        Student stu = sm.selectStudentById(1);
        System.out.println(stu.toString()+stu.getClazz().toString());
        session.commit();
        session.close();
    }
    public static void main(String[] args) {
//        oneToManyTest();
        manyToOneTest();
    }

}

注意：本文归作者所有，未经作者允许，不得转载