Mybatis一對一及一對多級聯

讀《深入淺出Mybatis技術原理與實戰》總結

業務模型如下：實體有三個，分别是學生、學生證、課程。

學生和學生證的關系是一對一，學生和課程為一對多，學生選課時會出現成績，課程和成績為一對一。

資料庫表

在資料庫中額外有學生課程表，表明學生選擇的課程及成績，表現為四張表：t_student、t_lecture、t_student_selfcard、t_student_lecture。

Javabean

學生：

public class Student {
    private long id;
    private String studentName;
    // 性别枚舉類，具體請參考上一篇部落格
    private Sex sex;
    // 學生證号碼
    private int selfcardNo;
    private String note;
//    儲存學生證号bean 一對一
    private StudentSelfcard studentSelfcard;
    // 儲存學生課程表 一對多
    private List<StudentLecture> studentLectureList = new ArrayList<StudentLecture>();
}
           

課程：

public class Lecture {
    private int id;
    private String lectureName;
    private String note;
}
           

學生課程:

public class StudentLecture {
    private int id;
    private int stuId;
    // 儲存課程資訊
    private Lecture lecture;
    private BigDecimal grade;
    private String note;
}
           

學生證：

public class StudentSelfcard {
    private int id;
    private int studentId;
    // 籍貫
    private String stuNative;
    // 發證日期
    private Date issueDate;
    private Date endDate;
    private String note;
}
           

Mapper接口

public interface LectureMapper {
    Lecture findLectureById(int id);
}
public interface StudentMapper {
    public Student findStudentById(int stuId);
}
public interface StudentSelfcardMapper {
    StudentSelfcard findStudentSelfCardByStudentId(int stuId);
}
public interface StudentLectureMapper {
    StudentLecture findStudentLectureByStuId(int id);
}
           

Mybatis配置檔案

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <!--引入資料庫資訊配置檔案-->
    <properties resource="dbconfig.properties">
    </properties>

    <settings>
        <!--駝峰命名方式-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--定義别名，也可以自動掃描-->
    <typeAliases>
        <!--<typeAlias type="com.lrx.model.Student" alias="student"></typeAlias>-->
        <!--自動掃描别名-->
        <package name="com.lrx.model"></package>
    </typeAliases>

    <!--注冊類型處理器-->
    <typeHandlers>
        <typeHandler handler="com.lrx.dao.MyStringTypeHandler" javaType="java.lang.String" jdbcType="VARCHAR"/>
        <typeHandler handler="com.lrx.dao.SexEnumTypeHandler"
                     javaType="com.lrx.model.Sex" jdbcType="INTEGER"/>
    </typeHandlers>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"></property>
                <property name="url" value="${jdbc.url}"></property>
                <property name="username" value="${jdbc.username}"></property>
                <property name="password" value=""></property>
            </dataSource>
        </environment>
    </environments>
    <!--定義Mapper-->
    <mappers>
        <mapper resource="mapper/StudentMapper.xml"/>
        <mapper resource="mapper/StudentSelfCard.xml"/>
        <mapper resource="mapper/LectureMapper.xml"/>
        <mapper resource="mapper/StudentLectureMapper.xml"/>
    </mappers>

</configuration>
           

Mapper配置檔案

課程：

<mapper namespace="com.lrx.dao.LectureMapper">

    <select id="findLectureById" parameterType="int" resultType="com.lrx.model.Lecture">
        SELECT id, lecture_name, note
        FROM t_lecture WHERE id = #{id}
    </select>
</mapper>
           

學生證：

<mapper namespace="com.lrx.dao.StudentSelfcardMapper">
    <resultMap id="studentSelfCardMap" type="com.lrx.model.StudentSelfcard">
        <id column="id" property="id"></id>
        <result column="student_id" property="studentId"/>
        <result column="native" property="stuNative"/>
        <result column="issue_date" property="issueDate"/>
        <result column="end_date" property="endDate"/>
        <result column="note" property="note"/>
    </resultMap>

    <select id="findStudentSelfCardByStudentId" parameterType="int" resultMap="studentSelfCardMap">
        SELECT id, student_id, native, issue_date, end_date, note
        FROM t_student_selfcard WHERE student_id = #{stuId}
    </select>
</mapper>
           

學生課程：

<mapper namespace="com.lrx.dao.StudentLectureMapper">
    <resultMap id="studentLectureResultMap" type="com.lrx.model.StudentLecture">
        <id column="id" property="id"/>
        <result column="student_id" property="stuId"/>
        <result column="selfcard_no" property="studentName"/>
        <result column="grade" property="grade"/>
        <result column="note" property="note"/>
        <!--級聯屬性，通過資料庫中的課程id列查詢課程-->
        <association column="lecture_id" property="lecture" select="com.lrx.dao.LectureMapper.findLectureById"/>
    </resultMap>

    <!--查詢學生成績表-->
    <select id="findStudentLectureByStuId" parameterType="int" resultMap="studentLectureResultMap">
        SELECT id, student_id, lecture_id, grade, note
        FROM t_student_lecture WHERE id = #{id}
    </select>
</mapper>
           

學生：

<mapper namespace="com.lrx.dao.StudentMapper">
    <resultMap id="findStudentByIdMap" type="com.lrx.model.Student">
        <id column="id" property="id"/>
        <result column="student_name" property="studentName"
            typeHandler="com.lrx.dao.MyStringTypeHandler"/>
        <!--自定義的類型處理器，處理資料庫中integer類型和Java的枚舉類型的轉換-->
        <result column="sex" property="sex"
                typeHandler="com.lrx.dao.SexEnumTypeHandler"/>

        <result column="selfcard_no" property="studentName"/>
        <result column="note" property="note"/>
        <!--級聯屬性，學生證-->
        <association property="studentSelfcard" column="id"
                     select="com.lrx.dao.StudentSelfcardMapper.findStudentSelfCardByStudentId"/>
        <!--一對多的級聯，學生成績-->
        <collection column="id" property="studentLectureList"
                    select="com.lrx.dao.StudentLectureMapper.findStudentLectureByStuId"/>
    </resultMap>

    <select id="findStudentById" parameterType="int" resultMap="findStudentByIdMap">
        SELECT id, student_name, sex, selfcard_no, note
        FROM t_student WHERE id = #{stuId}
    </select>
</mapper>
           

總結

在一對一級聯時，使用association ，一對多時使用collection 。我們隻需提供給mybatis級聯時需要執行的SQL語句即可，mybatis會自動将查詢到的資訊包裝到Javabean中。