MyBatis 一对多关联查询

MyBatis 一对多关联查询

sqlxml文件

<?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="ClassStudent">

    <!-- 1.联表查询SELECT * from class c ,teacher t where c.t_id = t.t_id and c.c_id=1 -->
    <select id="getClass" parameterType="int" resultMap="getClassMap">
        SELECT *
        from class c
        ,student s where c.c_id = s.c_id and c.c_id = #{id}
    </select>
    <resultMap type="com.stone.bean.ClassT" id="getClassMap">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <collection property="list" ofType="com.stone.bean.Student">
            <id property="id" column="s_id" />
            <result property="name" column="s_name" />
        </collection>
    </resultMap>
    <!-- 2.查询两次select * from class where c_id = 1 ; SELECT * from teacher where 
        t_id = 1 -->

    <select id="getClass2" resultMap="getClass2Map">
        select * from class where c_id
        =#{id}
    </select>
    <select id="getTeacher" parameterType="int" resultType="com.stone.bean.Teacher">
        select
        t_id id,t_name name
        from teacher where t_id=#{id}
    </select>
    <select id="getStudents" parameterType="int" resultType="com.stone.bean.Student">
        select
        S_id id,s_name name from student where c_id=#{id}
    </select>
    <resultMap type="com.stone.bean.ClassT" id="getClass2Map">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="t_id" select="getTeacher">
        </association>
        <collection property="list" column="c_id" select="getStudents"></collection>
    </resultMap>
　　<!--collection 可以进行嵌套-->
　　<resultMap type="person" id="selectMulti" extends="BaseResultMap">
　　　　<collection property="orderList" ofType="Orders">
　　　　　　<id ...>
　　　　　　<result ...>
　　　　　　<collection property="detailList" ofType="OrderDetail">
　　　　　　　　<id ...>
　　　　　　　　<result ...>
　　　　　　</collection>
　　　　</collection>
　　</resultMap>
</mapper>

 

javabean

package com.stone.bean;

import java.util.List;

public class ClassT {

    private int id;
    private String name;
    private Teacher teacher;
    private List<Student> list;
    
    
    public ClassT(int id, String name, Teacher teacher) {
        super();
        this.id = id;
        this.name = name;
        this.teacher = teacher;
    }
    public ClassT() {
        super();
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Teacher getTeacher() {
        return teacher;
    }
    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
    @Override
    public String toString() {
        return "ClassT [id=" + id + ", name=" + name + ", teacher=" + teacher
                + ", list=" + list + "]";
    }
    public List<Student> getList() {
        return list;
    }
    public void setList(List<Student> list) {
        this.list = list;
    }

    
}

package com.stone.bean;

public class Student {

    private int id;
    private String name;
    public Student(int id, String name) {
        super();
        this.id = id;
        this.name = name;
    }
    public Student() {
        super();
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + "]";
    }
    
}

package com.stone.bean;

public class Teacher {
    private int id;
    private String name;
    public Teacher(int id, String name) {
        super();
        this.id = id;
        this.name = name;
    }
    public Teacher() {
        super();
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Override
    public String toString() {
        return "Teacher [id=" + id + ", name=" + name + "]";
    }
    
}

 

test

package com.stone.dao;

import org.apache.ibatis.session.SqlSession;

import com.stone.bean.ClassT;
import com.stone.db.DBAccess;

public class DBDaoClassStudent {

    public static void main(String[] args) {
        DBAccess dbAccess = new DBAccess();
        SqlSession sqlSession = null;
        try {
            sqlSession = dbAccess.getSqlSession();
            String statement = "ClassStudent.getClass";
            Object parameter = 1;
            // 通过sqlSession执行SQL语句；
            ClassT class1 = sqlSession.selectOne(statement, parameter);
            System.out.println(class1);
            System.out.println("=======================");
            statement = "ClassStudent.getClass2";
            ClassT class2 = sqlSession.selectOne(statement, parameter);
            System.out.println(class2);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }

}