MyBatis（多表关系）

目录

一、数据库分页查询

二、多表关系

三、延迟加载

---

一、数据库分页查询

# MySql查询第（10 ～ 20条的数据）
SELECT * FROM skill LIMIT 10, 10

不同的数据库分页查询语句不一致，mysql的最简单；

国人开发了一款MyBatis分页插件：PageHelper,支持多种数据库，极大简化分页的业务逻辑

在maven中添加依赖：

    <!--分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.3.1</version>
        </dependency>

在mybatis-config.xml中配置：

    <plugins>
        <!--拦截器-->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!--true 代表分页合理化：pageNum <= 0时自动获取第一页-->
            <property name="reasonable" value="true"/>
        </plugin>
    </plugins>

     <mappers>
        <!--映射文件-->
        <mapper resource="mappers/person.xml" />
        <mapper resource="mappers/idCard.xml" />
        <mapper resource="mappers/bankCard.xml" />
        <mapper resource="mappers/job.xml" />
    </mappers>

// 分页
    @Test
    public void page() throws Exception{
        try ( SqlSession session = MyBatises.openSession()){
            PageHelper.startPage(1,10); // 分页插件
           List<Skill> skills = session.selectList("skill.list");
            for (Skill skill : skills) {
                System.out.println(skill);
            }
        }
    }

以上就是分页插件的使用，只需要加入：

 PageHelper.startPage(1,10); // 分页插件

二、多表关系

数据库的表之间可能存在一定的关系：一对多/多对一、一对一、多对多，需要创建表如下：person、job、id_card、bank_card、person_job这五张表

一对一关系比如person对身份证idCard：

 <sql id="sqlListAll">
        SELECT
        ic.*,
        p.name person_name,
        p.id person_id
        FROM
        id_card ic
        JOIN person p ON ic.person_id = p.id
    </sql>

    <resultMap id="rmList" type="com.mj.bean.IdCard">
        <id property="id" column="id"/>
        <result property="no" column="no"/>
        <result property="address" column="address"/>
        <association property="person" javaType="com.mj.bean.Person">
            <id property="id" column="person_id"/>
            <result property="name" column="person_name"/>
        </association>
    </resultMap>

    <select id="list" resultMap="rmList">
        <include refid="sqlListAll"/>
    </select>

 一对多比如person对应银行卡bankCard：

<sql id="sqlListAll">
        SELECT
        bc.*,
        p.name person_name
        FROM
        bank_card bc
        JOIN person p ON bc.person_id = p.id
    </sql>

    <resultMap id="rmList" type="com.mj.bean.BankCard">
        <id property="id" column="id"/>
        <result property="no" column="no"/>
        <result property="amout" column="amout"/>
        <association property="person" javaType="com.mj.bean.Person">
            <id property="id" column="person_id"/>
            <result property="name" column="person_name"/>
        </association>
    </resultMap>

    <select id="list" resultMap="rmList">
        <include refid="sqlListAll"/>
    </select>

多对多比如job对应person：

<sql id="sqlListAll">
        SELECT
        j.*,
        p.id person_id,
        p.name person_name
        FROM
        job j
        LEFT	JOIN person_job pj ON pj.job_id = j.id
        LEFT	JOIN person  p ON p.id = pj.person_id
    </sql>

    <resultMap id="rmList" type="com.mj.bean.Job">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="duty" column="duty"/>
        <collection property="persons" ofType="com.mj.bean.Person">
            <id property="id" column="person_id"/>
            <result property="name" column="person_name"/>
        </collection>
    </resultMap>

    <select id="list" resultMap="rmList">
        <include refid="sqlListAll"/>
    </select>

汇总person对应idCard，bankCard，job如下：

<sql id="sqlListAll">
        SELECT
        p.*,
        ic.id id_card_id,
        ic.NO id_card_no,
        ic.address id_card_address,
        bc.id bank_card_id,
        bc.NO bank_card_no,
        bc.amout bank_card_amout,
        j.id job_id,
        j.name job_name,
        j.duty job_duty
        FROM
        person p
        JOIN id_card ic ON p.id = ic.person_id
        LEFT JOIN bank_card bc ON p.id = bc.person_id
        LEFT JOIN person_job pj ON pj.person_id = p.id
        LEFT JOIN job j ON j.id = pj.job_id
    </sql>

    <resultMap id="rmList" type="com.mj.bean.Person">
        <id property="id" column="id"/>
        <result property="name" column="name"/>

        <association property="idCard" javaType="com.mj.bean.IdCard">
            <id property="id" column="id_card_id"/>
            <result property="no" column="id_card_no"/>
            <result property="address" column="id_card_address"/>
        </association>

        <collection property="bankCards"  ofType="com.mj.bean.BankCard">
            <id property="id" column="bank_card_id"/>
            <result property="no" column="bank_card_no"/>
            <result property="amout" column="bank_card_amout"/>
        </collection>

        <collection property="jobs" ofType="com.mj.bean.Job">
            <id property="id" column="job_id"/>
            <result property="name" column="job_name"/>
            <result property="duty" column="job_duty"/>
        </collection>
    </resultMap>

    <select id="list" resultMap="rmList">
        <include refid="sqlListAll"/>
    </select>

以上就是mybatis的多表关系。

三、延迟加载

关联对象（association、collection）可以实现延迟加载，需要用到参数：

        <collection property="ttList" 
                    fetchType="lazy"
                    column="id"
                    select="idCard.getByPerson"
                    ofType="com.mj.bean.Job">
            
        </collection>

然后在idCard的xml文件中配置即可。一般用的不多。

全局延迟加载：

     <settings>
        <!--是否开启驼峰命名自动映射,默认false，created_time -> createdTime 如果不开启在mapper里面也可以设置 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--true 代表所有关联对象都延迟加载-->
        <setting name="lazyLoadingEnabled" value="true"/>
    </settings>