使用concat做多條件模糊查詢
- 字符串拼接
// java代碼
searchText = "%" + searchText + "%";
// sql
<!--查詢訂單-->
<select id="queryOrder" parameterType="Map" resultType="com.alibaba.fastjson.JSONObject">
SELECT
order_code,
send_linkman,
settled_amount
FROM
order_info
WHERE
account_code = #{accountCode}
AND
state = 1
<if test="searchText != null and searchText != '' ">
AND CONCAT(order_code,received_linkman)
LIKE #{searchText }
</if>
ORDER BY
pay_time
DESC
</select>
- 使用concat
// sql
<!--查詢訂單-->
<select id="queryOrder" parameterType="Map" resultType="com.alibaba.fastjson.JSONObject">
SELECT
order_code,
send_linkman,
settled_amount
FROM
order_info
WHERE
account_code = #{accountCode}
AND
state = 1
<if test="searchText != null and searchText != '' ">
AND CONCAT(order_code,received_linkman)
LIKE concat('%', #{searchText},'%')
</if>
ORDER BY
pay_time
DESC
</select>
3.bin標簽
<!--查詢訂單-->
<select id="queryOrder" parameterType="Map" resultType="com.alibaba.fastjson.JSONObject">
SELECT
order_code,
send_linkman,
settled_amount
FROM
order_info
WHERE
account_code = #{accountCode}
AND
state = 1
<if test="searchText != null and searchText != '' ">
<bind name="searchText" value=" '%' + searchText + '%' "/>
AND CONCAT(order_code,received_linkman)
LIKE #{searchText}
</if>
ORDER BY
pay_time
DESC
</select>
