Mybatis的xml文件中sql语句记录

客官°小女子只卖身不卖艺 2022-11-16 15:10 227阅读 0赞

## 时间段查询 ##

方法一：
    <if test="knowledge.strDate != null">
        AND `create_time` >= #{knowledge.strDate}
    </if>
    <if test="knowledge.endDate != null">
        AND `create_time` <= #{knowledge.endDate}
    </if>
    
    方法二：
    <if test="knowledge.strDate != null">
        <![CDATA[AND `create_time` >= #{knowledge.strDate}]]>
    </if>
    <if test="knowledge.endDate != null">
        <![CDATA[AND `create_time` >= #{knowledge.endDate }]]>
    </if>

## 模糊查询 ##

<if test="knowledge.knowledgeName != null">
        AND `knowledge_name` LIKE concat(concat("%",#{knowledge.knowledgeName}),"%")
    </if>

## 将某字段的字符串类型转换为数值类型 ##

<select id="getAfterThree" parameterType="string" resultType="string">
            SELECT
                b.dept_name
            FROM
                biz_data_counts_detail a
                LEFT JOIN blade_dept b ON a.dept_id = b.id
            WHERE
                a.is_deleted = 0
            <if test="parentId !=null and parentId !=''">
                AND a.parent_id = #{parentId}
            </if>
            ORDER BY
                (a.answer_rate + 0) ASC
            LIMIT 3
        </select>

## 随机取3条数据 ##

SELECT *
    FROM `biz_knowledge`
    WHERE id >=
          ((SELECT MAX(id) FROM `biz_knowledge`) - (SELECT MIN(id) FROM `biz_knowledge`)) * RAND() +
          (SELECT MIN(id) FROM `biz_knowledge`)
    LIMIT 3

## 查询是否符合某个条件，返回id即可 ##

不需要返回整个对象

<select id="judgeExist" resultType="long">
            SELECT
                id
            FROM
                biz_temporary_meeting
            WHERE
                is_deleted = 0
            <if test="temporaryMeeting.id != null and temporaryMeeting.id != ''">
                AND id = #{temporaryMeeting.id}
            </if>
            <if test="temporaryMeeting.selectDateTime != null and temporaryMeeting.selectDateTime != ''">
                <![CDATA[AND start_time <= CONCAT(#{temporaryMeeting.selectDateTime})]]>
                <![CDATA[AND end_time >= CONCAT(#{temporaryMeeting.selectDateTime})]]>
            </if>
        </select>