Mybatis - dynamic SQL statement - if usage - MySQL series learning notes

select

Based on the parameters passed in : If the user name is not empty , Then fuzzy query is performed on the user name , If the mailbox is not empty , Then take the mailbox as the query criteria

 <select id="selectByUser" parameterType="com.tgb.mybatis.entity.SysUser" resultType="com.tgb.mybatis.entity.SysUser">
select
user_name "userName",
user_password "userPassword",
user_info "userInfo",
head_img "headImg",
create_time "createTime"
from
sys_user
where
1=1
<if test="userName != null and userName !=''">
and user_name like CONCAT('%',#{userName},'%')
</if>
<if test="userEmail != null and userEmail != ''">
and user_email =#{userEmail}
</if>
</select>

update

find id by xxx This data , Attribute by attribute judgment , If the attribute is not empty , Then update

 <update id="updateById">
update sys_user
SET
<if test="userName != null and userName !=''">
user_name = #{userName},
</if>
<if test="userPassword != null and userPassword !=''">
user_password = #{userPassword},
</if>
<if test="userEmail != null and userEmail !=''">
user_email = #{userEmail},
</if>
<if test="userInfo != null and userInfo !=''">
user_info = #{userInfo},
</if>
<if test="headImg != null and headImg !=''">
head_img = #{headImg},
</if>
<if test="createTime != null and createTime !=''">
create_time =#{createTime},
</if>
id = #{id}
where
id = #{id}
</update>

insert

 <insert id="insert">
insert into sys_user(
user_name,
user_password,
<if test="userEmail !=null and userEmail != ''">
user_email,
</if>
user_info,
head_img,
create_time
)
VALUES
(#{userName},
#{userPassword},
<if test="userEmail != null and userEmail !=''">
user_email = #{userEmail},
</if>
#{userInfo},
#{headImg, jdbcType=BLOB},
#{createTime,jdbcType=TIMESTAMP})
</insert>

About where1=1 Application , See my blog ——> Let's go to Dachang series 】 In depth understanding of MySQL in where 1 = 1 Usefulness

Please bring the original link to reprint ,thank
Similar articles

2021-10-14

2021-10-14

2021-10-14

2021-10-14

2021-10-14

2021-10-14

2021-10-14

2021-10-14

2021-10-14

2021-10-14

2021-10-14

2021-10-14

2021-10-14