preface

Recently, I am learning to do Java project, but I have forgotten many things, and I have come across new things about Mybatis. Before I used collection, I only used the two attributes ofType and property, but today I came into contact with the two new attributes. Column and select attributes, so I want to know the usage of these two attributes, so I record this in case I forget and forget later. Like association, collection elements also have two forms, which are introduced as follows:

A:

<? The 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" > < mapper namespace="com.abc.mapper.TeacherMapper"> <select id="getById" parameterType="int" resultMap="supervisorResultMap"> select t.id t_id, t.name t_name, t.gender t_gender, t.research_area t_research_area, t.title t_title, s.id, s.name, s.gender, s.major, s.grade from teacher t left join student s on 1=1 where t.id = #{id} and s.supervisor_id = t.id </select> <! The container is designed for supervisorResultMap, supervisorResultMap, supervisorResultMap, supervisorResultMap, supervisorResultMap, supervisorResultMap, supervisorResultMap, supervisorResultMap, supervisorResultMap property="name" column="t_name"/> <result property="gender" column="t_gender"/> <result property="researchArea" column="t_research_area"/> <result property="title" column="t_title"/> <! Note that in the select statement above, the field name/alias should be the same as the column property below. OfType refers to the type of elements contained in a collection, <collection property="supStudents" ofType="Student"> < ID property="id" column="id"/> <result property="name" column="name"/> <result property="gender" column="gender"/> <result property="major" column="major"/> <result property="grade" column="grade"/> <! -- Mapping students' advisor attributes, It is designed for handling the container itself --> <association Property ="supervisor" resultMap="supervisorResultMap"/> </ Collection > </resultMap> </mapper>Copy the code

This approach is the most basic. There are only property and ofType attributes in the collection, where the supStudents of property represents a supStudents set in the Teacher entity, and the entity class is as follows: Then the collection and the property of the field and the Student in this class, of course, also want to and query field, s.i d, s.n ame, s.g ender, s.m ajor, s.g rade, actually also corresponding to a Student of this class, So don’t worry about that

package com.imooc.pojo.bo; public class Teacher { private Integer id; private String name; private String gender; private String researchArea; private String title; private List<Student> supStudents; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public String getResearchArea() { return researchArea; } public void setResearchArea(String researchArea) { this.researchArea = researchArea; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public List<Student> getSupStudents() { return supStudents; } public void setSupStudents(List<Student> supStudents) { this.supStudents = supStudents; }}Copy the code

Disadvantages: The disadvantage of this approach compared to the previous approach is that the student entity map is embedded in the teacher entity map, so the student entity map cannot be reused.

Way 2

In this way, a single SELECT statement is used to load the associated entity, and then the select statement is referenced in the collection element. OfType refers to the type of the element contained in the collection. This attribute is indispensable. The column attribute refers to the select statement that passes the value of the orderId column of myOrdersVO in the previous myOrdersVO SELECT statement as a parameter to the following getSubItems to be referenced. This attribute is indispensable, and is referenced in the form of: Select id from the same namespace, so reference id directly

<? The 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" > < mapper namespace="com.imooc.mapper.OrdersMapperCustom"> <resultMap id="myOrdersVO" type="com.imooc.pojo.vo.MyOrdersVO"> <id column="orderId" property="orderId"/> <result column="createdTime" property="createdTime"/> <result column="payMethod" property="payMethod"/> <result column="realPayAmount" property="realPayAmount"/> <result column="postAmount" property="postAmount"/> <result column="orderStatus" property="orderStatus"/> <result column="isComment" property="isComment"/> <collection property="subOrderItemList" select="getSubItems" column="orderId" ofType="com.imooc.pojo.vo.MySubOrderItemVO"> <result column="itemId" property="itemId"/> <result column="itemName" property="itemName"/> <result column="itemImg" property="itemImg"/> <result column="itemSpecId" property="itemSpecId"/> <result column="itemSpecName" property="itemSpecName"/> <result column="buyCounts" property="buyCounts"/> <result column="price" property="price"/> </collection> </resultMap> <select id="queryMyOrders" resultMap="myOrdersVO" parameterType="Map"> SELECT od.id as orderId, od.created_time as createdTime, od.pay_method as payMethod, od.real_pay_amount as realPayAmount, od.post_amount as postAmount, os.order_status as orderStatus, od.is_comment as isComment FROM orders od LEFT JOIN order_status os on od.id = os.order_id WHERE od.user_id = #{paramsMap.userId} AND od.is_delete = 0 <if test="paramsMap.orderStatus ! = null"> and os.order_status = #{paramsMap.orderStatus} </if> ORDER BY od.updated_time ASC </select> <select id="getSubItems" parameterType="String" resultType="com.imooc.pojo.vo.MySubOrderItemVO"> select oi.item_id as itemId, oi.item_name as itemName, oi.item_img as itemImg, oi.item_spec_name as itemSpecName, oi.buy_counts as buyCounts, oi.price as price from order_items oi where oi.order_id = #{orderId} </select> <select id="queryMyOrdersDoNotUse" resultMap="myOrdersVO" parameterType="Map"> SELECT od.id as orderId, od.created_time as createdTime, od.pay_method as payMethod, od.real_pay_amount as realPayAmount, od.post_amount as postAmount, os.order_status as orderStatus, oi.item_id as itemId, oi.item_name as itemName, oi.item_img as itemImg, oi.item_spec_name as itemSpecName, oi.buy_counts as buyCounts, oi.price as price FROM orders od LEFT JOIN order_status os on od.id = os.order_id LEFT JOIN order_items oi ON od.id = oi.order_id WHERE od.user_id = #{paramsMap.userId} AND od.is_delete = 0 <if test="paramsMap.orderStatus ! = null"> and os.order_status = #{paramsMap.orderStatus} </if> ORDER BY od.updated_time ASC </select> <select id="getMyOrderStatusCounts" parameterType="Map" resultType="int"> SELECT count(1) FROM orders o LEFT JOIN order_status os on o.id = os.order_id WHERE o.user_id = #{paramsMap.userId} AND os.order_status = #{paramsMap.orderStatus} <if test="paramsMap.isComment ! = null"> and o.is_comment = #{paramsMap.isComment} </if> </select> <select id="getMyOrderTrend" parameterType="Map" resultType="com.imooc.pojo.OrderStatus"> SELECT os.order_id as orderId, os.order_status as orderStatus, os.created_time as createdTime, os.pay_time as payTime, os.deliver_time as deliverTime, os.success_time as successTime, os.close_time as closeTime, os.comment_time as commentTime FROM orders o LEFT JOIN order_status os on o.id = os.order_id WHERE o.is_delete = 0 AND o.user_id = #{paramsMap.userId} AND os.order_status in (20, 30, 40) ORDER BY os.order_id DESC </select> </mapper>Copy the code