mybatis查找的结果集对象中包含对象和集合的用法

平时项目中使用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连接,所以性能上差别不大,但是第三种写法明显复杂不少,推荐第一种和第二种写法。

©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页