自定义SQL的demo和案例

背景和使用须知

背景

自定义SQL的底层使用的是Ibatis的SQL执行引擎，所以基本上可以兼容大部分的SQL语法，含动态标签

使用须知

需要遵循XML字符转义规则：

• 在<if test="">标签的属性内不需要转义，可以直接使用<、<=、>、>=
• 在其他在XML格式的SQL内容中，需要使用转义字符，列表如下：

| 字符  | 转义符    | 描述      |
|------|----------|-----------|
| <    | &lt;     | 小于号     |
| >    | &gt;     | 大于号     |
| &    | &amp;    | 和号      |
| '    | &apos;   | 单引号（可选）|
| "    | &quot;   | 双引号（可选）|

案例

SELECT示例-含动态标签

<select id="selectByCondition" parameterType="map" resultType="map">
    SELECT
        *
    FROM 
        employee
    <where>
        <if test="storeId != null">
        AND store_id = #{storeId, jdbcType=INTEGER}
        </if>
        <if test="employeeName != null and employeeName != ''">
        AND employee_name LIKE CONCAT('%', #{employeeName, jdbcType=VARCHAR}, '%')
        </if>
        <if test="gender != null">
        AND gender = #{gender, jdbcType=TINYINT}
        </if>
        <if test="phone != null and phone != ''">
        AND phone = #{phone, jdbcType=VARCHAR}
        </if>
        <if test="position != null and position != ''">
        AND position = #{position, jdbcType=VARCHAR}
        </if>
        <if test="roleId != null">
        AND role_id = #{roleId, jdbcType=INTEGER}
        </if>
        <if test="status != null">
        AND status = #{status, jdbcType=TINYINT}
        </if>
        <if test="startHireDate != null">
        AND hire_date &gt;= #{startHireDate, jdbcType=DATE}
        </if>
        <if test="endHireDate != null">
        AND hire_date &lt;= #{endHireDate, jdbcType=DATE}
        </if>
        <if test="idCard != null and idCard != ''">
        AND id_card = #{idCard, jdbcType=VARCHAR}
        </if>
    </where>
    ORDER BY gmt_create DESC
</select>

入参:

{
    "sqlCode": "8cbdd953-cdc325d3",
    "params": {
        "model": [
            "V1",
            "V2","V4"
        ]
    }
}

SELECT示例-静态条件

SELECT 
    * 
FROM 
    `employee` 
WHERE 
    `store_id` = 1 
    AND(`gender` = 57 OR `status` = 3) 
    AND `hire_date` BETWEEN '2004-01-01' AND '2004-12-31' 
    AND (`position` LIKE '%C07%' OR `role_id` = 997) 
    AND `phone` IS NOT NULL 
    AND `id_card` REGEXP '^[0-9A-Za-z]+$' 
    AND `gmt_create` > '2004-01-01 00:00:00' 
    AND (`employee_name` LIKE '%张三%' OR `employee_name` LIKE '%卫玄%') 
    ORDER BY `gmt_modified` DESC, `hire_date` ASC 
LIMIT 10;

UPDATE示例

<update id="updateById" parameterType="map">
    UPDATE
        employee
    SET
        store_id = #{storeId, jdbcType=INTEGER},
        employee_name = #{employeeName, jdbcType=VARCHAR},
        gender = #{gender, jdbcType=TINYINT},
        phone = #{phone, jdbcType=VARCHAR},
        id_card = #{idCard, jdbcType=VARCHAR},
        position = #{position, jdbcType=VARCHAR},
        role_id = #{roleId, jdbcType=INTEGER},
        username = #{username, jdbcType=VARCHAR},
        password = #{password, jdbcType=VARCHAR},
        salt = #{salt, jdbcType=VARCHAR},
        status = #{status, jdbcType=TINYINT},
        hire_date = #{hireDate, jdbcType=DATE},
        gmt_modified = #{gmtModified, jdbcType=TIMESTAMP}
    WHERE 
        employee_id = #{employeeId, jdbcType=INTEGER}
</update>

入参：

{
    "sqlCode": "8cbdd953-9d009aea",
    "params": {
        "unit": "米",
        "price": 100.00,
        "model": "V4_wx",
        "remark": "",
        "inventory": 619.00,
        "productName": "产品V17-2",
        "barcode": "04268444",
        "isStandard": 1,
        "status": "可销售",
        "productId":17
    }
}

INSERT

    <insert id="insert" parameterType="map" 
            useGeneratedKeys="true" keyProperty="productId">
        INSERT INTO product (
            product_name, model, barcode, unit, price, 
            inventory, status, is_standard, remark, 
            gmt_create, gmt_modified
        ) VALUES (
            #{productName, jdbcType=VARCHAR},
            #{model, jdbcType=VARCHAR},
            #{barcode, jdbcType=VARCHAR},
            #{unit, jdbcType=VARCHAR},
            #{price, jdbcType=DECIMAL},
            #{inventory, jdbcType=DECIMAL},
            #{status, jdbcType=VARCHAR},
            #{isStandard, jdbcType=TINYINT},
            #{remark, jdbcType=VARCHAR},
            #{gmtCreate, jdbcType=TIMESTAMP},
            #{gmtModified, jdbcType=TIMESTAMP}
        )
    </insert>

入参：

{
    "sqlCode": "8cbdd953-f14de06e",
    "params": {
        "unit": "米",
        "price": 100.00,
        "productId": 4,
        "model": "V4_wx",
        "remark": "",
        "inventory": 619.00,
        "productName": "产品V17",
        "barcode": "04268444",
        "isStandard": 1,
        "status": "可销售"
    }
}