时间戳

首页 > 技术文章 > Mybatis 复杂查询-association和collection 对比说明

Mybatis 复杂查询-association和collection 对比说明

hnusthuyanhua 2020-10-11 17:16 原文

本篇记录一下Mybatis association和collection 查询说明

1.首先上一个实际项目中是mapper 借此注释说明

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.thirtydays.reading.dao.ReadingIeltsSectionPODao">
    
    <resultMap type="SectionDetailAppDTO" id="sectionDetail">
        <id property="sectionId" column="sectionId" />
        <result property="questionPageUrl" column="questionPageUrl" />
        <result property="passagePageUrl" column="passagePageUrl" />
        
        <association property="longSentenceInfo" 
　　　　　　javaType="LSBaseAppDTO" 
　　　　　　column="{sectionId=sectionId,accountId=accountId,lsInstructions=lsInstructions}"
 　　　　　select="getSectionLongSentenceInfo"/>
        
        <collection property="paragraphs"  
　　　　　　ofType="SectionParagraphAppDTO" 
　　　　　　column="{sectionId=sectionId}" 
　　　　　　select="listSectionParagraphs"/>    
        
        <collection property="questions"  ofType="SectionQuestionAppDTO" column="{sectionId=sectionId}" select="listSectionQuestions"/>    
            
        <collection property="collocations" ofType="SectionCollocationAppDTO" column="{sectionId=sectionId,accountId=accountId}" select="listSectionCollocations" />
        
        <collection property="vocabularies" ofType="SectionVocabulariesAppDTO" column="{sectionId=sectionId,accountId=accountId}" select="listSectionVocabularies" />
        
        <collection property="answers" ofType="SectionAnswerAppDTO" column="{sectionId=sectionId,accountId=accountId}" select="listSectionAnswers" />
    </resultMap>
    
    <!-- 获取剑雅阅读详情 -->
    <select id="getReadingSectionDetail" resultMap="sectionDetail">
        SELECT t1.`sectionId`,
               t1.`questionPageUrl`,
               t1.`passagePageUrl`,
               t1.`lsInstructions`,
               #{accountId} AS `accountId`
        FROM `reading_ielts_section` t1 
        WHERE t1.`sectionId` = #{sectionId}
    </select>
    
    <resultMap type="LSBaseAppDTO" id="lsBaseAppDTO">
    
        <result property="instructions" column="lsInstructions" />
        
        <collection property="sentences" ofType="LSDetailAppDTO" >
            <id property="sentenceId" column="sentenceId"/>
            <result property="longSentence" column="longSentence" />
            <result property="longSentenceType" column="longSentenceType" />
            <result property="translation" column="translation" />
            <result property="optionsStr" column="options" />
            
            <collection property="questions" ofType="LSQuestionAppDTO">
                <id property="questionNo" column="questionNo"/>
                <result property="question" column="question"/>
                <result property="questionAnswer" column="answer"/>
                <result property="answerAnalysis" column="analysis"/>
                <result property="userAnswer" column="userAnswer"/>
            </collection>
        </collection>
    </resultMap>
    
    <!-- 获取章节原文列表 -->
    <select id="listSectionParagraphs" resultType="SectionParagraphAppDTO">
        SELECT `paragraphNo`,
               `enSentence`,
               `cnSentence`
        FROM `reading_ielts_section_paragraph`
        WHERE `sectionId` = #{sectionId}
        ORDER BY `paragraphNo` ASC 
    </select>
    
    <!-- 获取剑雅阅读长难句句子列表 -->
    <select id="getSectionLongSentenceInfo" resultMap="lsBaseAppDTO">
        SELECT
            t1.`sentenceId`,
            t1.`longSentence`,
            t1.`longSentenceType`,
            t1.`translation`,
            t1.`options`,
            t2.`questionNo`,
            t2.`question`,
            t2.`answer`,
            t2.`analysis`,
            t3.`userAnswer`,
            #{lsInstructions} AS `lsInstructions`
            <!-- t4.`lsInstructions` -->
        FROM
            `reading_ielts_section_sentence` t1
            INNER JOIN `reading_ielts_section_sentence_question` t2 ON t1.`sentenceId` = t2.`sentenceId`
            LEFT JOIN `reading_ielts_section_sentence_answer` t3 ON t2.`questionNo` = t3.`questionNo` AND t3.`accountId` = #{accountId}
            <!-- INNER JOIN `reading_ielts_section` t4 ON t1.`sectionId` = t4.`sectionId` -->
        WHERE
            t1.`sectionId` = #{sectionId}
        ORDER BY t1.`sentenceId`
    </select>
    
    <!-- 获取剑雅阅读章节词伙列表 -->
    <select id="listSectionCollocations" resultType="SectionCollocationAppDTO">
        SELECT t2.`collocationId`,
               t2.`cnCollocation`,
               t2.`enCollocation`,
               IFNULL(t3.`collectStatus`, 0) AS `collectStatus`
        FROM `reading_ielts_section_collocation` t1
        INNER JOIN `collocation` t2 ON t1.`collocationId` = t2.`collocationId`
        LEFT JOIN `collocation_collect` t3 ON t2.`collocationId` = t3.`collocationId` AND t3.`accountId` = #{accountId}
        WHERE t1.`sectionId` = #{sectionId}
        ORDER BY t1.`serialNo` 
    </select>
    
    <!-- 获取剑雅章节下词汇列表 -->
    <select id="listSectionVocabularies" resultType="SectionVocabulariesAppDTO">
        SELECT t2.`wordId`,
               t2.`word`,
               t2.`pronounce`,
               t2.`pronounceAudio`,
               t2.`bref`,
               IFNULL(t3.`collectStatus`, 0) AS `collectStatus`
        FROM `reading_ielts_section_vocabulary` t1
        INNER JOIN `word` t2 ON t1.`wordId` = t2.`wordId`
        LEFT JOIN `word_collect` t3 ON t2.`wordId` = t3.`wordId` AND t3.`accountId` = #{accountId}
        WHERE t1.`sectionId` = #{sectionId}
        ORDER BY t1.`serialNo`
    </select>
    
    <!-- 获取剑雅阅读答题记录列表 -->
    <select id="listSectionAnswers" resultType="SectionAnswerAppDTO">
        SELECT `answerId`,
               `answerTime`,
               `questionNum`,
               `correctNum`,
               `answerDuration`,
               `correctRate`
        FROM `reading_ielts_section_answer`
        WHERE `sectionId` = #{sectionId} AND `accountId` = #{accountId}
        ORDER BY `answerTime` DESC
    </select>
    
    <!-- 获取章节题目列表 -->
    <select id="listSectionQuestions" resultType="SectionQuestionAppDTO">
        SELECT `questionNo`,
               `answer` AS `questionAnswer`,
               `analysis` AS `answerAnalysis`
        FROM `reading_ielts_section_part_question`
        WHERE `sectionId` = #{sectionId}
        ORDER BY `questionNo`
    </select>
    
    <!-- 获取剑雅阅读答题记录列表 -->
    <select id="listSectionAnswerRecordsByAnswerId" resultType="SectionAnswerListAppVO">
        SELECT t1.`userAnswer`,
               t1.`correctStatus`,
               t3.`questionNo`,
               t3.`answer` AS `questionAnswer`,
               t3.`analysis` AS `answerAnalysis`
        FROM `reading_ielts_section_answer_detail` t1 
        INNER JOIN `reading_ielts_section_answer` t2 ON t1.`answerId` = t2.`answerId`
        INNER JOIN `reading_ielts_section_part_question` t3 ON t1.`questionNo` = t3.`questionNo` AND t2.`sectionId` = t3.`sectionId`
        WHERE t1.`answerId` = #{answerId}
    </select>
    
    <!-- 更新剑雅阅读练习统计数据 -->
    <update id="updateSectionPracticeNum">
        UPDATE `reading_ielts_section`
        SET `practiceNum` = `practiceNum` + #{practiceNum},
            `answerNum` = `answerNum` + #{answerNum},
            `correctNum` = `correctNum` + #{correctNum},
            `correctRate` = `correctNum` * 100 / `answerNum`
        WHERE `sectionId` = #{sectionId}
    </update>

    <!-- 剑雅列表 -->
    <select id="listSection" resultType="com.thirtydays.reading.model.vo.IeltsSectionsVO">
        SELECT
        ris.sectionName,
        ris.sectionId,
        rit.testName,
        ic.categoryName,
        COUNT(distinct riss.sentenceId) sentenceCount,
        COUNT(distinct risc.collocationId) collocationCount,
        COUNT(distinct risv.wordId)  vocabularyCount
        FROM
        reading_ielts_section ris
        LEFT JOIN
        reading_ielts_test rit ON ris.testId = rit.testId
        LEFT JOIN
        ielts_category ic ON rit.ieltsCategoryId = ic.ieltsCategoryId
        LEFT JOIN
        reading_ielts_section_collocation risc ON ris.sectionId = risc.sectionId
        LEFT JOIN
        reading_ielts_section_vocabulary risv ON ris.sectionId = risv.sectionId
        LEFT JOIN
        reading_ielts_section_sentence riss ON riss.sectionId = ris.sectionId
        <where>
            <if test="ieltsCategoryId != null">
                rit.ieltsCategoryId = #{ieltsCategoryId}
            </if>
            <if test="testId != null">
                AND ris.testId = #{testId}
            </if>
        </where>
        GROUP BY ris.sectionId
    </select>
</mapper>

首先我们先看最开始的查询部分

1.通常我们的级联查询需求要么就是基于一条数据查询相关的，按照日常我们的写法，我们估计会先将下表的t1内容全部查询出来 然后再单条遍历去查相关的级联对象，在mybatis中通过一对一和一对多的关联关系可以极大的优化我们的代码

这里我的需求是根据t1中的每条对象 查询其相关的内容  一次方法获取所有相关的级联集合

包括一对一的 association  和一对多的  collection

在这当中需要注意的点包括：
1.association  通常为一对一关系  查询的实体一般用javaType

2.级联关系中的column字段为select可用的参数，参数必须来子于主查询语句

3.主语句不需要的子段或者其他一些参数可直接作为查询条件进行查询 用来作为子查询的参数
4.级联子查询也可以是级联查询  级联查询可以嵌套 
    
    <resultMap type="SectionDetailAppDTO" id="sectionDetail">
        <id property="sectionId" column="sectionId" />
        <result property="questionPageUrl" column="questionPageUrl" />
        <result property="passagePageUrl" column="passagePageUrl" />
        <association property="longSentenceInfo" javaType="LSBaseAppDTO" column="{sectionId=sectionId,accountId=accountId,lsInstructions=lsInstructions}" select="getSectionLongSentenceInfo"/>
        <collection property="paragraphs"  ofType="SectionParagraphAppDTO" column="{sectionId=sectionId}" select="listSectionParagraphs"/>    
        <collection property="questions"  ofType="SectionQuestionAppDTO" column="{sectionId=sectionId}" select="listSectionQuestions"/>     
        <collection property="collocations" ofType="SectionCollocationAppDTO" column="{sectionId=sectionId,accountId=accountId}" select="listSectionCollocations" />
        <collection property="vocabularies" ofType="SectionVocabulariesAppDTO" column="{sectionId=sectionId,accountId=accountId}" select="listSectionVocabularies" />
        <collection property="answers" ofType="SectionAnswerAppDTO" column="{sectionId=sectionId,accountId=accountId}" select="listSectionAnswers" />
    </resultMap>
    
    <!-- 获取剑雅阅读详情 -->
    <select id="getReadingSectionDetail" resultMap="sectionDetail">
        SELECT t1.`sectionId`,
               t1.`questionPageUrl`,
               t1.`passagePageUrl`,
               t1.`lsInstructions`,
               #{accountId} AS `accountId`
        FROM `reading_ielts_section` t1 
        WHERE t1.`sectionId` = #{sectionId}
    </select>

 

级联嵌套查询

在上边我们已经通过 

association  和  collection  做了一个对象相关的级联查询
其中我们也可以在级联查询中嵌套级联查询  比如这里的assocaition又嵌套了一层查询映射
<resultMap type="LSBaseAppDTO" id="lsBaseAppDTO">
    
        <result property="instructions" column="lsInstructions" />
        
        <collection property="sentences" ofType="LSDetailAppDTO" >
            <id property="sentenceId" column="sentenceId"/>
            <result property="longSentence" column="longSentence" />
            <result property="longSentenceType" column="longSentenceType" />
            <result property="translation" column="translation" />
            <result property="optionsStr" column="options" />
            
            <collection property="questions" ofType="LSQuestionAppDTO">
                <id property="questionNo" column="questionNo"/>
                <result property="question" column="question"/>
                <result property="questionAnswer" column="answer"/>
                <result property="answerAnalysis" column="analysis"/>
                <result property="userAnswer" column="userAnswer"/>
            </collection>
        </collection>
    </resultMap>
    <!-- 获取剑雅阅读长难句句子列表 -->
    <select id="getSectionLongSentenceInfo" resultMap="lsBaseAppDTO">
        SELECT
            t1.`sentenceId`,
            t1.`longSentence`,
            t1.`longSentenceType`,
            t1.`translation`,
            t1.`options`,
            t2.`questionNo`,
            t2.`question`,
            t2.`answer`,
            t2.`analysis`,
            t3.`userAnswer`,
            #{lsInstructions} AS `lsInstructions`
            <!-- t4.`lsInstructions` -->
        FROM
            `reading_ielts_section_sentence` t1
            INNER JOIN `reading_ielts_section_sentence_question` t2 ON t1.`sentenceId` = t2.`sentenceId`
            LEFT JOIN `reading_ielts_section_sentence_answer` t3 ON t2.`questionNo` = t3.`questionNo` AND t3.`accountId` = #{accountId}
            <!-- INNER JOIN `reading_ielts_section` t4 ON t1.`sectionId` = t4.`sectionId` -->
        WHERE
            t1.`sectionId` = #{sectionId}
        ORDER BY t1.`sentenceId`
    </select>

在mybatis中 

collection 可以根据查询的列进行合并归组，例如此处我们的关系就是一对多对多
lsInstructions 对应多个  sentences，  每个sentences 又对应多个  questions

 

推荐阅读