Mybatis 多表关联查询（2） one-to-many关系

Mybatis 多表关联查询（2） one-to-many关系

本文中未说明的都在多表关联查询（1）中有说明。

基础文件

• 在mybatis- config.xml文件加入两个映射文件

<mapper resource="com/adoken/mapper/ClassesMapper.xml"/>
<mapper resource="com/adoken/mapper/StudentMapper.xml"/>

• one2many.sql（数据库脚本）
  1个班级N个学生

DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS CLASSES;

CREATE TABLE CLASSES(
    ID INT PRIMARY  KEY AUTO_INCREMENT,
     NAME VARCHAR(20) NOT NULL
) ;
INSERT INTO CLASSES (NAME) VALUES ("JAVA班");
INSERT INTO CLASSES (NAME) VALUES ("IOS班");

 CREATE TABLE STUDENT(
     ID INT PRIMARY  KEY AUTO_INCREMENT,
     NAME VARCHAR(20) NOT NULL,
     C_ID  INT NOT NULL,
      FOREIGN KEY(C_ID) REFERENCES CLASSES(ID)
);

INSERT INTO STUDENT (NAME,C_ID) VALUES ("张三","1");
INSERT INTO STUDENT (NAME,C_ID) VALUES ("李四","1");
INSERT INTO STUDENT (NAME,C_ID) VALUES ("王五","2");
INSERT INTO STUDENT (NAME,C_ID) VALUES ("赵六","2");

• 创建实体类
  Student.java

package com.adoken.bean;

public class Student {
    private Integer id;
    private String name;
    private Classes classes;//关联属性


    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 Classes getClasses() {
        return classes;
    }

    public void setClasses(Classes classes) {
        this.classes = classes;
    }
}

Classes.java

package com.adoken.bean;

import java.util.List;
public class Classes {
    private Integer id;
    private String name;
    private List<Student> studentList;关联属性

    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 List<Student> getStudentList() {
        return studentList;
    }

    public void setStudentList(List<Student> studentList) {
        this.studentList = studentList;
    }
}

• mapper.xml文件（核心，请先阅读下面说明）

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.adoken.mapper.StudentMapper">
    <resultMap id="studentMap" type="com.adoken.bean.Student">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="classes" javaType="com.adoken.bean.Classes">
            <id property="id" column="cid"/>
            <result property="name" column="cname"/>
        </association>
    </resultMap>

    <select id="findStudent" resultMap="studentMap">
        SELECT
            C.ID   AS CID,
            C.NAME AS CNAME,
            S.ID   AS SID,
            S.NAME AS SNAME
        FROM CLASSES C, STUDENT S
        WHERE C.ID = S.C_ID
              AND C.NAME = #{NAME}
    </select>
</mapper>

ClassesMapper.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.adoken.mapper.ClassesMapper">
    <resultMap id="classesMap" type="com.adoken.bean.Classes">
        <id property="id" column="cid"/>
        <result property="name" column="cname"/>
        <collection property="studentList" resultMap="com.adoken.mapper.StudentMapper.studentMap"/>
    </resultMap>

    <select id="find" resultMap="classesMap">
       SELECT
        C.ID AS CID,
        C.NAME AS CNAME,
        S.ID AS SID ,
        S.NAME AS SNAME
        FROM CLASSES C ,STUDENT S
        WHERE C.ID = S.C_ID
        AND S.NAME =#{NAME }
    </select>
</mapper>

测试

package com.adoken.dao.impl;

import com.adoken.bean.Classes;
import com.adoken.bean.Student;
import com.adoken.dao.ClassesDao;
import com.adoken.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class ClassesDaoImpl extends BaseDaoImpl implements ClassesDao {
    public static void main(String[] args) {
        /*查询班级有哪些学生 */
        List<Student> studentList = find2("JAVA班");
        for (Student s : studentList) {
            System.out.println(s.getClasses().getName() + " ：" + s.getName());
        }


        /*查询张三在哪一个班 */
        Classes classes = find("张三");
        System.out.println(classes.getStudentList().get(0).getName() + " 在 " + classes.getName());
    }

    /*查询张三在哪一个班 */
    public static Classes find(String name) {
        SqlSession session = null;
        try {
            session = MybatisUtil.getSession();
            return session.selectOne("com.adoken.mapper.ClassesMapper.find", name);
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            MybatisUtil.closeSession(session);
        }
    }

    /**
     * 查询班级有哪些学生
     *
     * @param name
     * @return
     */
    public static List<Student> find2(String name) {
        SqlSession session = null;
        try {
            session = MybatisUtil.getSession();
            return session.selectList("com.adoken.mapper.StudentMapper.find", name);
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            MybatisUtil.closeSession(session);
        }
    }
}

说明

1. 注意我的数据库主键都是id，name都是相同名称的字段，所以使用别名，否则查出来的数据有问题。（应该是匹配机制问题）。
2. ClassesMapper.xml中 studentList 使用 collection 标签，1对1使用 association 标签
3. 测试中是属于双向的，两边查都可以。
4. 如果查询数据数据只需要同一张表的字段，就不需要collection 或association 标签