This article is participating in “Java Theme Month – Java Debug Notes Event”, see < Event link > for more details.
[TOC]
At present, many web projects are developed on the basis of SSM framework. Among them, M refers to mybatis(IBatis). Mybatis refers to the avoidance of traditional JDBC and other tedious. In MyBatis we can just focus on the SQL itself. Don’t worry too much about the execution process. Greatly simplifies our normal development. Mybatis will have a lot to say. Today we will just look at the association tags provided in mybatis mapping.
Data preparation
The data structure
The stage_list in the following table stage_ORDER is a string consisting of ids from the stage table separated by commas. For example, stage_list=1,2,3 indicates that stage_order is associated with stages with ID 1,2, or 3. We need to query stageID=1 or 2 or 3 stage respectively for entity mapping display. In the next section we’ll look at the construction of entities
Entities to prepare
Base entity (corresponding to stage_ORDER single table)
- We don’t need to care about the annotations that appear in the following entities, which are the annotations of the Swagger framework and don’t need to be understood in this chapter. In other words we can delete the comment. It won’t affect our functionality.
@apiModel (description = "STAGE_ORDER") @table (name = "STAGE_ORDER") public class StageOrder {@id @apiModelProperty (" STAGE_ORDER") @notnull (message = "stageOrderId cannot be NULL ") private Long stageOrderId; @apimodelproperty (" intersectionId ") @notnull (message = "intersectionId cannot be NULL ") private Long intersectionId; @apiModelProperty (" phase name ") @notBlank (message = "phase name cannot be blank ") @bytelEngth (Max = 30, Message = "phase orderName length cannot exceed {Max}") private String orderName; @apiModelProperty (" stage list ") @notBlank (message = "stage list cannot be blank ") @bytelEngth (Max = 200, Message = "stageList length cannot exceed {Max}") private String stageList; public Long getStageOrderId() { return stageOrderId; } public void setStageOrderId(Long stageOrderId) { this.stageOrderId = stageOrderId; } public Long getIntersectionId() { return intersectionId; } public void setIntersectionId(Long intersectionId) { this.intersectionId = intersectionId; } public String getOrderName() { return orderName; } public void setOrderName(String orderName) { this.orderName = orderName == null ? null : orderName.trim(); } public String getStageList() { return stageList; } public void setStageList(String stageList) { this.stageList = stageList == null ? null : stageList.trim(); }}Copy the codeExtended entity
Public class StageOrderDto extends StageOrder {/** * private List<Stage> stageInfoList; public List<Stage> getStageInfoList() { return stageInfoList; } public void setStageInfoList(List<Stage> stageInfoList) { this.stageInfoList = stageInfoList; }}Copy the codeThe basic mapping
One to one
- With the above entity and the corresponding Table, how can we associate the Table with the entity in Mybatis? Look at the following configuration in mapper.xml
<resultMap id="BaseResultMap" type="com.jsits.xk.dto.StageOrderDto">
<id column="STAGE_ORDER_ID" jdbcType="NUMERIC" property="stageOrderId"/>
<result column="INTERSECTION_ID" jdbcType="NUMERIC" property="intersectionId"/>
<result column="ORDER_NAME" jdbcType="VARCHAR" property="orderName"/>
<result column="STAGE_LIST" jdbcType="VARCHAR" property="stageList"/>
<association column="STAGE_LIST" property="stageInfoList" select="selectStageInfos"/>
</resultMap>
Copy the code- But how does stageInfoList correspond to the database in resultMap? And what is an association? What is select in assocaition? Why is column in assocaition the same as above?
I think for friends who just contact the heart will have these questions.
- As mentioned above, a ResultMap is a bridge that connects entities to database tables. The ID attribute in a resultMap serves a unique purpose. You generally only need to be unique in the current XML. Because there are in every XML
namespaceTo determine the uniqueness of the current mapper.xml.
- ResultMap labels ID and result are used to map single-column values to entities corresponding to Type or attributes in the map. The ID is used by the primary key in the database. There will be federated primary keys in the database, and in the resultMap, two ID tags will correspond. There is also a point where ID is used as a unique identifier. That’s when the ID comes into play when you’re comparing it to other objects. Here, just like in a database, a primary key represents a piece of data. The same thing happens here. There is a caching mechanism in Mybatis. And that’s what we do with this ID. For example, Mybatis needs to cache this object. With the ID as the key, the json of the object is stored in the cache as a value.
More than a pair of
-
This one to many uses the BaseResultMap resultMap. Above we talk about ID and result is our ordinary simple single table query one-to-one relationship processing. But association is for associative queries. The Chinese translation of association means association. In mybatis, the function of association is to upload the STAGE_LIST value to selectStageInfos by default when the STAGE_LIST field is queried. We can’t get it with #{stage_list}, we have to get it with #{id}. The default is to use #{id}. But a robust framework is not so rigid. Let’s look at how to pass it dynamically.
-
Association is strictly supposed to be used for selectStageInfos queries to retrieve data of a basic type. However, in our requirement description above, we know that STAGE_LIST corresponds to the stage table with multiple data. So in StageOrderDto, stageInfoList is also carried by a list set. You can’t go wrong with association at this point. Mybatis base layer is different, the bottom layer is actually converted to the collection tag to implement. Should a collection correspond to the mapping of a collection. So perfectionists use the Collection tag when cascading queries. But association works in both cases.
Query the application
- With that said, let’s look at the implementation code of the query through two queries
<select id="selectStageOrderDtoListByIntersectionId" parameterType="java.lang.Long" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from stage_order
where INTERSECTION_ID=#{intersectionId}
</select>
<select id="selectStageInfos" parameterType="java.lang.String" resultType="com.jsits.xk.model.Stage">
select
<include refid="Stage_Column_List"/>
from stage
where stage_id in (#{id})
</select>
Copy the code- When we call selectStageOrderDtoListByIntersectionId by intersectionId query to the data, through the query to STAGE_LIST as call selectStageInfos the query conditions. In the selectStageInfos # {id} is STAGE_LIST selectStageOrderDtoListByIntersectionId query to.
Complex mapping
- But we usually enterprise development light such queries sometimes can not meet our needs. Common complex queries are: multi-cascade and federated primary key queries
Multiple mapping
- Multiple queries are simply associations or collections, as simple as that. This is something that some of you haven’t even thought about. The actual code is posted below. Because the following code is also needed in the union primary key section.
<sql id="Program_Column_List"> PHASE_LIST,PROGRAM_ID,PROGRAM_NAME,INTERSECTION_ID,STAGE_ID,STAGE_NAME,STAGE_SEQ,GREEN,RED_YELLOW,YELLOW,ALL_RED,MIN_GRE EN,MAX_GREEN </sql> <resultMap id="weekProgramResultMap" type="com.jsits.xk.dto.WeekPlanProgramDto"> <result column="PLAN_ID" property="planId" /> <result column="PLAN_NAME" property="planName" /> <association column="{intersectionId=INTERSECTION_ID,dayPlanNo=PLAN_ID}" property="dayPlanList" select="selectDayPlanInfosByPlanNo"/> </resultMap> <resultMap id="dayPlanResultMap" type="com.jsits.xk.dto.DayPlanAndProgramListDto"> <result property="timeDuration" column="time_duration" /> <result property="programId" column="program_id" /> <result property="stageOrderId" column="stage_order_id" /> <result property="dayPlanId" column="day_plan_id" /> <result property="phaseList" column="phase_list" /> <result property="programName" column="program_name" /> <result property="stageOrderName" column="order_name" /> <result property="controlModeId" column="control_mode_id" /> <result property="controlModeName" column="control_mode_name" /> <result property="cycle" column="cycle" /> <association column="program_id" property="programList" select="selectProgramInfosByProgramId"/> </resultMap> <select id="selectProgramInfosByProgramId" parameterType="long" resultType="com.jsits.xk.model.Program"> select <include refid="Program_Column_List" /> from program where program_id=#{id} </select> <select id="selectDayPlanInfosByPlanNo" parameterType="java.util.HashMap" resultMap="dayPlanResultMap" > select * from (select dp.day_plan_id, trim(REGEXP_REPLACE(to_char(dp.time_duration,'0000'),'([[:digit:]]{2})([[:digit:]]{2})','\1:\2')) as time_duration, pg.program_id, pg.program_name, so.order_name, so.stage_order_id, cm.control_mode_id, cm.control_mode_name, '120' as cycle from day_plan dp left join control_mode cm on dp.control_mode_id=cm.control_mode_id left join program pg on dp.program_id=pg.program_id left join stage_order so on dp.stage_order_id=so.stage_order_id where dp.day_plan_no=#{dayPlanNo} and dp.intersection_id=#{intersectionId} ) group by (day_plan_id,time_duration,program_id,program_name,order_name,stage_order_id,control_mode_id,control_mode_name,cycle) order by time_duration asc </select> <select id="selectWeekInfosByIntersectionId" parameterType="long" resultMap="weekProgramResultMap"> select #{intersectionId} as intersection_id,week.plan_name,week.plan_id from ( select 'Monday' as plan_name,intersection_id, Monday as plan_id from week_plan union select 'Tuesday' as Plan_name,intersection_id, Tuesday as plan_id from week_plan union select 'Wednesday' as plan_name,intersection_id,wendesday as Plan_id from week_plan union select 'Thursday' as plan_name,intersection_id, Thursday as plan_id from week_plan union select 'Friday' as plan_name,intersection_id, Friday as plan_id from week_plan union select 'Saturday' as Plan_name,intersection_id, Saturday as plan_id from week_plan union select 'Sunday' as plan_name,intersection_id, Sunday as plan_id from week_plan )week left join day_plan dp on dp.day_plan_no=week.plan_id where week.intersection_id=#{intersectionId} group by week.plan_id,week.plan_name,week.intersection_id order by week.plan_id </select>Copy the codeFederated primary key query
- Most of the time the primary key in our database is not a single field, but a combination of two or more fields to form a primary key. We used to call it the union primary key. We can see this in the code in multiple maps
weekProgramResultMapIntermediate linkselectDayPlanInfosByPlanNoThis SQL query. But what’s interesting about column is that it’s not a field that we’re familiar with. Instead, it is a string with curly braces at the beginning and end and multiple fields in between. This is the concept introduced in this section – federated primary key cascading queries. The value of the column property and we can see that if we look at itintersectionId=INTERSECTION_IDanddayPlanNo=PLAN_IDComposed of. We can view these as two maps, where the equals sign is key and the equals sign is value. Key is the key we reference in the new select, and the #{key} method. Value is the data from the original SELECT query. Value is also used to query new data. This is where we’re going up hereBasic Mapping sectionLimitations of saying you must use #{id}. So if we don’t want to use #{id}, we need to change the style {stageId=stage_list} for column to use #{stageId}.
Simple instructions
Work to meet the needs of Mybatis there are many god operation. We’ll have a chance to update it later. Mapping in MyBatis is not just about fields. And a funny reference. These will be updated at a later time.
