Mybatis关联映射

两种不同实现方式

1. 嵌套查询：通过执行另一个sql执行语句返回数据
2. 嵌套结果查询： 执行表关联查询语句，查询结果映射成关联对象（查询一次）

一对一映射

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>