sql总结

1.查询

a.xml查询所有

is_del=0查询没有被标记为删除

    <!--查询所有及单条查询-->
    <select id="findAllLiveInfoy" parameterType="com.greenchoice.search.api.dto.SpecialLiveinfoQueryDto" resultMap="BaseResultMap">
        SELECT * FROM t_special_live_info
        <where> 1=1 and is_del = 0
        <if test="id!=null">
            and id = #{id}
        </if>
        </where>
        order by sort asc
    </select>

b.xml条件查询,也可查询所有

selectStatus代表时间区间:预告——直播中——直播结束

满足条件查询

<!--查询所有-->
    <select id="findByType" parameterType="com.greenchoice.search.api.dto.SpecialLiveinfoQueryDto" resultMap="BaseResultMap">
        SELECT * FROM t_special_live_info
        <where> 1=1 and is_del = 0
            <if test="liveType!=null">
                and live_type = #{liveType}
            </if>
            <if test="liveTitle!=null">
                and live_title LIKE "%"#{liveTitle}"%"
            </if>
            <if test="selectStatus==0">
                <![CDATA[ and DATE_FORMAT(live_start_time, '%Y-%m-%d') >=  DATE_FORMAT(#{nowDate}, '%Y-%m-%d')]]>
            </if>
            <if test="selectStatus==1">
                <![CDATA[ and DATE_FORMAT(live_start_time, '%Y-%m-%d') <  DATE_FORMAT(#{nowDate}, '%Y-%m-%d') and DATE_FORMAT(live_close_time, '%Y-%m-%d') >  DATE_FORMAT(#{nowDate}, '%Y-%m-%d')]]>
            </if>
            <if test="selectStatus==2">
                <![CDATA[ and DATE_FORMAT(live_close_time, '%Y-%m-%d') <=  DATE_FORMAT(#{nowDate}, '%Y-%m-%d')]]>
            </if>
        </where>
    order by sort asc
    </select>

c.条件查询,也可查询所有

 @Override
    public List<GoodsEsLog> findAll(GoodsEsQuery goodsEsQuery) {
        QueryWrapper<GoodsEsLog> wrapper = new QueryWrapper<GoodsEsLog>();
        if (StringUtils.isNotBlank(goodsEsQuery.getLogsFlag())) {
            wrapper.eq("logs_flag", goodsEsQuery.getLogsFlag());
        }
        if (StringUtils.isNotBlank(goodsEsQuery.getLogsMsg())) {
            wrapper.eq("logs_msg", goodsEsQuery.getLogsMsg());
        }
        if (StringUtils.isNotBlank(goodsEsQuery.getLogsStatus())) {
            wrapper.eq("logs_status", goodsEsQuery.getLogsStatus());
        }
        if (StringUtils.isNotBlank(goodsEsQuery.getLogsType())) {
            wrapper.eq("logs_type", goodsEsQuery.getLogsType());
        }
        if (StringUtils.isNotBlank(goodsEsQuery.getParamIds())) {
            wrapper.eq("logs_paramIds", goodsEsQuery.getParamIds());
        }
        if (StringUtils.isNotBlank(goodsEsQuery.getLogsTime())) {
            wrapper.eq("date_format(logs_time,'%Y-%m-%d')", goodsEsQuery.getLogsTime());
        }
        if (StringUtils.isNotBlank(goodsEsQuery.getStartTime())) {
            wrapper.ge("date_format(logs_time,'%Y-%m-%d')", goodsEsQuery.getStartTime());
        }
        if (StringUtils.isNotBlank(goodsEsQuery.getEndTime())) {
            wrapper.le("date_format(logs_time,'%Y-%m-%d')", goodsEsQuery.getEndTime());
        }
        wrapper.orderByDesc("logs_time");
        List<GoodsEsLog> goodsEsLogList = goodsEsLogDao.selectList(wrapper);
        return goodsEsLogList;
    }

2.删除

新建立dto,包含字段isDel and ids

从ids中选取id进行遍历删除的标记。也可serviceImpl进行foreach. 

    <!--更新刪除-->
    <update id="delete" parameterType="com.greenchoice.search.api.dto.SpecialLiveinfoDeleteDto" >
        update t_special_live_info
        <if test="isDel!= null">
            set is_del = #{isDel}
        </if>
        where id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

 

©️2020 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页