• 项目的xml文件中经常使用的sql语句


    本文目录

    一、insert语句

    二、select查询语句

    三、批量添加

    四、与时间比较相关的


    项目中经常会用到的sql语句有:

    一、insert语句

    <!--保存用户信息-->
    <insert id="save">
            insert into mainsite_product_message
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="customerName != null and customerName != ''">
                    customer_name,
                </if>
                <if test="customerPhone != null and customerPhone != ''">
                    customer_phone,
                </if>
                <if test="customerEmail != null and customerEmail != ''">
                    customer_email,
                </if>
                <if test="createTime != null">
                    create_time,
                </if>
                <if test="customerStatus != null and customerStatus != ''">
                    customer_status,
                </if>
            </trim>
            <trim prefix="values (" suffix=")" suffixOverrides=",">
                <if test="customerName != null and customerName != ''">
                    #{customerName},
                </if>
                <if test="customerPhone != null and customerPhone != ''">
                    #{customerPhone},
                </if>
                <if test="customerEmail != null and customerEmail != ''">
                    #{customerEmail},
                </if>
                <if test="createTime != null">
                    #{createTime},
                </if>
                <if test="customerStatus != null and customerStatus != ''">
                    #{customerStatus},
                </if>
            </trim>
        </insert>
    
        <!--修改用户信息-->
        <update id="update">
    
        </update>
    
        <!--批量更改客户状态-->
        <update id="updateStatus">
            update mainsite_product_message set customer_status = 1 where id in
            <foreach collection="array" item="id" open="(" separator="," close=")" index="index">
                #{id}
            </foreach>
        </update>
    
        <!--根据主键删除留言,物理删除-->
        <delete id="deleteByPrimaryKey">
            delete from mainsite_product_message where id = #{id}
        </delete>
    
        <!--查询留言列表-->
        <select id="selectByCondition"
                resultType="com.uiotsoft.mainsite.module.productmessage.entity.ProductMessage">
            select
            id as id,
            site_id as siteId,
            site_name as siteName,
            customer_name as customerName,
            customer_phone as customerPhone,
            customer_email as customerEmail,
            product_want as productWant,
            create_time as createTime,
            customer_status as customerStatus,
            search_keyword as searchKeyword
            from mainsite_product_message
            <trim prefix="WHERE (" suffix=")" prefixOverrides="AND|OR">
                <if test="customerName != null and customerName != ''">
                    customer_name like CONCAT('%', #{customerName}, '%')
                </if>
                <if test="customerPhone != null and customerPhone != ''">
                    and customer_phone = #{customerPhone}
                </if>
                <if test="createTime != null">
                    and create_time = #{createTime}
                </if>
                <if test="customerEmail != null and customerEmail != ''">
                    and customer_email = #{customerEmail}
                </if>
                <if test="productWant != null and productWant != ''">
                    and product_want = #{productWant}
                </if>
                <if test="siteId != null and siteId != '' and siteId != 1">
                    and site_id = #{siteId}
                </if>
                <if test="siteName != null and siteName != ''">
                    and site_name = #{siteName}
                </if>
                <if test="customerStatus != null and customerStatus != ''">
                    and customer_status = #{customerStatus}
                </if>
                <if test="searchKeyword != null and searchKeyword != ''">
                    and search_keyword = #{searchKeyword}
                </if>
            </trim>
            ORDER BY create_time DESC
        </select>

     <trim><choose><when></when><otherwise></otherwise></choose></trim>

    二、select查询语句

    <select id="selectByContentListBySiteIdAndCategoryIds"   resultType="java.util.Map">
            SELECT
            c.*,
            m.*,
            u.avatar avatar,
            u.username username
            FROM
            site_model_content c LEFT JOIN  site_model_${tableName}  m
            ON
            c.content_id = m.content_id
            LEFT JOIN
            t_sys_user u ON u.user_id = c.user_id
            <trim prefix="WHERE ("  suffix=")" prefixOverrides="AND|OR">
                c.status=1
                <if test="siteId!=null">
                   AND  c.site_id = #{siteId}
                </if>
                <if test="categoryIds!=null">
                   AND
                    c.category_id in
                     <foreach item="item" index="index" collection="categoryIds" open="(" separator="," close=")">
                       #{item}
                     </foreach>
                </if>
                <if test='isRecommend!=null and isRecommend==1'>
                    AND
                    c.recommend = 1
                </if>
                <if test='whereParam!=null and valueParam!=null'>
                    AND  m.${whereParam} = #{valueParam}
                </if>
                <if test='isPic!=null and isPic==1'>
                    AND
                    c.thumb!=''
                </if>
                <if test='isPic!=null and isPic==0'>
                    AND
                    c.thumb=''
                </if>
                <if test='siteType!=null'>
                    AND
                    c.site_type like CONCAT('%', #{siteType}, '%')
                </if>
    
            </trim>
    
            <choose>
                <when test="orderBy==1">
                    order by c.content_id
                </when>
                <when test="orderBy==2">
                    order by c.inputdate DESC
                </when>
                <when test="orderBy==3">
                    order by c.inputdate
                </when>
                <when test="orderBy==4">
                    order by c.updatedate DESC
                </when>
                <when test="orderBy==5">
                    order by c.updatedate
                </when>
                <when test="isHot==1">
                    order by c.view_num DESC
                </when>
                <otherwise>
                    order by c.content_id DESC
                </otherwise>
            </choose>
        </select>

    三、批量添加

    <!-- 批量添加 -->
        <insert id="insertMore" useGeneratedKeys="true" keyProperty="siteId">
            INSERT INTO
                t_cms_site(
                site_name,
                `domain`,
                template,
                is_mobile,
                mobile_tpl,
                status
                )
                values
                <foreach item="item" collection="list" separator=",">
                    (#{item.siteName},#{item.domain},#{item.template},
                    #{item.isMobile},#{item.mobileTpl},#{item.status})
                </foreach>
        </insert>

    四、与时间比较相关的

    SELECT
            count(*)
            FROM
            crm_contract
            <where> isDel = 0
                <if test="departmentId != null and departmentId != null">
                    and departmentId LIKE CONCAT(#{departmentId},'%')
                </if>
                <if test="directorAccount != null and directorAccount !='' ">
                    and directorAccount = #{directorAccount}
                </if>
                <if test="executeState != null and executeState != ''">
                    and executeState = #{executeState}
                </if>
                <if test="auditState != null and auditState != ''">
                    and auditState = #{auditState}
                </if>
                <if test="contractType != null and contractType != ''">
                    and contractType = #{contractType}
                </if>
                <if test="categoryId != null and categoryId != ''">
                    and categoryId = #{categoryId}
                </if>
                <if test="customerName != null and customerName != ''">
                    and customerName like CONCAT("%",#{customerName},"%")
                </if>
                <if test="director != null and director != ''">
                    and director like CONCAT("%",#{director},"%")
                </if>
                <if test="signUser != null and signUser != ''">
                    and signUser like CONCAT("%",#{signUser},"%")
                </if>
                <if test="startDate != null">
                    <![CDATA[ and DATE_FORMAT(endDate,"%Y-%m-%d")  >= DATE_FORMAT(#{startDate},"%Y-%m-%d") ]]>
                </if>
                <if test="endDate != null">
                    <![CDATA[ and DATE_FORMAT(endDate,"%Y-%m-%d")  <= DATE_FORMAT(#{endDate},"%Y-%m-%d")]]>
                </if>
            </where>
            select
            c.*
            from crm_contract c left join crm_customer_info i on c.customerId =
            i.customerId
            <where>
                c.isDel = 0
                <if test="startDate != null and startDate != ''">
                    and c.endDate &gt;= #{startDate}
                </if>
                <if test="endDate != null and endDate != ''">
                    and c.endDate &lt;= #{endDate}
                </if>
                <if test="customerName != null and customerName != ''">
                    and c.customerName like CONCAT(#{customerName},"%")
                </if>
                <if test="director != null and director != ''">
                    and c.director like CONCAT(#{director},"%")
                </if>
                <if test="auditState != null and auditState != ''">
                    and c.auditState = #{auditState}
                </if>
            </where>
            limit #{start},#{end}

    拓展:

    Mysql动态SQL语句标签:https://blog.csdn.net/weixin_44299027/article/details/103258305

  • 相关阅读:
    LeetCode
    LeetCode
    ELK系列(5)
    ELK系列(4)
    ELK系列(3)
    ELK系列(2)
    ELK系列(1)
    计算机网络常见面试题总结
    mosquitto启动时Address already in use 和 一般的 Address already in use
    size和STL中的size_type
  • 原文地址:https://www.cnblogs.com/no8g/p/13415572.html
Copyright © 2020-2023  润新知