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中的层级关系。
网友评论