使用resultMap实现ibatis复合数据结构查询(1.多重属性查询;2.属性中含有列表查询)，resultmapibatis
以订单为例(订单详情包含了订单的基本信息,配送物流信息,商品信息),直接上代码:
1.多重属性查询
java实体
public class OrderDetail {
@XmlElement(required = true)
protected String orderSn;
@XmlElement(required = true)
protected String orderAmount;
@XmlElement(required = true)
protected String orderStatus;
@XmlElement(required = true)
protected String orderAddTime;
@XmlElement(required = true)
protected Logistics logistics;
@XmlElement(required = true)
protected OrderGoods orderGoods;
@XmlElement(required = true)
protected List<OrderDetail> listData;
<pre name="code" class="java"> ... getter and setter...}
class="orderDetail" 即为上面的类,有个属性logistics的类型是复合类型Logistics
下面是订单详情的resultMap:
<resultMap id="OrderInfoMap" class="orderDetail"> <result property="orderSn" column="orderSn"/> <result property="orderAmount" column="orderAmount"/> <result property="orderStatus" column="orderStatus"/> <result property="orderStatus" column="orderAddTime"/> <result property="logistics.consignee" column="consignee"/> <!-- <result property="logistics.receiver" column="receiver"/> --> <result property="logistics.country" column="country"/> <result property="logistics.province" column="province"/> <result property="logistics.city" column="city"/> <result property="logistics.district" column="district"/> <result property="logistics.address" column="address"/> <result property="logistics.mobile" column="mobile"/> <result property="logistics.shippingName" column="shippingName"/> <result property="orderGoods.listData" select="wxShopOrder.getOrderGoodsList" column="orderSn"/> </resultMap>
其中"<result property="logistics.consignee" column="consignee"/> ",logistics.consignee就可以用对象访问的写法来查询,consignee是logistics的属性,logistics是orderDetail的属性.注意:column的名称应该与sql语句查询的列名一致.
<select id="getOrderDetail" parameterClass="string" resultMap="OrderInfoMap" >
select
order_sn as orderSn,
order_amount as orderAmount,
order_status as orderStatus,
add_time as orderAddTime,
consignee,
country,
b.region_name province,
c.region_name city,
d.region_name district,
address,
mobile,
shipping_name as shippingName
from ecs_order_info o
left join ecs_region b on o.province=b.region_id
left join ecs_region c on o.city=c.region_id
left join ecs_region d on o.district=d.region_id
where order_sn=#orderSn#
</select>
2.属性中含有列表查询
orderDetail有个属性orderGoods的类型是List复合类型,应在resultMap的属性定义中添加属性select="getOrderGoodsList",getOrderGoodsList也是一个sql查询
<result property="orderGoods.listData" select="wxShopOrder.getOrderGoodsList" column="orderSn"/>
注意:上面column="orderSn"指的是orderSn作为sql的查询参数. 看下这个 getOrderGoodsList查询
<!-- 通过订单号获取订单商品列表 -->
<select id="getOrderGoodsList" parameterClass="string" resultMap="orderGoodsMap">
select goods_name as goodsName,
goods_number as goodsNumber
from ecs_order_goods where order_id=
(select order_id from ecs_order_info where order_sn=#orderSn#)
</select>刚进公司，学校期间没用过ibatis。今天领到任务，想着是挺简单的一个业务。但涉及到多表联查，用的框架是ibatis+struts1+ejb。这里需要写DTO 来与数据库表中字段一一对应。视频信息表：vedioID vedioInfo ipAndGallery（视频ID，视频信息，ip 与端口号）人员分组表：personGroupID personID personGroup（分组ID,人员ID，分组信息）权限控制表（中间表）：IMpower vedioID personGroupID 权限ID、视频ID、分组ID）需要在jsp 页面展示：【分组信息，视频信息、ip 与端口号 】这三个字段。select a.personGroup,b.vedioInfo,b.ipAndGalleryfrom jk_persongroup a,jk_vedio b,jk_impower cwhere a.personGroupID=c.personGroupID AND b.vedioID=c.vedioID疑惑：------解决方案--------------------------------------------------------疑惑：像这种多表联合查询的最好是在建一个 domain 专门用来查询这样你 sql 就可以返回这个domain.------解决方案--------------------------------------------------------你把返回的字段映射到一个javabean 里面，或者直接返回一个map，都可以。------解决方案--------------------------------------------------------以一张表为主表 别的表的字段 as 主表的属性。 这样就可以用 主表.属性取值。主表属性要与从表字段类型一致
as 一下弄个不同的列名就行了