<?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"><mappernamespace="com.smcv.xyx.user.operation.domain.ShufflePicMapper"><resultMaptype="com.smcv.xyx.user.operation.domain.ShufflePicDO"id="ShufflePicDOResultMap"><idcolumn="id"property="id"jdbcType="INTEGER"/><resultcolumn="title"property="title"jdbcType="VARCHAR"/><resultcolumn="pic_path"property="picPath"jdbcType="VARCHAR"/><resultcolumn="pic_url"property="picUrl"jdbcType="VARCHAR"/><resultcolumn="url_type"property="urlType"jdbcType="INTEGER"/><resultcolumn="wechat_id"property="wechatId"jdbcType="VARCHAR"/><resultcolumn="pic_order"property="picOrder"jdbcType="INTEGER"/><resultcolumn="effictive_start_time"property="effictiveStartTime"jdbcType="TIMESTAMP"/><resultcolumn="effictive_end_time"property="effictiveEndTime"jdbcType="TIMESTAMP"/><resultcolumn="create_id"property="createId"jdbcType="BIGINT"/><resultcolumn="update_id"property="updateId"jdbcType="BIGINT"/><resultcolumn="is_deleted"property="deleted"jdbcType="BIT"/><resultcolumn="create_time"property="createTime"jdbcType="TIMESTAMP"/><resultcolumn="update_time"property="updateTime"jdbcType="TIMESTAMP"/></resultMap><sqlid="allColumnFields">
id,title,pic_path,pic_url,url_type,wechat_id,pic_order,effictive_start_time,effictive_end_time,create_id,update_id,is_deleted,create_time,update_time
</sql><!-- 只有varchar char text类型 做 !='' 判断,其他类型时只做!=null 判断 0或fasle与空''是相同的,在mybatis中的动态语句中--><!-- xml转义字符需要 <![CDATA[ ]]> 标签--><!-- 包含(创建时间|修改时间字段区间查询)--><sqlid="dynamicWhereFieldsUseQuery"><where><iftest="id != null "> AND id=#{id,jdbcType=INTEGER} </if><iftest="title != null and title != ''"> AND title=#{title,jdbcType=VARCHAR} </if><iftest="picPath != null and picPath != ''"> AND pic_path=#{picPath,jdbcType=VARCHAR} </if><iftest="picUrl != null and picUrl != ''"> AND pic_url=#{picUrl,jdbcType=VARCHAR} </if><iftest="urlType != null "> AND url_type=#{urlType,jdbcType=INTEGER} </if><iftest="wechatId != null and wechatId != ''"> AND wechat_id=#{wechatId,jdbcType=VARCHAR} </if><iftest="picOrder != null "> AND pic_order=#{picOrder,jdbcType=INTEGER} </if><iftest="effictiveStartTime != null "> AND effictive_start_time=#{effictiveStartTime,jdbcType=TIMESTAMP} </if><iftest="effictiveEndTime != null "> AND effictive_end_time=#{effictiveEndTime,jdbcType=TIMESTAMP} </if><iftest="createId != null "> AND create_id=#{createId,jdbcType=BIGINT} </if><iftest="updateId != null "> AND update_id=#{updateId,jdbcType=BIGINT} </if><iftest="deleted != null "> AND is_deleted=#{deleted,jdbcType=BIT} </if><iftest="startCreateTime != null"><![CDATA[ AND create_time>=#{startCreateTime,jdbcType=Date} ]]></if><iftest="endCreateTime != null"><![CDATA[ AND create_time<=#{endCreateTime,jdbcType=Date} ]]></if><iftest="startUpdateTime != null"><![CDATA[ AND update_time>=#{startUpdateTime,jdbcType=TIMESTAMP} ]]></if><iftest="endUpdateTime != null"><![CDATA[ AND update_time<=#{endUpdateTime,jdbcType=TIMESTAMP} ]]></if></where></sql><selectid="selectById"parameterType="int"resultMap="ShufflePicDOResultMap">
SELECT
<includerefid="allColumnFields"/>
FROM
t_shuffle_pic
WHERE
id = #{id,jdbcType=INTEGER} AND is_deleted =0
</select><insertid="insert"parameterType="com.smcv.xyx.user.operation.domain.ShufflePicDO"useGeneratedKeys="true"statementType="PREPARED"keyProperty="id">
INSERT INTO t_shuffle_pic(
id,
title,
pic_path,
pic_url,
url_type,
wechat_id,
pic_order,
effictive_start_time,
effictive_end_time,
create_id,
update_id,
is_deleted,
create_time,
update_time
)values(
#{id,jdbcType=INTEGER},
#{title,jdbcType=VARCHAR},
#{picPath,jdbcType=VARCHAR},
#{picUrl,jdbcType=VARCHAR},
#{urlType,jdbcType=INTEGER},
#{wechatId,jdbcType=VARCHAR},
#{picOrder,jdbcType=INTEGER},
#{effictiveStartTime,jdbcType=TIMESTAMP},
#{effictiveEndTime,jdbcType=TIMESTAMP},
#{createId,jdbcType=BIGINT},
#{updateId,jdbcType=BIGINT},
#{deleted,jdbcType=BIT},
#{createTime,jdbcType=TIMESTAMP},
#{updateTime,jdbcType=TIMESTAMP}
)
</insert><insertid="insertSelective"parameterType="com.smcv.xyx.user.operation.domain.ShufflePicDO"keyProperty="id"useGeneratedKeys="true"statementType="PREPARED">
insert into t_shuffle_pic
<trimprefix="("suffix=")"suffixOverrides=","><iftest="id != null">
id,
</if><iftest="title != null">
title,
</if><iftest="picPath != null">
pic_path,
</if><iftest="picUrl != null">
pic_url,
</if><iftest="urlType != null">
url_type,
</if><iftest="wechatId != null">
wechat_id,
</if><iftest="picOrder != null">
pic_order,
</if><iftest="effictiveStartTime != null">
effictive_start_time,
</if><iftest="effictiveEndTime != null">
effictive_end_time,
</if><iftest="createId != null">
create_id,
</if><iftest="updateId != null">
update_id,
</if><iftest="deleted != null">
is_deleted,
</if><iftest="createTime != null">
create_time,
</if><iftest="updateTime != null">
update_time,
</if></trim><trimprefix="values ("suffix=")"suffixOverrides=","><iftest="id != null">
#{id,jdbcType=INTEGER},
</if><iftest="title != null">
#{title,jdbcType=VARCHAR},
</if><iftest="picPath != null">
#{picPath,jdbcType=VARCHAR},
</if><iftest="picUrl != null">
#{picUrl,jdbcType=VARCHAR},
</if><iftest="urlType != null">
#{urlType,jdbcType=INTEGER},
</if><iftest="wechatId != null">
#{wechatId,jdbcType=VARCHAR},
</if><iftest="picOrder != null">
#{picOrder,jdbcType=INTEGER},
</if><iftest="effictiveStartTime != null">
#{effictiveStartTime,jdbcType=TIMESTAMP},
</if><iftest="effictiveEndTime != null">
#{effictiveEndTime,jdbcType=TIMESTAMP},
</if><iftest="createId != null">
#{createId,jdbcType=BIGINT},
</if><iftest="updateId != null">
#{updateId,jdbcType=BIGINT},
</if><iftest="deleted != null">
#{deleted,jdbcType=BIT},
</if><iftest="createTime != null">
#{createTime,jdbcType=TIMESTAMP},
</if><iftest="updateTime != null">
#{updateTime,jdbcType=TIMESTAMP},
</if></trim></insert><updateid="updateById"parameterType="com.smcv.xyx.user.operation.domain.ShufflePicDO"statementType="PREPARED">
UPDATE t_shuffle_pic
SET
title = #{title,jdbcType=VARCHAR},
pic_path = #{picPath,jdbcType=VARCHAR},
pic_url = #{picUrl,jdbcType=VARCHAR},
url_type = #{urlType,jdbcType=INTEGER},
wechat_id = #{wechatId,jdbcType=VARCHAR},
pic_order = #{picOrder,jdbcType=INTEGER},
effictive_start_time = #{effictiveStartTime,jdbcType=TIMESTAMP},
effictive_end_time = #{effictiveEndTime,jdbcType=TIMESTAMP},
create_id = #{createId,jdbcType=BIGINT},
update_id = #{updateId,jdbcType=BIGINT},
is_deleted = #{deleted,jdbcType=BIT},
update_time = #{updateTime,jdbcType=TIMESTAMP}
WHERE
id = #{id,jdbcType=INTEGER} AND is_deleted =0
</update><updateid="logicDeleteById"parameterType="java.util.Map">
update t_shuffle_pic set is_deleted=1,update_id= #{updateId,jdbcType=BIGINT},update_time=now() WHERE id = #{id,jdbcType=INTEGER} AND is_deleted =0
</update><updateid="logicDeleteByIds"parameterType="java.util.Map">
update t_shuffle_pic set is_deleted=1,update_id= #{updateId,jdbcType=BIGINT},update_time=now() WHERE id in
<foreachitem="item"index="index"collection="ids"open="("separator=","close=")">
#{item}
</foreach>
AND is_deleted =0
</update><updateid="updateDynamic"parameterType="com.smcv.xyx.user.operation.domain.ShufflePicDO">
UPDATE t_shuffle_pic
<set><iftest="title != null and title != ''">title=#{title,jdbcType=VARCHAR},</if><iftest="picPath != null and picPath != ''">pic_path=#{picPath,jdbcType=VARCHAR},</if><iftest="picUrl != null and picUrl != ''">pic_url=#{picUrl,jdbcType=VARCHAR},</if><iftest="urlType != null ">url_type=#{urlType,jdbcType=INTEGER},</if><iftest="wechatId != null and wechatId != ''">wechat_id=#{wechatId,jdbcType=VARCHAR},</if><iftest="picOrder != null ">pic_order=#{picOrder,jdbcType=INTEGER},</if><iftest="effictiveStartTime != null ">effictive_start_time=#{effictiveStartTime,jdbcType=TIMESTAMP},</if><iftest="effictiveEndTime != null ">effictive_end_time=#{effictiveEndTime,jdbcType=TIMESTAMP},</if><iftest="createId != null ">create_id=#{createId,jdbcType=BIGINT},</if><iftest="updateId != null ">update_id=#{updateId,jdbcType=BIGINT},</if><iftest="deleted != null ">is_deleted=#{deleted,jdbcType=BIT},</if><iftest="updateTime != null ">update_time=#{updateTime,jdbcType=TIMESTAMP},</if></set>
WHERE id = #{id,jdbcType=INTEGER} AND is_deleted =0
</update><selectid="dynamicSelectList"resultMap="ShufflePicDOResultMap"parameterType="com.smcv.xyx.user.operation.query.ShufflePicQuery">
SELECT
<includerefid="allColumnFields"/>
FROM
t_shuffle_pic
<includerefid="dynamicWhereFieldsUseQuery"/>
ORDER BY update_time DESC
limit #{limitSize}
</select><selectid="dynamicSelectEffectiveList"resultMap="ShufflePicDOResultMap"parameterType="com.smcv.xyx.user.operation.query.ShufflePicQuery">
SELECT
<includerefid="allColumnFields"/>
FROM
t_shuffle_pic
<includerefid="dynamicWhereFieldsUseQuery"/>
AND effictive_start_time > getdate()
AND effictive_end_time < getdate()
ORDER BY pic_order ACS
</select><selectid="dynamicSelectOne"resultMap="ShufflePicDOResultMap"parameterType="com.smcv.xyx.user.operation.query.ShufflePicQuery">
SELECT
<includerefid="allColumnFields"/>
FROM
t_shuffle_pic
<includerefid="dynamicWhereFieldsUseQuery"/>
limit #{limitSize}
</select><selectid="dynamicSelectCount"resultType="long"parameterType="com.smcv.xyx.user.operation.query.ShufflePicQuery">
SELECT
count(id)
FROM
t_shuffle_pic
<includerefid="dynamicWhereFieldsUseQuery"/></select><selectid="dynamicSelectPageQuery"resultMap="ShufflePicDOResultMap"parameterType="com.smcv.xyx.user.operation.query.ShufflePicQuery">
SELECT
<includerefid="allColumnFields"/>
FROM
t_shuffle_pic
<includerefid="dynamicWhereFieldsUseQuery"/>
ORDER BY pic_order
<iftest="start != null and pageSize!=null">
LIMIT #{start},#{pageSize}
</if></select><selectid="selectByIds"resultMap="ShufflePicDOResultMap"parameterType="java.util.List">
SELECT
<includerefid="allColumnFields"/>
FROM
t_shuffle_pic
WHERE id in
<foreachitem="item"index="index"collection="list"open="("separator=","close=")">
#{item}
</foreach>
AND is_deleted =0
</select></mapper>
foreach
<updateid="logicDeleteByIds"parameterType="java.util.List">
update t_praise set is_delete=1,update_time=now() WHERE id in
<foreachitem="item"index="index"collection="list"open="("separator=","close=")">
#{item}
</foreach>
AND is_delete=0
</update>
<selectid="selectCount"resultType="long"parameterType="com.smcv.xyx.mifa.query.TopicQuery">
SELECT
count(id)
FROM
t_topic
<where><iftest="topicTypeId != null "> AND topic_type_id=#{topicTypeId,jdbcType=INTEGER} </if><iftest="content != null and content != ''"> AND tt.content=#{content,jdbcType=LONGVARCHAR} </if><iftest="userId != null "> AND user_id=#{userId,jdbcType=BIGINT} </if><iftest="topicStatus != null "> AND topic_status=#{topicStatus,jdbcType=INTEGER} </if><iftest="aduitStatus != null "> AND aduit_status=#{aduitStatus,jdbcType=INTEGER} </if><iftest="isInform != null "> AND is_inform=#{isInform,jdbcType=INTEGER} </if><iftest="highLight != null "> AND high_light=#{highLight,jdbcType=INTEGER} </if><iftest="aduitRemark != null and aduitRemark != ''"> AND aduit_remark=#{aduitRemark,jdbcType=VARCHAR} </if><iftest="reportReasonId != null and reportReasonId != ''"> AND report_reason_id=#{reportReasonId,jdbcType=INTEGER} </if><iftest="sysUserId != null "> AND sys_user_id=#{sysUserId,jdbcType=BIGINT} </if><iftest="isDelete != null "> AND is_delete=#{isDelete,jdbcType=INTEGER} </if><iftest="startCreateTime != null"><![CDATA[ AND create_time>=#{startCreateTime,jdbcType=TIMESTAMP} ]]></if><iftest="endCreateTime != null"><![CDATA[ AND create_time<=#{endCreateTime,jdbcType=TIMESTAMP} ]]></if><iftest="startUpdateTime != null"><![CDATA[ AND update_time>=#{startUpdateTime,jdbcType=TIMESTAMP} ]]></if><iftest="endUpdateTime != null"><![CDATA[ AND update_time<=#{endUpdateTime,jdbcType=TIMESTAMP} ]]></if><iftest="userIds != null">
AND user_id in
<foreachitem="item"index="index"collection="userIds"open="("separator=","close=")">
#{item}
</foreach></if></where></select>
leftjoin
<selectid="selectListByQuery"parameterType="com.smcv.xyx.mifa.query.TopicCommentQuery"resultType="com.smcv.xyx.mifa.model.vo.TopicCommentVO">
select *,u1.nick_name as userNickName,u2.nick_name as reUserNickName
from t_topic_comment t
LEFT JOIN t_user u1 on t.user_id = u1.id
LEFT JOIN t_user u2 on t.reply_user_id = u2.id
where t.is_delete = 0 and t.topic_id = #{topicId,jdbcType=INTEGER}
<iftest="aduitStatus != null">
and t.aduit_status = #{aduitStatus,jdbcType=INTEGER}
</if>
order by t.create_time
</select><selectid="selectListByQuery"parameterType="com.smcv.xyx.mifa.query.TopicCommentQuery"resultType="com.smcv.xyx.mifa.model.vo.TopicCommentVO">
select *,u1.nick_name as userNickName,u2.nick_name as reUserNickName
from t_topic_comment t
LEFT JOIN t_user u1 on t.user_id = u1.id
LEFT JOIN t_user u2 on t.reply_user_id = u2.id
where t.is_delete = 0 and t.topic_id = #{topicId,jdbcType=INTEGER}
<iftest="aduitStatus != null">
and t.aduit_status = #{aduitStatus,jdbcType=INTEGER}
</if>
order by t.create_time
</select>
<selectid="dynamicSelectEffectiveList"resultMap="ShufflePicDOResultMap"parameterType="com.smcv.xyx.user.operation.query.ShufflePicQuery">
SELECT
<includerefid="allColumnFields"/>
FROM
t_shuffle_pic
<includerefid="dynamicWhereFieldsUseQuery"/>
AND effictive_start_time < NOW()
AND effictive_end_time > NOW()
ORDER BY pic_order ASC
</select>
模糊查询
<selectid="matchSecretByAgentId"resultType="com.smcv.xyx.wework.dto.master.FixCodeDTO">
select * from t_fix_code
where code_type = #{type,jdbcType=DECIMAL}
and special_value like concat('%',#{agentId},'%')
</select>