Mybatis 关联查询 collection 效率分析 -

zhaojian0910

浏览: 46369 次

最近访客更多访客>>

智拔天下

wk82254887

zhaodengfeng1989

csmnjk

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

Mybatis 关联查询 collection 效率分析

博客分类：

web
mysql
mybatis
java

java mybatis

背景：

有一张评议人表(e_evaluator)，一张参评企业表(attend_enterprise_info)，一张评议人和参评企业关系表(er_evaluator_attend_enterprise)，关系如下图

目前e_evaluator表中有3800条数据，attend_enterprise_info表中有130条数据，er_evaluator_attend_enterprise表中有42000条数据

通过中间表er_evaluator_attend_enterprise，建立一个e_evaluator与多个e_attend_enterprise的一对多关系

java代码调用

long s1 = System.currentTimeMillis();
// 所有评议人
List<Evaluator> evaluatorLst = this.evaluatorMapper.queryEvaluators(evaluatorParam);
long s2 = System.currentTimeMillis();
log.info("用时：" + (s2 - s1))

mybatis的xml文件用到了两种写法

第一种写法：

第一步：配置返回的resultMap

<resultMap id="BaseResultMap" type="com.hhsoft.evaluation.model.dto.Evaluator" >
    	<id column="evaluator_id" property="evaluatorId" jdbcType="VARCHAR" />
	    <result column="evaluator_name" property="evaluatorName" jdbcType="VARCHAR" />
	    <result column="account" property="account" jdbcType="VARCHAR" />
	    <result column="password" property="password" jdbcType="VARCHAR" />
	    <result column="login_status" property="loginStatus" jdbcType="VARCHAR" />
	    <result column="gender" property="gender" jdbcType="VARCHAR" />
	    <result column="mobile" property="mobile" jdbcType="VARCHAR" />
	    <result column="administrative_area_code" property="administrativeAreaCode" jdbcType="VARCHAR" />
	    <result column="administrative_area_name" property="administrativeAreaName" jdbcType="VARCHAR" />
	    <result column="administrative_area_grade" property="administrativeAreaGrade" jdbcType="VARCHAR" />
	    <result column="questionnaire_id" property="questionnaireId" jdbcType="VARCHAR" />
	    <result column="evaluator_type" property="evaluatorType" jdbcType="VARCHAR" />
	    <result column="city_code" property="cityCode" jdbcType="VARCHAR" />
	    <result column="city_name" property="cityName" jdbcType="VARCHAR" />
	    <result column="county_code" property="countyCode" jdbcType="VARCHAR" />
	    <result column="county_name" property="countyName" jdbcType="VARCHAR" />
	    <result column="position_name" property="positionName" jdbcType="VARCHAR" />
	    <result column="organization_name" property="organizationName" jdbcType="VARCHAR" />
	    <result column="create_user_id" property="createUserId" jdbcType="VARCHAR" />
	    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
	    <result column="update_user_id" property="updateUserId" jdbcType="VARCHAR" />
	    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
	    <result column="order_no" property="orderNo" jdbcType="FLOAT" />
	    <result column="status" property="status" jdbcType="INTEGER" />
	    <result column="office_people_id" property="officePeopleId" jdbcType="VARCHAR" />
	    <result column="work_unit" property="workUnit" jdbcType="VARCHAR" />
	    <result column="office_id" property="officeId" jdbcType="VARCHAR" />
	    <result column="answer_status" property="answerStatus" jdbcType="INTEGER" />
	    
	    <collection property="attendEnterpriseLst" ofType="com.hhsoft.evaluation.model.dto.AttendEnterprise" >
	    	<id column="attend_enterprise_id" property="attendEnterpriseId"/>
	    	<result column="attend_enterprise_name" property="attendEnterpriseName"/>
	    </collection>
  </resultMap>

其中，重点在collection

<collection property="attendEnterpriseLst" ofType="com.hhsoft.evaluation.model.dto.AttendEnterprise" >
	    	<id column="attend_enterprise_id" property="attendEnterpriseId"/>
	    	<result column="attend_enterprise_name" property="attendEnterpriseName"/>
	    </collection>

查询的sql

<select id="queryEvaluators" resultMap="BaseResultMap" parameterType="com.hhsoft.evaluation.model.dto.Evaluator" >

SELECT eei.evaluator_id, 
	eei.evaluator_name, 
	eei.account, 
	eei.password, 
	eei.login_status, 
	eei.gender, 
	eei.mobile, 
	eei.administrative_area_code, 
	eei.administrative_area_grade, 
	eei.questionnaire_id, 
	eei.evaluator_type, 
	eei.city_code, 
	eei.county_code, 
	eei.position_name, 
	eei.organization_name, 
	eei.create_user_id, 
	eei.create_time, 
	eei.update_user_id, 
	eei.update_time, 
	eei.order_no, 
	eei.status,
	eei.office_people_id,
	eei.work_unit,
	eei.office_id,
	b1.area_name administrative_area_name,
	b2.area_name city_name, 
	b3.area_name county_name,
	eeae.attend_enterprise_id,
	eae.attend_enterprise_name
	 
	FROM 
	e_evaluator eei
	
	LEFT JOIN b_area b1 ON b1.area_code = eei.administrative_area_code
	LEFT JOIN b_area b2 ON b2.area_code = eei.city_code
	LEFT JOIN b_area b3 ON b3.area_code = eei.county_code
	LEFT JOIN e_office_info eoi ON eoi.office_id = eei.office_id
	LEFT JOIN e_office_people_info eopi ON eopi.office_people_id = eei.office_people_id
	LEFT JOIN er_evaluator_attend_enterprise eeae ON eei.evaluator_id = eeae.evaluator_id
	LEFT JOIN e_attend_enterprise eae ON eeae.attend_enterprise_id = eae.attend_enterprise_id
	where eei.status = 0 and eeae.status = 0

    ORDER BY eei.city_code, eei.county_code, eoi.order_no,eopi.order_no, eei.order_no
    <!-- 分页 -->
	<include refid="pagerB"></include>
  </select>

用时：10463ms

第二种写法：

第一步：配置返回的resultMap

<resultMap id="BaseResultMap" type="com.hhsoft.evaluation.model.dto.Evaluator" >
    	<id column="evaluator_id" property="evaluatorId" jdbcType="VARCHAR" />
	    <result column="evaluator_name" property="evaluatorName" jdbcType="VARCHAR" />
	    <result column="account" property="account" jdbcType="VARCHAR" />
	    <result column="password" property="password" jdbcType="VARCHAR" />
	    <result column="login_status" property="loginStatus" jdbcType="VARCHAR" />
	    <result column="gender" property="gender" jdbcType="VARCHAR" />
	    <result column="mobile" property="mobile" jdbcType="VARCHAR" />
	    <result column="administrative_area_code" property="administrativeAreaCode" jdbcType="VARCHAR" />
	    <result column="administrative_area_name" property="administrativeAreaName" jdbcType="VARCHAR" />
	    <result column="administrative_area_grade" property="administrativeAreaGrade" jdbcType="VARCHAR" />
	    <result column="questionnaire_id" property="questionnaireId" jdbcType="VARCHAR" />
	    <result column="evaluator_type" property="evaluatorType" jdbcType="VARCHAR" />
	    <result column="city_code" property="cityCode" jdbcType="VARCHAR" />
	    <result column="city_name" property="cityName" jdbcType="VARCHAR" />
	    <result column="county_code" property="countyCode" jdbcType="VARCHAR" />
	    <result column="county_name" property="countyName" jdbcType="VARCHAR" />
	    <result column="position_name" property="positionName" jdbcType="VARCHAR" />
	    <result column="organization_name" property="organizationName" jdbcType="VARCHAR" />
	    <result column="create_user_id" property="createUserId" jdbcType="VARCHAR" />
	    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
	    <result column="update_user_id" property="updateUserId" jdbcType="VARCHAR" />
	    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
	    <result column="order_no" property="orderNo" jdbcType="FLOAT" />
	    <result column="status" property="status" jdbcType="INTEGER" />
	    <result column="office_people_id" property="officePeopleId" jdbcType="VARCHAR" />
	    <result column="work_unit" property="workUnit" jdbcType="VARCHAR" />
	    <result column="office_id" property="officeId" jdbcType="VARCHAR" />
	    <result column="answer_status" property="answerStatus" jdbcType="INTEGER" />
	    
	    <collection property="attendEnterpriseLst"  select="queryErAttendEnterpriseEvaluator" column="evaluator_id">
	    </collection>
  </resultMap>

重点还是在collection

<collection property="attendEnterpriseLst"  select="queryErAttendEnterpriseEvaluator" column="evaluator_id">
	    </collection>

sql语句

<select id="queryEvaluators" resultMap="BaseResultMap" parameterType="com.hhsoft.evaluation.model.dto.Evaluator" >

SELECT eei.evaluator_id, 
	eei.evaluator_name, 
	eei.account, 
	eei.password, 
	eei.login_status, 
	eei.gender, 
	eei.mobile, 
	eei.administrative_area_code, 
	eei.administrative_area_grade, 
	eei.questionnaire_id, 
	eei.evaluator_type, 
	eei.city_code, 
	eei.county_code, 
	eei.position_name, 
	eei.organization_name, 
	eei.create_user_id, 
	eei.create_time, 
	eei.update_user_id, 
	eei.update_time, 
	eei.order_no, 
	eei.status,
	eei.office_people_id,
	eei.work_unit,
	eei.office_id,
	b1.area_name administrative_area_name,
	b2.area_name city_name, 
	b3.area_name county_name
	 
	FROM 
	e_evaluator eei
	
	LEFT JOIN b_area b1 ON b1.area_code = eei.administrative_area_code
	LEFT JOIN b_area b2 ON b2.area_code = eei.city_code
	LEFT JOIN b_area b3 ON b3.area_code = eei.county_code
	LEFT JOIN e_office_info eoi ON eoi.office_id = eei.office_id
	LEFT JOIN e_office_people_info eopi ON eopi.office_people_id = eei.office_people_id
	where eei.status = 0

    ORDER BY eei.city_code, eei.county_code, eoi.order_no,eopi.order_no, eei.order_no
    <!-- 分页 -->
	<include refid="pagerB"></include>
  </select>

关联关系select用到的sql语句

  <select id="queryErAttendEnterpriseEvaluator" parameterType="string" resultType="com.hhsoft.evaluation.model.dto.AttendEnterprise">
  	select eae.attend_enterprise_id attendEnterpriseId, eae.attend_enterprise_name attendEnterpriseName
  	from e_attend_enterprise eae
  	left join er_evaluator_attend_enterprise eeae on eeae.attend_enterprise_id = eae.attend_enterprise_id
  	where eeae.evaluator_id = #{evaluator_id}
  </select>

用时：4862ms

效率提高一倍。具体原理待分析

查看图片附件

分享到：

iframe jsp 传入中文参数的时候乱码的问题

2017-04-17 17:09
浏览 5830
评论(0)
分类:编程语言
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

Mybatis 关联查询 collection 效率分析

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

Mybatis 关联查询 collection 效率 分析

评论

发表评论

相关推荐

倒水问题引出的 不特定的N次for循环嵌套

mysql update 使用 WHEN/THEN/ELSE/END 的时候非WHEN的数据被改成null

mysql timestamp类型的字段 默认值CURRENT_TIMESTAMP 自动更新的问题

Mybatis 批量更新 set 多个字段你的时候报错 mysql

Spring MVC ContentNegotiatingViewResolver 样例 根据不同的url地址后缀返回不同类型的数据

HttpClient + PostMethod 发送post消息

Spring 扫描不到 Mybatis的Mapper的interface

java 发送http消息 带参数 HttpURLConnection

mysql错误： Cannot convert value '0000-00-00 00:00:00' from column 10 to TIMESTAMP

java.lang.NoClassDefFoundError: org/springframework/jdbc/datasource/TransactionA

EL表达式无效

通过反射技术，获取泛型类的真实类型

Java笔记之代理

最近访客更多访客>>

Mybatis 关联查询 collection 效率分析

倒水问题引出的不特定的N次for循环嵌套

mysql timestamp类型的字段默认值CURRENT_TIMESTAMP 自动更新的问题

Spring MVC ContentNegotiatingViewResolver 样例根据不同的url地址后缀返回不同类型的数据

java 发送http消息带参数 HttpURLConnection