MyBatis 一对多和多对一关联查询

首先  数据库量表之间字段关系（没有主外键）

studentmajor表的id字段对应student表里major字段

两个实体类:Student,StudentMajor

package maya.model;

public class Student {
    private Integer sno;
    private String sname;
    private String ssex;
    private Integer sclass;
    private Integer mark;
    //private Integer major;
    private StudentMajor studentmajor;
    public Student() {
        super();
    }
    

    public Student(Integer sno, String sname, String ssex, Integer sclass, Integer mark, StudentMajor studentmajor) {
        super();
        this.sno = sno;
        this.sname = sname;
        this.ssex = ssex;
        this.sclass = sclass;
        this.mark = mark;
        this.studentmajor = studentmajor;
    }

    
    public Integer getSno() {
        return sno;
    }

    public void setSno(Integer sno) {
        this.sno = sno;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getSsex() {
        return ssex;
    }

    public void setSsex(String ssex) {
        this.ssex = ssex;
    }

    public Integer getSclass() {
        return sclass;
    }

    public void setSclass(Integer sclass) {
        this.sclass = sclass;
    }

    public Integer getMark() {
        return mark;
    }

    public void setMark(Integer mark) {
        this.mark = mark;
    }
    
    public StudentMajor getStudentmajor() {
        return studentmajor;
    }


    public void setStudentmajor(StudentMajor studentmajor) {
        this.studentmajor = studentmajor;
    }


    @Override
    public String toString() {
        return "Student [sno=" + sno + ", sname=" + sname + ", ssex=" + ssex + ", sclass=" + sclass + ", mark=" + mark
                + ", studentmajor=" + studentmajor.getMname() + "]";
    }
    
}

package maya.model;

import java.util.List;

public class StudentMajor {
    private Integer id;
    private String mcode;
    private String mname;
    private List<Student> students;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getMcode() {
        return mcode;
    }
    public void setMcode(String mcode) {
        this.mcode = mcode;
    }
    public String getMname() {
        return mname;
    }
    public void setMname(String mname) {
        this.mname = mname;
    }
    public List<Student> getStudents() {
        return students;
    }
    public void setStudents(List<Student> students) {
        this.students = students;
    }
    public StudentMajor(Integer id, String mcode, String mname, List<Student> students) {
        super();
        this.id = id;
        this.mcode = mcode;
        this.mname = mname;
        this.students = students;
    }
    public StudentMajor() {
        super();
    }
    @Override
    public String toString() {
        return "StudentMajor [id=" + id + ", mcode=" + mcode + ", mname=" + mname + ", students=" + students + "]";
    }
    
}

定义两个接口:StudentMapper,StudentMajorMapper

package maya.dao;

import java.util.List;

import maya.model.Student;

/*
 * 学生信息操作接口
 */
public interface StudentMapper {    
    /**
     * 全表查询
     * @return
     */
    public List<Student> selectAll();
    /**
     * 根据专业查人员，给一对多用
     * @param id
     * @return
     */
    public List<Student> selectStudentByMajorId(Integer major);
}

package maya.dao;

import java.util.List;

import maya.model.StudentMajor;
/**
 * 专业表数据库操作接口
 * @author User
 *
 */
public interface StudentMajorMapper {
    /**
     * 全表查询
     * @return
     */
    public List<StudentMajor> selectAll();
    /**
     * 根据主键查数据，给多对一用
     * @param id
     * @return
     */
    public StudentMajor selectMajorById(Integer id);
}

定义两个实体类的映射方法StudentMapper.xml,StudentMajorMapper.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="maya.dao.StudentMapper">
    <!-- 多对一查询  -->
    <resultMap type="student" id="stuList">
        <!-- association表示查单条记录，跟一对一一样用association标签,实体类定义的成员,要跟数据库字段名对应上 -->
        <association property="studentmajor" column="major"        
        select="maya.dao.StudentMajorMapper.selectMajorById">
        </association><!-- 用接口里定义的方法，根据student表中的major字段查出对应数据 -->
    </resultMap>
    
    <!-- 根据专业查人员  -->
    <select id="selectStudentByMajorId" parameterType="Integer" resultMap="stuList">
        select * from student s where s.major=#{major}
    </select>
    <!-- 查全部 -->
    <select id="selectAll" resultMap="stuList">
        select * from student
    </select>
</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="maya.dao.StudentMajorMapper">
    <!-- 一对多查询关联  -->    
    <resultMap type="StudentMajor" id="smlist">
        <!-- property的id是实体类中的属性，column的id是数据库表中字段的id -->
        <id property="id" column="id"/>
        <!-- 查集合用collection。students是实体类的属性，id是数据库表中的主键，往下一行方法中传值 -->
        <collection property="students" column="id" 
        select="maya.dao.StudentMapper.selectStudentByMajorId"/>
    </resultMap>
    <!-- 全表查询 -->
    <select id="selectAll" resultMap="smlist">
        select * from StudentMajor
    </select>
    <!-- 根据主键查 -->
    <select id="selectMajorById" parameterType="Integer" resultMap="smlist">
        select * from studentmajor sm where sm.id=#{id}
    </select>
</mapper>

JUnit测试

package maya.util;

import static org.junit.Assert.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import maya.dao.StudentMapper;
import maya.model.Student;

public class StudentJUnit {
    private SqlSession ss;
    private StudentMapper sm;
    
    @Test
    public void selectStudentByMajorId() {
        List<Student> list = sm.selectStudentByMajorId(2);
        for (Student s : list) {
            System.out.println(s);
        }
    }
    
    public void selectStudentAll() {
        List<Student> list = sm.selectAll();
        for (Student s : list) {
            System.out.println(s);
        }
    }
    
    @Before
    public void setUp() throws Exception {
        ss = MybatisSqlSessionFactoryUtil.getSqlSession();
        sm = ss.getMapper(StudentMapper.class);
    }

    @After
    public void tearDown() throws Exception {
        ss.commit();
        ss.close();
    }
}

package maya.util;

import static org.junit.Assert.*;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import maya.dao.StudentMajorMapper;
import maya.model.StudentMajor;

public class StudentMajorJUnit {
    
    private SqlSession ss;
    private StudentMajorMapper smm;
    
    @Test
    public void selectMajorById() {
        StudentMajor sm = smm.selectMajorById(3);
        System.out.println(sm);
    }
    public void selectAll() {
        List<StudentMajor> list = smm.selectAll();
        for (StudentMajor sm : list) {
            System.out.println(sm);
        }
    }    
    
    
    @Before
    public void setUp() throws Exception {
        ss = MybatisSqlSessionFactoryUtil.getSqlSession();
        smm = ss.getMapper(StudentMajorMapper.class);
    }

    @After
    public void tearDown() throws Exception {
        ss.commit();
        ss.close();
    }    

}

一对多查询结果

StudentMajor [id=3, mcode=h-003, mname=Python, students=[Student [sno=18, sname=小精灵, ssex=女, sclass=403, mark=27, studentmajor=Python], Student [sno=11, sname=露娜, ssex=女, sclass=402, mark=21, studentmajor=Python]]]

多对一查询结果

Student [sno=16, sname=痛苦女王, ssex=女, sclass=402, mark=21, studentmajor=C++]
Student [sno=17, sname=莉娜, ssex=女, sclass=401, mark=22, studentmajor=C++]
Student [sno=9, sname=宙斯, ssex=男, sclass=403, mark=23, studentmajor=C++]
Student [sno=12, sname=风行者, ssex=女, sclass=401, mark=22, studentmajor=C++]