7.28--SSH学习之MyBatis多表关联查询

承接上个文章，实体类有Student表，Class表，Score表，Subject表，stusub表

在StudentMapper.xml映射文件中配置，需要注意的是mybatis配置关联关系没有单双向，另外在某一类映射文件中只配与该类有关的属性和关系，其他类不配

StudentMapper.xml

    <!-- 学生和班级相关联 -->
    <resultMap type="com.su.domain.Student" id="StudentWithClasses">
        <id column="stuid" property="stuId" />
        <result column="stuname" property="stuName"/>
        <result column="stugender" property="stuGender"/>
        <result column="stuage" property="stuAge"/>
        <result column="classesid" property="classesId"/>
        <association property="classes" javaType="com.su.domain.Classes">
            <id column="classesid" property="classesId" />
            <result column="classesname" property="classesName"/>
        </association>  
    </resultMap>

    <!-- 学生、班级、成绩，相关联 -->
    <resultMap type="com.su.domain.Student" id="StudentWithScore" extends="StudentWithClasses">
        <collection property="scoreList" ofType="com.su.domain.Score">
            <id column="scoreid" property="scoreId"/>
            <result column="stuid" property="stuId"/>
            <result column="subjectid" property="subjectId"/>
            <result column="score" property="score"/>
        </collection>
    </resultMap>

    <!-- 学生、班级、科目，相关联 -->
    <resultMap type="com.su.domain.Student" id="StudentWithSubject" extends="StudentWithClasses">
        <collection property="subjectList" ofType="com.su.domain.Subject">
            <id column="subjectid" property="subjectId"/>
            <result column="subjectname" property="subjectName"/>
            <result column="subjecthour" property="subjectHour"/>
        </collection>
    </resultMap>

<!-- 查询学生和班级 -->
    <select id="findStudentWithClasses" resultMap="StudentWithClasses">
        select student.*,classes.classesname from student,classes 
        where student.classesid = classes.classesid
    </select>

    <!-- 查询学生班级成绩 -->
    <select id="findStudentWithScore" resultMap="StudentWithScore">
        select student.*,classes.classesname,score.* from student,classes,score 
        where student.classesid = classes.classesid and score.stuid=student.stuid
    </select>

    <!-- 查询学生班级科目 -->
    <select id="findStudentWithSubject" resultMap="StudentWithSubject">
        select student.*,classes.classesname,subject.* from student,classes,subject,stusubtab
        where student.classesid = classes.classesid 
        and stusubtab.stuid = student.stuid and stusubtab.subjectid = subject.subjectid
    </select>

在StudentMapper.java接口中需要配置方法

import java.util.List;

import com.su.domain.Student;

public interface StudentMapper {

    public List<Student> findAllStudents();

    public Student findStudentById(Integer stuId);

    public void addStudent(Student stu);

    public void updateStudent(Student stu);

    public void deleteStudent(Integer stuId);

    public List<Student> findStudentWithClasses();

    public List<Student> findStudentWithScore();

    public List<Student> findStudentWithSubject();
}

因为是在Student的映射文件中配置，所以只配了以Student为主体的属性，例如：Score,Subject.Classes，都在Student下的标签。

Student.java

package com.su.domain;

import java.util.ArrayList;
import java.util.List;

public class Student {
    private Integer stuId;
    private String stuName;
    private String stuGender;
    private Integer stuAge;
    private Integer classesId;

    private Classes classes;
    private List<Score> scoreList = new ArrayList<Score>();
    private List<Subject> subjectList = new ArrayList<Subject>();

    public List<Score> getScoreList() {
        return scoreList;
    }
    public void setScoreList(List<Score> scoreList) {
        this.scoreList = scoreList;
    }
    public List<Subject> getSubjectList() {
        return subjectList;
    }
    public void setSubjectList(List<Subject> subjectList) {
        this.subjectList = subjectList;
    }
    public Classes getClasses() {
        return classes;
    }
    public void setClasses(Classes classes) {
        this.classes = classes;
    }
    public Integer getClassesId() {
        return classesId;
    }
    public void setClassesId(Integer classesId) {
        this.classesId = classesId;
    }
    public Integer getStuId() {
        return stuId;
    }
    public void setStuId(Integer stuId) {
        this.stuId = stuId;
    }
    public String getStuName() {
        return stuName;
    }
    public void setStuName(String stuName) {
        this.stuName = stuName;
    }
    public String getStuGender() {
        return stuGender;
    }
    public void setStuGender(String stuGender) {
        this.stuGender = stuGender;
    }
    public Integer getStuAge() {
        return stuAge;
    }
    public void setStuAge(Integer stuAge) {
        this.stuAge = stuAge;
    }
}

测试类：

package com.su.test;

import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.su.domain.Student;
import com.su.domain.Subject;
import com.su.mapper.StudentMapper;

public class TestStudentMapper {

    public static void main(String[] args) {
        try{
            //加载配置文件
            InputStream is = Resources.getResourceAsStream("mybatisconfig.xml");
            //创建SqlSessionFactory对象
            SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(is);
            //创建SqlSession
            SqlSession ss = ssf.openSession();

            StudentMapper sm = ss.getMapper(StudentMapper.class);

            //查询全部学生
//          List<Student> list = sm.findAllStudents();
//          for(Student stu : list){
//              System.out.println("名字："+stu.getStuName()+"\t++++年龄："+stu.getStuAge());
//          }

            //关联查询学生和班级
//          List<Student> stulist = sm.findStudentWithClasses();
//          for(Student stu: stulist){
//              System.out.println(stu.getStuName()+"\t"+stu.getClasses().getClassesName());
//          }

            //关联查询学生班级成绩
//          List<Student> stulist = sm.findStudentWithScore();
//          for(Student stu : stulist){
//              System.out.println(stu.getStuName()+"\t"+stu.getClasses().getClassesName());
//              for(Score se : stu.getScoreList()){
//                  System.out.println(se.getSubjectId()+"\t"+se.getScore());
//              }
//          }

            //关联查询学生班级科目
            List<Student> stulist = sm.findStudentWithScore();
            for(Student stu : stulist){
                System.out.println(stu.getStuName()+"\t"+stu.getClasses().getClassesName());
                for(Subject sb : stu.getSubjectList()){
                    System.out.println(sb.getSubjectName()+"\t"+sb.getSubjectHour());
                }
            }   
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}

今天复习内容，供以后复习

---

Author:su1573