Mybatis關聯對映和逆向工程

Mybatis關聯對映

兩種不同實現方式

巢狀查詢：通過執行另一個sql執行語句返回資料
巢狀結果查詢：執行表關聯查詢語句，查詢結果對映成關聯物件（查詢一次）

一對一對映

use jektong;
create table t_teacher(
	t_id int(4) primary key auto_increment,
	t_name varchar(10) not null
);
	
create table t_class(
	c_id int(4) primary key auto_increment,
	c_name varchar(20),
	teacher_id int(4)
);

alert table t_class add constraint fk_teacher_id foreign key(teacher_id) references t_teacher(t_id);

insert into t_teacher(t_name) values("張三丰");
insert into t_teacher(t_name) values("孫悟空");
insert into t_class(c_name, teacher_id) values('三年二班',2);
insert into t_class(c_name, teacher_id) values('五年六班',1);

@1:巢狀查詢

<!-- 巢狀關聯查詢 -->
    <resultMap type="Teacher" id="teacherMapOne">
        <id property="id" column="t_id" jdbcType="INTEGER"/>
        <result property="name" column="t_name" jdbcType="VARCHAR"/>
        <!-- 一對一使用association屬性值：teacher類中關聯的class屬性 欄位值是主表與從表外來鍵關聯的主表欄位 -->
        <association property="cls" column="t_id" select="findClass"/>
    </resultMap>
	<select id="findTeacherByIdOne" parameterType="int" resultMap="teacherMapOne">
       select * from t_teacher where t_id=#{id}
    </select>
    <resultMap id="classMap" type="class">
        <id property="id" column="c_id"/>
        <result property="name" column="c_name" jdbcType="VARCHAR"/>
        <result property="teacherId" column="teacher_id" jdbcType="INTEGER"/>
    </resultMap>
    <select id="findClass" parameterType="integer" resultMap="classMap">
        select * from t_class where  teacher_id=#{id};
    </select>

@2:巢狀結果查詢

<!-- 巢狀結果查詢 -->
<select id="findTeacherByIdTwo" parameterType="integer" resultMap="teacherMapTwo">
    select t.*, c.*
    from t_teacher t
    join t_class c
    on  t.t_id = c.teacher_id
    where t_id = #{id};
</select>
<resultMap type="Teacher" id="teacherMapTwo">
    <id property="id" column="t_id" jdbcType="INTEGER"/>
    <result property="name" column="t_name" jdbcType="VARCHAR"/>
    <association property="cls" column="t_id" javaType="Class">
        <id property="id" column="c_id"/>
        <result property="name" column="c_name" jdbcType="VARCHAR"/>
        <result property="teacherId" column="teacher_id" jdbcType="INTEGER"/>
    </association>
</resultMap>

一對多對映

@1:巢狀查詢

<resultMap type="dept" id="deptMap">
        <id property="id" column="deptno" jdbcType="INTEGER"/>
        <result property="dname" column="dname" jdbcType="VARCHAR"/>
        <result property="location" column="location" jdbcType="DOUBLE"/>
        <result property="dbSource" column="db_source" jdbcType="DOUBLE"/>
    	<!-- ofType一對多關聯的物件 javaType一般為集合物件 select進行查詢 -->
        <collection property="emps" column="deptno" ofType="Emp" javaType="list" select="findEmps"/>
    </resultMap>

    <select id="findDeptById0ne" parameterType="integer" resultMap="deptMap" resultType="com.jektong.entity.Dept">
        select * from dept_xu where deptno = #{id};
    </select>

    <select id="findEmps" parameterType="integer" resultMap="empMap">
        select * from t_emp where e_deptno = #{id};
    </select>

    <resultMap id="empMap" type="emp">
        <id property="id" column="e_id" jdbcType="INTEGER"/>
        <result property="name" column="e_name" jdbcType="VARCHAR"/>
        <result property="salary" column="e_salary" jdbcType="DOUBLE"/>
        <result property="bonus" column="e_bonus" jdbcType="DOUBLE"/>
        <result property="hiredate" column="e_hiredte" jdbcType="DATE"/>
        <result property="deptno" column="e_deptno" jdbcType="INTEGER"/>
    </resultMap>

create table t_student(
	s_id int(4) primary key auto_increment,
	s_name varchar(10) not null
);

create table t_course(
	c_id int(4) primary key auto_increment,
	c_name varchar(10) not null
);

create table student_course(
	student_id int(4),
	course_id int(4)
);

insert into t_student(s_name) values(('張三'),('李四'),(‘王五'),('趙六'));
insert into t_course(c_name) values(('語文'),('數學'),('英語'));
insert into student_course values((1,1),(1,2),(2,1),(2,2),(2,3),(3,2),(3,3),(4,1),(4,2));

多對多

 <select id="findByStudentId" resultMap="studentMap" parameterType="integer">
        select s.*, c.*, sc.*
        from t_student s
        join student_course sc on s.s_id=sc.student_id
        join t_course c on c.c_id=sc.course_id
        where s.s_id=#{id}
    </select>
    <resultMap id="studentMap" type="Student">
        <id property="id" column="s_id"/>
        <result property="sName" column="s_name"/>
        <collection property="courses" column="s_id" ofType="Course" javaType="list">
            <id property="id" column="c_id"/>
            <result property="cName" column="c_name"/>
        </collection>
    </resultMap>

MyBatis Generator

MBG,mybatis程式碼生成器，快速生成對映檔案，介面以及bean類，支援基本的CRUD，以及QBC風格查詢，表連線儲存過程等sql的定義需要手動編寫

http://mybatis.org/generator/

組態檔：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <context id="develop" targetRuntime="Mybatis3">
        <commentGenerator>
            <!-- 是否去除自動生成的註釋 true:是;false:否 -->
            <property name="suppressAllComments" value="true" />
        </commentGenerator>
        <!-- 資料庫連線 -->
        <jdbcConnection
                connectionURL="jdbc:mysql://localhost:3306/jektong?useUnicode=true&amp;characterEncoding=utf-8"
                driverClass="com.mysql.jdbc.Driver"
                password="123456"
                userId="jektong"/>
        <!-- 指定要生成的實體類包及路徑 -->
        <javaModelGenerator
                targetPackage="com.jektong.entiey" targetProject="src\main\java"/>
        <sqlMapGenerator targetPackage="com.jektong.mapper"
                         targetProject="src\main\java"/>
        <javaClientGenerator
                targetPackage="com.jektong.dao" targetProject="src\main\java"
                type="XMLMAPPER"/>
        <table tableName="t_emp" domainObjectName="Emp"></table>
    </context>
</generatorConfiguration>