【SQL用法】Mybatis框架中的xml文件中经常使用的sql语句

灰太狼 2023-05-29 14:25 1阅读 0赞

**本文目录**

一、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 >= #{startDate}
                </if>
                <if test="endDate != null and endDate != ''">
                    and c.endDate <= #{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}

<select id="selectAll" resultType="java.util.Map">
            SELECT
            log_id AS logId,
            page_name AS pageName,
            template_name AS templateName,
            publish_status AS publishStatus,
            consuming_time AS consumingTime,
            C.description,
            create_time AS createTime,
            C.site_id AS siteId,
            C.category_id AS categoryId,
            C.site_name as siteName,
            D.category_name as categoryName
            FROM
                (
                    SELECT
                        log_id,
                        page_name,
                        template_name,
                        publish_status,
                        consuming_time,
                        A.description,
                        create_time,
                        A.site_id,
                        category_id,
                        site_name
                    FROM
                        mainsite_publish_log A
                    LEFT JOIN t_cms_site B ON A.site_id = B.site_id
                ) C
            LEFT JOIN t_cms_category D ON (C.category_id = D.category_id)
            <where>
                <if test="startTime != null">
                    <![CDATA[ and DATE_FORMAT(create_time,"%Y-%m-%d %H:%i:%s")  >= DATE_FORMAT(#{startTime},"%Y-%m-%d %H:%i:%s") ]]>
                </if>
                <if test="endTime != null">
                    <![CDATA[ and DATE_FORMAT(create_time,"%Y-%m-%d %H:%i:%s")  <= DATE_FORMAT(#{endTime},"%Y-%m-%d %H:%i:%s") ]]>
                </if>
            </where> order by log_id desc
        </select>

拓展：

**[Mysql动态SQL语句标签：][Mysql_SQL][https://blog.csdn.net/weixin\_44299027/article/details/103258305][Mysql_SQL]**

**完结！**

[Mysql_SQL]: https://blog.csdn.net/weixin_44299027/article/details/103258305