Mybatis dynamically added (insert) and modified (update)
Dynamic operation tags are used here
The trim tag is a formatted tag, mainly used to splice SQL conditional statements (adding or ignoring prefixes or suffixes), which can complete the function of set or where tags.
The four main properties of the tag:
-
prefix: Prefix overwrites and adds its content -
suffix: The suffix overwrites and adds its content -
prefixOverrides: Conditions for prefix judgment -
suffixOverrides: Conditions for suffix judgment
New
<insert useGeneratedKeys="true" keyProperty="intCowId">
insert into cowtest
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="intPastureId != null and '' != intPastureId">
intPastureId,
</if>
<if test="varCowCode != null and '' != varCowCode">
varCowCode,
</if>
<if test="cSex != null and '' != cSex">
cSex,
</if>
<if test="addSource != null and '' != addSource">
addSource,
</if>
<if test="sireClass != null and '' != sireClass">
sireClass,
</if>
<if test="dateLeave != null and '' != dateLeave">
dateLeave,
</if>
<if test="intLeaveClass != null and '' != intLeaveClass">
intLeaveClass,
</if>
<if test="intReason != null and '' != intReason">
intReason,
</if>
<if test="intCurBar != null and '' != intCurBar">
intCurBar,
</if>
<if test="intCurBarName != null and '' != intCurBarName">
intCurBarName,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="intPastureId != null and '' != intPastureId">
#{intPastureId},
</if>
<if test="varCowCode != null and '' != varCowCode">
#{varCowCode},
</if>
<if test="cSex != null and '' != cSex">
#{cSex},
</if>
<if test="addSource != null and '' != addSource">
#{addSource},
</if>
<if test="sireClass != null and '' != sireClass">
#{sireClass},
</if>
<if test="dateLeave != null and '' != dateLeave">
#{dateLeave},
</if>
<if test="intLeaveClass != null and '' != intLeaveClass">
#{intLeaveClass},
</if>
<if test="intReason != null and '' != intReason">
#{intReason},
</if>
<if test="intCurBar != null and '' != intCurBar">
#{intCurBar},
</if>
<if test="intCurBarName != null and '' != intCurBarName">
#{intCurBarName},
</if>
</trim>
</insert>The last comma "," will be ignored here
Revise
<update >
update cowtest
<trim prefix="SET" suffixOverrides=",">
<if test="dateBirthDate != null and '' != dateBirthDate">
dateBirthDate= #{dateBirthDate},
</if>
<if test="decBirWeight != null and '' != decBirWeight">
decBirWeight= #{decBirWeight},
</if>
<if test="decQuotiety != null and '' != decQuotiety">
decQuotiety= #{decQuotiety},
</if>
<if test="intCurBar != null and '' != intCurBar">
intCurBar= #{intCurBar},
</if>
<if test="intCurBarName != null and '' != intCurBarName">
intCurBarName= #{intCurBarName},
</if>
<if test="intCurFetal != null and '' != intCurFetal">
intCurFetal= #{intCurFetal},
</if>
<if test="intBreed != null and '' != intBreed">
intBreed= #{intBreed},
</if>
<if test="cSex != null and '' != cSex">
cSex= #{cSex},
</if>
</trim>
where varCowCode= #{varCowCode}
</update>also
The trim tag can also omit the prefix and in the where statement. Of course, we can also use where 1=1 and follow the judgment statement.
Mybatis determines whether to use insert or update
This situation will be encountered in actual development. It means that a data needs to be judged whether it is added or updated. The normal development idea is to first check whether the ID of this data already exists in the database. Existence is update, otherwise it is insert. Mybatis is also implemented based on this idea. Let's take an example below.
Specific implementation
For example, the front desk saves a teacher's information to the teacher's entity bean, and then needs to save this information to the database. At this time, you need to determine whether the teacher's information is to be updated or inserted.
The teacher information entity bean is as follows:
public class Teacher {
private int teacherId;//Teacher ID
private String teacherName;//Teacher name
private int count;//mybatis determines whether the ID exists
public int getTeacherId() {
return teacherId;
}
public void setTeacherId(int teacherId) {
= teacherId;
}
public String getTeacherName() {
return teacherName;
}
public void setTeacherName(String teacherName) {
= teacherName;
}
public int getCount() {
return count;
}
public void setCount(int count) {
= count;
}
}You can see that in the entity bean, in addition to normal teacher information, there is an additional count. It is used by mybatis to determine whether the teacherId exists. If it exists, the number of existing ones will be saved in the count. Of course, the Id is generally the primary key, and all counts are generally 1.
Let’s take a look at the mybatis mapping file below.
<insert parameterType="">
<selectKey keyProperty="count" resultType="int" order="BEFORE">
select count(*) from Teacher where teacher_id = #{teacherId}
</selectKey>
<if test="count > 0">
update event
<set>
<if test="teacherName!= null" >
teacher_name= #{teacherName},
</if>
</set>
<where>
teacher_id = #{teacherId}
</where>
</if>
<if test="count==0">
insert into teacher(teacher_id,teacher_name) values (#{teacherId},#{teacherName})
</if>
</insert>You can see that the implementation idea of mybatis is to first check whether the ID exists, and judge whether it is insert or update based on the count.
illustrate
1. The implementation principle is to selectKey to do the first query and then make judgments based on the results. Therefore, the order="BEFORE" here is necessary. Because of BEFORE, the intermediate value cannot be temporarily stored through the <bind> tag, and can only add attributes to the entry parameter to store.
2. As for the example above, the entity class requires that the count attribute (can be another name). Otherwise, the result of selectKey cannot be saved. If the parameter is a Map type, there is no such restriction.
3. This method is just used to implement the query by selectKey, but if you also need to obtain the sequence or self-increment id through selectKey, it will be a lot of trouble (oracle is troublesome, other things that support self-increment are still easy). For example, in the previous article, I used selectKey to obtain the primary key Id.
4. It is recommended to check and learn the usage of selectKey separately.
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.