【Mybatis】——resultMap实现主从表递归查询

ゝ一世哀愁。 2022-05-31 06:54 334阅读 0赞

# 前提 #

最近在重构系统中题库部分，其中涉及到题目的多层的父子结构的题型（类似于树结构），而且其中还有每个题目还涉及到从表的关系。之前从程序还是可以实现递归的方式，还没有直接从sql实现的递归的查询，故特此记录一下。

# 表结构关系 #

主表 t\_problem\_main

从表t\_problem\_sub

t\_problem\_main中可能存在类似树结构的递归关系，t\_problem\_main中任何一条数据与t\_problem\_sub均是一对多的关系。

# resultMap作用 #

Mybatis中最复杂的元素就是resultMap，主要作用就是映射规则，级联的更新，定制类型转化器，是一个结果集的映射关系。目前版本的mybatis只支持resultMap的查询。

## resultMap元素 ##

此处只是简单介绍resultMap元素中涉及的一些基本元素，具体的内容还是大家自己去深入学习。

<resultMap> <constructor> //适用于不存在没有参数的构造方法 <idArg></idArg> <arg></arg> </constructor> <id/>//这个对象的主键 <association/>//一对一级联 <collection/>//一对多级联 <discriminator>//鉴别器 <case/> </discriminator> </resultMap>

# 业务 #

提供一个题干id，查询出这个题目的完整信息。此题目可能有多层子题目，而且每个题目下均带有选项内容（对应从表）

# 实现思路 #

先查询父节点id，利用<association>元素的一对一级联查询出父题干带的选项内容，其次利用父节点id作为p\_id递归查询其下带有的子题详情利用的mybatis中<collection>

## 具体实现 ##

<resultMap id="MainQuestionMap" type="com.dmsdbj.itoo.examinationEvaluation.entity.ext.ProblemModel"> <id property="id" column="mainId"/> <result property="pId" column="pMainId"/> <association property="problemSubEntityList" column="mainId" select="querySubOption"> <id property="id" column="psId"/> <result property="imageId" column="subImageId"/> <result property="thumbId" column="subThumbId"/> <result property="imageName" column="subImageName"/> <result property="resource" column="subResource"/> </association> <collection property="problemModelList" column="mainId" select="querySubQuestion"/> </resultMap>  <resultMap id="subQuestionMap" type="com.dmsdbj.itoo.examinationEvaluation.entity.ext.ProblemModel"> <id column="mainId" property="id"/> <association property="problemSubEntityList" column="mainId" select="querySubOption"> <id property="id" column="psId"/> <result property="imageId" column="subImageId"/> <result property="thumbId" column="subThumbId"/> <result property="imageName" column="subImageName"/> <result property="resource" column="subResource"/> </association> <collection property="problemModelList" column="mainId" select="querySubQuestion"/> </resultMap>  <select id="queryMainQuestion" resultMap="MainQuestionMap"> SELECT pm.id AS mainId, question_content AS questionContent, answer, analysis, p_id AS pMainId, problem_id AS problemId, degree, chapter, question_code AS questionCode, pm.image_name AS imageName, pm.image_id AS imageId, pm.thumb_id AS thumbId, blank_count AS blankCount, child_order AS childOrder, pm.remark, is_effective AS isEffective, is_out_of_order AS isOutOfOrder, is_alias AS isAlias, is_paper AS isPaper, pm.resource FROM t_problem_main pm WHERE id = #{problemId} and history_flag = 0 and pm.is_delete=0 </select>  <select id="querySubQuestion" resultMap="subQuestionMap"> SELECT pm.id AS mainId, question_content AS questionContent, answer, analysis, p_id AS pId, problem_id AS problemId, degree, chapter, question_code AS questionCode, pm.image_name AS imageName, pm.image_id AS imageId, pm.thumb_id AS thumbId, blank_count AS blankCount, child_order AS childOrder, pm.remark, is_effective AS isEffective, is_out_of_order AS isOutOfOrder, is_alias AS isAlias, is_paper AS isPaper, pm.resource FROM t_problem_main pm WHERE p_id = #{pid} and history_flag = 0 and pm.is_delete=0 ORDER BY child_order </select>  <select id="querySubOption" resultType="com.dmsdbj.itoo.examinationEvaluation.entity.ProblemSubEntity"> SELECT id, option_order AS optionOrder, problem_main_id AS problemMainId, image_id AS imageId, image_name AS imageName, resource, remark, operator FROM t_problem_sub WHERE problem_main_id = #{id} and is_delete = 0 ORDER BY option_order </select>

# 总结 #

利用这用方式减少递归查询过程中对数据库的打开和关闭操作，在用户使用上时间得到减少，但是同时也把业务的主要压力放到了数据库所在的服务器上。说真的也不知道这两个因素具体怎么考虑最好，问了leader说暂时也无法判断哪种很好一些，唉，现在是这么做吧，最起码用户感受起来还是稍好一些的。