mybatis中的常见动态sql语句( 增删改查)
开发中写sql语句的增删改查是不可避免的,但只写单一sql语句,复用性不强,所以此时可以写动态sql。这里列举开发中常写的四种动态sql ,当做笔记以后直接拿。
1.查询
// 接口
public List<Map<String,Object>> getPerformScoreList(ScoringStandardVO scoringStandardVO);
<select id="getPerformScoreList" resultType="java.util.Map">
SELECT
es.PER_ID AS perId ,
es.PER_NAME AS perName ,
date_format(es.UPDATE_TIME,'%Y-%m-%d') AS updateTime, ps: 时间类型,展示 年月日
es.`STATUS` AS status ps:(注意最后没有逗号)
FROM ACT_RU_TASK ART
LEFT JOIN ACT_RU_IDENTITYLINK ARI ON ARI.TASK_ID_ = ART.ID_
LEFT JOIN evaluation_scheme_sun ess ON ess.sun_business_id = AHP.BUSINESS_KEY_
<where>
<if test="perName != null and perName != ''">
es.PER_NAME LIKE CONCAT('%',#{perName},'%')
</if>
<if test="status != null and status != ''">
AND status= #{status}
</if>
<if test="createTime != null "> ps:注意时间类型比较时,不能写 != "" ,空是字符串类型。
AND TO_DAYS(es.CREATE_TIME)= TO_DAYS(#{createTime})
</if>
<if test="createTime != null ">
AND convert(date,su.LOCK_TIME) >= convert(date,#{lockTime})
</if>
<if test="taskKeyList != null and taskKeyList.size > 0">
AND es.task_key in
<foreach collection="taskKeyList" item="key" open="(" close=")" separator=",">
#{key}
</foreach>
</if>
AND (( ARI.TYPE_ = 'candidate'
AND ARI.USER_ID_ = #{userId})
OR ART.ASSIGNEE_ = #{userId})
</where>
ORDER BY es.CREATE_TIME ASC
<trim prefix="LIMIT" suffixOverrides=","> ps:suffixOverrides=","意思是忽略,
<if test="pageStart != null and pageStart != ''">
#{pageStart},
</if>
<if test="pageEnd != null and pageEnd != ''">
#{pageEnd},
</if>
</trim>
</select>
代码比较全,复制时把所有ps去掉。
注意:sql语句里,不能加注释。<!-----> 也不行
<if test="taskKeyList != null and taskKeyList.size > 0">
AND es.task_key in
<foreach collection="taskKeyList" item="key" open="(" close=")" separator=",">
#{key}
</foreach>
</if>
PS: 单独拿这段解析。此时可以理解成当taskKeyList存在时,把其值一个一个添加到 AND es.task_key in()中。
java代码如下:
String a = "AND es.task_key in(";
Steing b = ")";
String c = " ";
if( taskKeyList != null && taskKeyList.size() > 0){
for(String key: taskKeyList){
c += key;
}
}
return a+c+b ;
2.新增
// 接口
public boolean insertMessageReceive(MessageReceivedInfo messageReceivedInfo);
<!-- .xml写法-->
<insert id="insertMessageReceive" parameterType="com.zhaoxi.tec.model.MessageReceivedInfo">
insert into message_received_info
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">id,</if>
<if test="messageId != null">message_id,</if>
<if test="receivedUser != null">received_user,</if>
<if test="receivedTime != null">received_time,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">#{id},</if>
<if test="messageId != null">#{messageId},</if>
<if test="receivedUser != null">#{receivedUser},</if>
<if test="receivedTime != null">#{receivedTime},</if>
</trim>
</insert>
- 一般增删改,返回值都是 boolean
- parameterType=“全路径” ,可以不写
- resultType=“java.lang.Integer” ,返回值为 String ,Integer ,List<Map<String,Object>>等时,需要写
3.修改
// 接口
public boolean updateMessageReceive(MessageReceivedVo messageReceivedVo);
<update id="updateMessageReceive" parameterType="com.zhaoxi.tec.model.MessageReceivedVo">
UPDATE message_received_info
<trim prefix="set" suffixOverrides=",">
<if test="id!=null and id != ''">id=#{id},</if>
<if test="receivedTime!=null">received_time=#{receivedTime},</if>
<if test="receivedStatus!=null and receivedStatus != ''">received_status=#{receivedStatus},</if>
</trim>
<trim prefix="WHERE" prefixOverrides="AND">
<if test="messageId != null and messageId != ''">
message_id=#{messageId}
</if>
<if test="id != null and id != ''">
AND id=#{id}
</if>
<if test="createTime != null ">
AND TO_DAYS(CREATE_TIME)= TO_DAYS(#{createTime})
</if>
</trim>
</update>
4. 删除
// 接口
public boolean deleteSunBusinessId(String perId,String deptId);
<delete id="deleteSunBusinessId">
DELETE FROM evaluation_scheme_sun
<trim prefix="WHERE" prefixOverrides="AND">
<if test="perId != null and perId != ''">
per_id=#{perId}
</if>
<if test="deptId != null and deptId != ''">
AND dept_id=#{deptId}
</if>
</trim>
</delete>