mybatis association , collection

https://www.cnblogs.com/zwwhnly/p/11201185.html

public class User {
    private String user_id;
    private String user_name;
    private List<Record> recordList;
    private Record record;
}

public class Record {
    private String record_id;
    private String record_name;
}

<resultMap id="userMap" type="com.air.manager.pojo.agent.User">
        <id column="USER_ID" property="user_id" jdbcType="VARCHAR" />
        <result column="USER_NAME" property="user_name" jdbcType="VARCHAR" />
        
        <association property=”userDetail” column=”user_detail” resultMap=”com.A.xsstuser.impl.dao.UserDetailDao.UserDetailMap” />


          <!--association多对一属性 property（映射到列结果的字段或属性） column（来自数据库的列    名）javaType（一个 
Java 类的完全限定名，或一个类型别名） -->
        // 一、关联单个对象 association ，如果关联对象超过一个会报错  
        //方法1 使用select属性查找selectRecordByUserId去加载
        <association property="record" column="USER_ID" select="selectRecordByUserId"/>

        //方法2 直接在内部定义record属性
        <association property="record" javaType="com.air.manager.pojo.agent.Record" >
            <id column="RECORD_ID" property="record_id" jdbcType="VARCHAR" />
            <result column="RECORD_NAME" property="record_name" jdbcType="VARCHAR" />
        </association >

        // 二、关联对象集合 collection
        //方法1 使用select属性查找selectRecordByUserId去加载
        <collection property="recordList" column="USER_ID" select="selectRecordByUserId"/>

        //方法2 直接在内部定义recordList集合 ，javaType="java.util.ArrayList"可以不添加
        <collection property="recordList" ofType="com.air.manager.pojo.agent.Record"  javaType="java.util.ArrayList" >
            <id column="RECORD_ID" property="record_id" jdbcType="VARCHAR" />
            <result column="RECORD_NAME" property="record_name" jdbcType="VARCHAR" />
        </collection>
</resultMap>



<select id="queryList" resultMap="userMap">
         SELECT 
             T.USER_ID ,
             T.USER_NAME
         FROM KING_USER T 
</select>

<resultMap id="recordMap" type="com.air.manager.pojo.agent.Record">
        <id column="RECORD_ID" property="record_id" jdbcType="VARCHAR" />
        <result column="RECORD_NAME" property="record_name" jdbcType="VARCHAR" />
</resultMap>
    
<select id="selectRecordByUserId" resultMap="recordMap">
        SELECT  
         R.RECORD_ID,
         R.RECORD_NAME
        FROM KING_RECORD R WHERE R.USER_ID=#{userId} //这里的userId名称可以随意，一样可以取到值
</select>

MyBatis中 collection 的两种使用方法，及效率比较

# 方案一
 <resultMap type="Student" id="StudentMap">
          <id column="id" property="id" />
          <result column="name" property="name" />
          <result column="job" property="job" />
          <collection property="scores" ofType="Score" column="id"  select="queryScoresBySID" ></collection>
     </resultMap>
     <resultMap type="Score" id="ScoreMap">
          <id column="id" property="id" />
          <result column="num" property="num" />
          <association property="subject" javaType="Subject" column="subject" select="querySubjectBySubId"></association>
     </resultMap>
     <select id="queryStudents" resultMap="StudentMap" >
          SELECT id,name,job FROM t_student
     </select>
     <select id="queryScoresBySID" resultMap="ScoreMap">
          SELECT id,num,subject FROM t_score WHERE sid = #{sid}
     </select>
     <select id="querySubjectBySubId" resultType="Subject" >
          SELECT id,name FROM t_subject where id = #{id}
     </select>





# 方案二

 <resultMap type="Student" id="StudentMap2">
          <id column="id" property="id" />
          <result column="name" property="name" />
          <result column="job" property="job" />
          <collection property="scores" javaType="java.util.ArrayList" ofType="Score">
              <id column="id" property="id" />
              <result column="num" property="num" />
              <association property="subject" javaType="Subject">
                   <id column="id" property="id" />
                   <result column="name" property="name" />
              </association>
          </collection>
     </resultMap>
     <select id="queryStudents2" resultMap="StudentMap2" >
          SELECT stu.id,stu.name name,stu.job,sco.id id,sco.num num,sub.id id,sub.name name
          FROM t_student stu LEFT JOIN t_score sco ON stu.id = sco.sid LEFT JOIN t_subject sub ON sco.subject = sub.id
     </select>

方案一：需要执行至少三次sql语句，开启三次事务才能完成本次请求。
方案二：需要执行一次sql语句，开启一次事务就能完成本次请求

方案二比方案一的效率要高，但是在使用的时候，方案一的代码可重用性要高

如果想要追求代码重用性可以选择方案一
如果比较在乎运行的性能可以选择方案二

升级版 https://blog.csdn.net/u010018421/article/details/77620145

Mybatis使用association与collection关联查询

association:复杂的类型联合,一个复杂的关联,就是在查出结果后,根据情况将其映射到一个类中(其是一个对象),即resultMap中的一个property对应的是一个类
collection:复杂的类型集合,查出结果后,根据情况将其映射到一个集合中,resultMap中的一个property的对象类型是一个集合对象(通常是List)
下面看我的mapper.xml文件

<?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" >
<!--namespace对应的是Mapper对应的interface-->
<mapper namespace="com.gm.dao.ext.OrderExtMapper" >
    <!--type对应的是该resultMap对应的实体类-->
    <resultMap id="BaseResultMap" type="com.gm.dao.entity.ext.OrderExtEntity" >
        <id column="order_id" property="orderId" jdbcType="VARCHAR" />
        <result column="customer_id" property="customerId" jdbcType="VARCHAR" />
        <result column="customer_name" property="customerName" jdbcType="VARCHAR"/>
        <result column="total_price" property="totalPrice" jdbcType="INTEGER" />
        <result column="order_desc" property="orderDesc" jdbcType="VARCHAR" />
        <result column="order_pay_amount" property="orderPayAmount" jdbcType="INTEGER" />
        <result column="order_pay_type" property="orderPayType" jdbcType="INTEGER" />
        <result column="order_change" property="orderChange" jdbcType="INTEGER" />
        <result column="order_status" property="orderStatus" jdbcType="VARCHAR" />
        <result column="created_person" property="createdPerson" jdbcType="VARCHAR" />
        <result column="created_timestamp" property="createdTimestamp" jdbcType="TIMESTAMP" />
        <result column="updated_person" property="updatedPerson" jdbcType="VARCHAR" />
        <result column="updated_timestamp" property="updatedTimestamp" jdbcType="TIMESTAMP" />
        <collection property="orderDetailList" ofType="com.gm.dao.entity.OrderDetail" column="order_id"
                    select="selectOrderDetails"/>
        <!--column属性代表传递给selectOrderRecords的参数-->
        <collection property="orderRecords" ofType="com.gm.dao.entity.OrderRecord" column="order_id"
                    select="selectOrderRecords"/>
    </resultMap>
    <resultMap id="OrderDetailMap" type="com.gm.dao.entity.ext.OrderDetailExtEntity">
        <id column="order_id" jdbcType="VARCHAR" property="orderId" />
        <id column="goods_id" jdbcType="VARCHAR" property="goodsId" />
        <result column="sales_count" jdbcType="INTEGER" property="salesCount" />
        <result column="sales_total_price" jdbcType="INTEGER" property="salesTotalPrice" />
        <result column="sales_desc" jdbcType="VARCHAR" property="salesDesc" />
        <result column="sales_goods_status" jdbcType="VARCHAR" property="salesGoodsStatus" />
        <!--引用其他文件的select(com.gm.dao.GoodsMapper.selectByPrimaryKey)-->
        <association property="goods" column="goods_id" javaType="com.gm.dao.entity.Goods"
                     select="com.gm.dao.GoodsMapper.selectByPrimaryKey"/>
    </resultMap>
    <sql id="Base_Column_List" >
        order_id, t.customer_id, total_price, order_desc, order_pay_amount, order_pay_type, order_change,
        order_status, t.created_person, t.created_timestamp, t.updated_person, t.updated_timestamp
    </sql>
    <!--该id,selectOrderDetails,与resultMap中的collection对应-->
    <select id="selectOrderDetails" resultMap="OrderDetailMap">
        SELECT
          order_id,
          goods_id,
          sales_count,
          sales_total_price,
          sales_desc,
          sales_goods_status
        FROM order_detail
        WHERE order_id = #{order_id}
    </select>
    <!--com.gm.dao.OrderRecordMapper.BaseResultMap,这种写法可以引用其他文件的BaseResultMap-->
    <select id="selectOrderRecords" resultType="com.gm.dao.entity.OrderRecord" resultMap="com.gm.dao.OrderRecordMapper.BaseResultMap">
        SELECT
          order_id,
          update_time,
          update_person,
          update_desc,
          order_origin_status,
          order_target_status
        FROM order_record
        WHERE order_id = #{order_id}
    </select>
    <select id="getOrderList" parameterType="java.util.Map" resultMap="BaseResultMap">
        SELECT
          <include refid="Base_Column_List"></include>,customer_name
        FROM `order` AS t
        LEFT JOIN customer AS t2 ON t.customer_id = t2.customer_id
        <where>
            1 = 1
            <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
                AND t.created_timestamp BETWEEN CONCAT('',#{startTime,jdbcType=VARCHAR},' 00:00:00')
                  AND CONCAT('',#{endTime,jdbcType=VARCHAR},' 23:59:59')
            </if>
            <if test="minPrice != 0 and maxPrice != 0">
                AND total_price BETWEEN #{minPrice,jdbcType=INTEGER} AND #{maxPrice,jdbcType=INTEGER}
            </if>
            <if test="customerId != null and customerId !=''">
                AND t.customer_id = #{customerId,jdbcType=VARCHAR}
            </if>
            <if test="orderStatus != null and orderStatus != ''">
                AND order_status = #{orderStatus,jdbcType=VARCHAR}
            </if>
        </where>
        ORDER BY t.updated_timestamp DESC
        LIMIT #{start,jdbcType=INTEGER},#{size,jdbcType=INTEGER}
    </select>
</mapper>

  <resultMap id="BaseResultMap" type="User" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="role_id" property="roleId" jdbcType="VARCHAR" />
  </resultMap>
 
 <resultMap id="ViewMap" type="UserView" extends="BaseResultMap">
    <result column="role_name" property="roleName" jdbcType="VARCHAR" />
 </resultMap>
    
 <select id="queryList" resultMap="ViewMap" parameterType="UserView">
    select
        u.id,
        u.name,
        u.role_id,
        r.name as role_name
    from user u
    left join role r
    on u.role_id = r.id 



//用户类
public class User {
    
    private String id;
    
    private String name;
    
    private String roleId;
 
    public String getId() {
        return id;
    }
 
    public void setId(String id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public String getRoleId() {
        return roleId;
    }
 
    public void setRoleId(String roleId) {
        this.roleId = roleId;
    }
    
}
//角色类
public class Role {
    
    private String id;
    
    private String name;
 
    public String getId() {
        return id;
    }
 
    public void setId(String id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
    
}
//接收查询结果的用户类
public class UserView extends User{
 
    private String roleName;
 
    public String getRoleName() {
        return roleName;
    }
 
    public void setRoleName(String roleName) {
        this.roleName= roleName;
    }

mybatis 中高级结果映射的官方文档示例

需要在select时，根据层级关系添加对应的父级的columnPrefix:ew_ewts_is_enable

https://www.jianshu.com/p/e7f283f15521

http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html

 其中使用的列映射是在xml中直接配置的，比如：

```

<resultMap id="detailedBlogResultMap" type="Blog">
<constructor>
<idArg column="blog_id" javaType="int"/>
</constructor>
<result property="title" column="blog_title"/>
<association property="author" javaType="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
<result property="password" column="author_password"/>
<result property="email" column="author_email"/>
<result property="bio" column="author_bio"/>
<result property="favouriteSection" column="author_favourite_section"/>
</association>
<collection property="posts" ofType="Post">
<id property="id" column="post_id"/>
<result property="subject" column="post_subject"/>
<association property="author" javaType="Author"/>
<collection property="comments" ofType="Comment">
<id property="id" column="comment_id"/>
</collection>
<collection property="tags" ofType="Tag" >
<id property="id" column="tag_id"/>
</collection>
<discriminator javaType="int" column="draft">
<case value="1" resultType="DraftPost"/>
</discriminator>
</collection>
</resultMap>

实际使用中我们经常在属性配置时使用 autoMapping, columnPrefix 来做字段映射，按照之前的想法直接在 select 时添加前缀的方式匹配不上， 需要在select时，根据层级关系添加对应的父级的columnPrefix:
例子：
<resultMap id="moduleDetail" type="etlModule" autoMapping="true">
<id property="moduleCode" column="module_code"/>
<result property="hospitalName" column="hospital_code"/>
<collection property="workflows" ofType="etlWorkflow" columnPrefix="ew_" autoMapping="true">
<id property="workflowCode" column="workflow_code"/>
<association property="connection" javaType="etlConnection" columnPrefix="ec_" autoMapping="true"/>
<association property="filter" javaType="etlWorkflowTokenFilter" columnPrefix="ewtf_" autoMapping="true"/>

```
        <collection property="selectList" ofType="etlWorkflowTokenSelect" columnPrefix="ewts_" autoMapping="true">
            <id property="workflowTokenCode" column="workflow_token_code"/>
        </collection>

        <collection property="fromOrJoinList" ofType="etlWorkflowTokenFromOrJoin" columnPrefix="ewtfj_" autoMapping="true">
            <id property="workflowTokenCode" column="workflow_token_code"/>
        </collection>
    </collection>
</resultMap>

```

select 语句：
SELECT
ew.workflow_code AS ew_workflow_code,
ew.workflow_name AS ew_workflow_name,
ew.workflow_sequence_default AS ew_workflow_sequence_default,
ew.workflow_sequence_customized AS ew_workflow_sequence_customized,
ew.module_code AS ew_module_code,
ew.component_code AS ew_component_code,
ew.is_enable AS ew_is_enable,
ew.is_default AS ew_is_default,
ew.created_at AS ew_created_at,
ew.updated_at AS ew_updated_at,
ec.connection_code AS ew_ec_connection_code,
ec.type AS ew_ec_type,
ec.url AS ew_ec_url,
ec.user AS ew_ec_user,
ec.engine_id AS ew_ec_engine_id,
ec.created_at AS ew_ec_created_at,
ec.updated_at AS ew_ec_updated_at,
ewts.workflow_token_code AS ew_ewts_workflow_token_code,
ewts.source_table_alias_name AS ew_ewts_source_table_alias_name,
ewts.source_column_name AS ew_ewts_source_column_name,
ewts.source_column_expression_default AS ew_ewts_source_column_expression_default,
ewts.source_column_expression_customized AS ew_ewts_source_column_expression_customized,
ewts.target_column_alias_name AS ew_ewts_target_column_alias_name,
ewts.is_enable AS ew_ewts_is_enable,
ewts.is_default AS ew_ewts_is_default,
ewts.created_at AS ew_ewts_created_at,
ewts.updated_at AS ew_ewts_updated_at,
ewtfj.workflow_token_code AS ew_ewtfj_workflow_token_code,
ewtfj.source_db_name AS ew_ewtfj_source_db_name,
ewtfj.source_table_name AS ew_ewtfj_source_table_name,
ewtfj.source_table_expression AS ew_ewtfj_source_table_expression,
ewtfj.source_table_alias_name AS ew_ewtfj_source_table_alias_name,
ewtfj.is_primary_table AS ew_ewtfj_is_primary_table,
ewtfj.join_type AS ew_ewtfj_join_type,
ewtfj.join_on_current_column_name AS ew_ewtfj_join_on_current_column_name,
ewtfj.join_on_right_table_alias_name AS ew_ewtfj_join_on_right_table_alias_name,
ewtfj.join_on_right_table_column_name AS ew_ewtfj_join_on_right_table_column_name,
ewtfj.join_on_expression AS ew_ewtfj_join_on_expression,
ewtfj.is_enable AS ew_ewtfj_is_enable,
ewtfj.is_default AS ew_ewtfj_is_default,
ewtfj.created_at AS ew_ewtfj_created_at,
ewtfj.updated_at AS ew_ewtfj_updated_at,
ewtfj.workflow_token_code AS ew_ewtf_workflow_token_code,
ewtf.common_filter_expression AS ew_ewtf_common_filter_expression,
ewtf.common_filter_expression_customized AS ew_ewtf_common_filter_expression_customized,
ewtf.incremental_filter_expression AS ew_ewtf_incremental_filter_expression,
ewtf.incremental_filter_expression_customized AS ew_ewtf_incremental_filter_expression_customized,
ewtf.range_filter_expression AS ew_ewtf_range_filter_expression,
ewtf.range_filter_expression_customized AS ew_ewtf_range_filter_expression_customized,
ewtf.is_enable AS ew_ewtf_is_enable,
ewtf.is_default AS ew_ewtf_is_default,
ewtf.created_at AS ew_ewtf_created_at,
ewtf.updated_at AS ew_ewtf_updated_at
...

注意其中select as 的字段名称，添加了resultMap中的层级关系。