平时项目中使用mybatis查询数据库,对象结果集可能比较复杂,对象中嵌套对象或者集合。
如下图所示,返回结果集对象project中包含其他子对象(查询的主表与关联子表数据一对一关系)和list集合(查询的主表与关联子表数据一对多关系):
解决方案:
对象下面嵌套的对象采用<association>写法,嵌套的集合采用<collection>写法
写法实例:
(1)其中projectInfo与projectCharge对象为Project的嵌套对象,采用<association>写法,而requires是list集合,采用<collection>写法
<resultMap type="com.utry.ucsc.task.vo.Project" id="ProjectDetailResultMap">
<association property="projectInfo" javaType="com.utry.ucsc.task.vo.ProjectInfo">
<result column="id" jdbcType="VARCHAR" property="projectId" />
<result column="version" jdbcType="INTEGER" property="version" />
<result column="project_name" jdbcType="VARCHAR" property="projectName" />
<result column="project_type" jdbcType="VARCHAR" property="projectType" />
<result column="description" jdbcType="VARCHAR" property="description" />
<result column="project_status" jdbcType="VARCHAR" property="projectStatus" />
<result column="payment_status" jdbcType="VARCHAR" property="paymentStatus" />
<result column="start_date" jdbcType="TIMESTAMP" property="startDate" />
<result column="end_date" jdbcType="TIMESTAMP" property="endDate" />
<result column="workbench_name" jdbcType="VARCHAR" property="workbenchName" />
<result column="project_logo" jdbcType="VARCHAR" property="projectLogo" />
<result column="project_display_level" jdbcType="VARCHAR" property="projectDisplayLevel" />
<result column="task_total" jdbcType="INTEGER" property="taskTotal" />
<result column="max_task_once" jdbcType="INTEGER" property="maxTaskOnce" />
<result column="accepted_task_count" jdbcType="INTEGER" property="acceptedTaskCount" />
<result column="completed_task_count" jdbcType="INTEGER" property="completedTaskCount" />
<result column="company_id" jdbcType="VARCHAR" property="companyId" />
<result column="creator" jdbcType="VARCHAR" property="creator" />
<result column="create_date" jdbcType="TIMESTAMP" property="createDate" />
<result column="modified_date" jdbcType="TIMESTAMP" property="modifiedDate" />
</association>
<association property="projectCharge" javaType="com.utry.ucsc.task.vo.ProjectCharge">
<result column="charge_type" jdbcType="VARCHAR" property="chargeType" />
<result column="unit_price" jdbcType="DECIMAL" property="unitPrice" />
<result column="extract_price" jdbcType="DECIMAL" property="extractPrice" />
</association>
<collection property="requires" resultMap="ProjectRequireResultMap"/>
</resultMap>
<resultMap type="com.utry.ucsc.task.vo.ProjectRequirement" id="ProjectRequireResultMap">
<result column="require_type" jdbcType="VARCHAR" property="requireType" />
<result column="require_value" jdbcType="VARCHAR" property="requireValue" />
</resultMap>
SQL写法:
(2)<association>写法不变,<collection>中采用ofType属性指向集合中的元素类型
<resultMap type="com.utry.ucsc.task.vo.Project" id="ProjectDetailResultMap">
<association property="projectInfo" javaType="com.utry.ucsc.task.vo.ProjectInfo">
<result column="projectId" jdbcType="VARCHAR" property="projectId" />
<result column="version" jdbcType="INTEGER" property="version" />
<result column="project_name" jdbcType="VARCHAR" property="projectName" />
<result column="project_type" jdbcType="VARCHAR" property="projectType" />
<result column="description" jdbcType="VARCHAR" property="description" />
<result column="project_status" jdbcType="VARCHAR" property="projectStatus" />
<result column="payment_status" jdbcType="VARCHAR" property="paymentStatus" />
<result column="start_date" jdbcType="TIMESTAMP" property="startDate" />
<result column="end_date" jdbcType="TIMESTAMP" property="endDate" />
<result column="workbench_name" jdbcType="VARCHAR" property="workbenchName" />
<result column="project_logo" jdbcType="VARCHAR" property="projectLogo" />
<result column="project_display_level" jdbcType="VARCHAR" property="projectDisplayLevel" />
<result column="task_total" jdbcType="INTEGER" property="taskTotal" />
<result column="max_task_once" jdbcType="INTEGER" property="maxTaskOnce" />
<result column="accepted_task_count" jdbcType="INTEGER" property="acceptedTaskCount" />
<result column="completed_task_count" jdbcType="INTEGER" property="completedTaskCount" />
<result column="company_id" jdbcType="VARCHAR" property="companyId" />
<result column="creator" jdbcType="VARCHAR" property="creator" />
<result column="create_date" jdbcType="TIMESTAMP" property="createDate" />
<result column="modified_date" jdbcType="TIMESTAMP" property="modifiedDate" />
</association>
<association property="projectCharge" javaType="com.utry.ucsc.task.vo.ProjectCharge">
<result column="charge_type" jdbcType="VARCHAR" property="chargeType" />
<result column="unit_price" jdbcType="DECIMAL" property="unitPrice" />
<result column="extract_price" jdbcType="DECIMAL" property="extractPrice" />
</association>
<collection property="requires" ofType="com.utry.ucsc.task.vo.ProjectRequirement">
<result column="require_type" jdbcType="VARCHAR" property="requireType" />
<result column="require_value" jdbcType="VARCHAR" property="requireValue" />
</collection>
</resultMap>
SQL写法:
(3)projectInfo与projectCharge对象仍然采用<association>写法,requires采用内部嵌套查询方式
<resultMap type="com.utry.ucsc.task.vo.Project" id="ProjectDetailResultMap">
<association property="projectInfo" javaType="com.utry.ucsc.task.vo.ProjectInfo">
<result column="projectId" jdbcType="VARCHAR" property="projectId" />
<result column="version" jdbcType="INTEGER" property="version" />
<result column="project_name" jdbcType="VARCHAR" property="projectName" />
<result column="project_type" jdbcType="VARCHAR" property="projectType" />
<result column="description" jdbcType="VARCHAR" property="description" />
<result column="project_status" jdbcType="VARCHAR" property="projectStatus" />
<result column="payment_status" jdbcType="VARCHAR" property="paymentStatus" />
<result column="start_date" jdbcType="TIMESTAMP" property="startDate" />
<result column="end_date" jdbcType="TIMESTAMP" property="endDate" />
<result column="workbench_name" jdbcType="VARCHAR" property="workbenchName" />
<result column="project_logo" jdbcType="VARCHAR" property="projectLogo" />
<result column="project_display_level" jdbcType="VARCHAR" property="projectDisplayLevel" />
<result column="task_total" jdbcType="INTEGER" property="taskTotal" />
<result column="max_task_once" jdbcType="INTEGER" property="maxTaskOnce" />
<result column="accepted_task_count" jdbcType="INTEGER" property="acceptedTaskCount" />
<result column="completed_task_count" jdbcType="INTEGER" property="completedTaskCount" />
<result column="company_id" jdbcType="VARCHAR" property="companyId" />
<result column="creator" jdbcType="VARCHAR" property="creator" />
<result column="create_date" jdbcType="TIMESTAMP" property="createDate" />
<result column="modified_date" jdbcType="TIMESTAMP" property="modifiedDate" />
</association>
<association property="projectCharge" javaType="com.utry.ucsc.task.vo.ProjectCharge">
<result column="charge_type" jdbcType="VARCHAR" property="chargeType" />
<result column="unit_price" jdbcType="DECIMAL" property="unitPrice" />
<result column="extract_price" jdbcType="DECIMAL" property="extractPrice" />
</association>
<collection property="requires" ofType="com.utry.ucsc.task.vo.ProjectRequirement"
select="com.utry.ucsc.task.dao.ProjectInfoBeanMapper.getProjectRequire" column="{projectId=projectId,version=version}">
</collection>
</resultMap>
<resultMap type="com.utry.ucsc.task.vo.ProjectRequirement" id="RequiresResultMap">
<result column="require_type" jdbcType="VARCHAR" property="requireType" />
<result column="require_value" jdbcType="VARCHAR" property="requireValue" />
</resultMap>
对应的sql片段:
注意:这里嵌套的查询getProjectRequire查询中parameterType采用Map,<collection>标签中的column多个参数写法也需按照上述规范,=左边的是getProjectRequire这个方法中的入参名称,==右边是getProjectDetail这个查询方法中的查询出来对应的字段名称。另外,结果集<resultMap>中各个标签顺序也是有规定的:(constructor?, id*, result*, association*, collection*, discriminator?),不按照这个规定,编译会报错
三种写法的比较:
通过日志打印sql发现,其中第一种和第二种写法都只查询了一次sql,第三种查询了两次sql,但是用的是一个sql连接,并不是新建一个sql连接,所以性能上差别不大,但是第三种写法明显复杂不少,推荐第一种和第二种写法。