Use resultMap to implement ibatis composite Data Structure Query (1. Multi-attribute query; 2. List query included in the attribute), resultmapibatis

Use resultMap to implement ibatis composite Data Structure Query (1. Multi-attribute query; 2. List query included in the attribute), resultmapibatis

Take the order as an example (the order details include the basic information of the Order, delivery logistics information, and product information), and directly go to the Code:

1. Multi-attribute Query

Java entity

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" is the preceding class. The type of the attribute logistics is a composite type Logistics.

The following is the resultMap of Order details:

<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 can be queried using the method of object access. consignee is the attribute of logistics and logistics is the attribute of orderDetail. note: The column name should be consistent with the column name queried by the SQL statement.

The SQL query statement is as follows:

   <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. the attribute contains list queries.

OrderDetail has an attribute orderGoods of the List compound type. You should add the attribute select = "getOrderGoodsList" to the attribute definition of resultMap. getOrderGoodsList is also an SQL query.

<result property="orderGoods.listData" select="wxShopOrder.getOrderGoodsList" column="orderSn"/>

Note: The preceding column = "orderSn" refers to orderSn as the SQL query parameter. Check the getOrderGoodsList query.

<! -- Obtain the order item list using the order number --> <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>

Quick answers to ibatis's resultMap multi-table joint query and how to deal with DTO

I have never used ibatis in my school. Today, I got my job and thought it was a simple business. However, the framework for multi-table joint query is ibatis + struts1 + ejb. Here we need to write DTO to match the fields in the database table one by one. Video information table: vedioID vedioInfo ipAndGallery (video ID, video information, ip address and port number) Personnel Grouping table: personGroupID personID personGroup (group ID, personnel ID, group information) permission control table (intermediate table): IMpower vedioID personGroupID permission ID, video ID, and group ID must be displayed on the jsp page: [group information, video information, ip address, and port number. Select. personGroup, B. vedioInfo, B. ipAndGalleryfrom jk_persongroup a, jk_vedio B, jk_impower cwhere. personGroupID = c. personGroupID AND B. vedioID = c. vedioID doubt: ------ solution -------------------------------------------------------- doubt: it is best to create a domain for queries like this multi-table joint query so that your SQL can return this domain. ------ solution ---------------------------------------------------------- you can map the returned field to a javabean or directly return a map. ------ Solution ---------------------------------------------------------- attributes of the as main table fields in one table. In this way, you can use the primary table. attribute value. The primary table attribute must be consistent with the field type of the slave table.

Ibatis configuration file configure database table multi-Table connection query configuration resultMap each table has the same name column name, how to distinguish? Solution

Just get a different column name.