Custom SQL demos and examples

Background and usage notes

Background

Custom SQL runs on the iBATIS SQL execution engine under the hood, so it is compatible with most SQL syntax, including dynamic tags.

Usage notes

You need to follow XML character escaping rules:

• Inside the attributes of the <if test=""> tag, no escaping is needed — you can use <, <=, >, >= directly
• In other XML-formatted SQL content, you must use escape characters, as listed below:

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

Examples

SELECT example with dynamic tags

<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>

Input parameters:

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

SELECT example with static conditions

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 example

<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>

Input parameters:

{
    "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>

Input parameters:

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