MyBatis 模糊查询

一、对于Oracle数据库

1. 字符串连接符 ||

MyBatis 映射文件写法：

<select id="findByNickname" parameterType="java.lang.String" 
    resultMap="articleResult">
		select *
		from "article", "user"
		where "user_id" = "user"."id"
		and "nickname" like '%'||#{nickname}||'%'
</select>

日志sql输出：

select * 
from "article", "user" 
where "user_id" = "user"."id" 
and "nickname" like '%'||?||'%'

2. Oracle拼接函数 concat()

MyBatis 映射文件写法：

<select id="findByNickname" parameterType="java.lang.String" 
    resultMap="articleResult">
		select *
		from "article", "user"
		where "user_id" = "user"."id"
		and "nickname" like concat(concat('%', #{nickname}), '%')
</select>

日志sql输出：

select * 
from "article", "user" 
where "user_id" = "user"."id" 
and "nickname" like concat(concat('%', ?), '%') 

3. MyBatis ${}

MyBatis 映射文件写法：

<select id="findByNickname" parameterType="java.lang.String"
    resultMap="articleResult">
		select *
		from "article", "user"
		where "user_id" = "user"."id"
		and "nickname" like '%${nickname}%'
</select>

日志sql输出：

select * 
from "article", "user" 
where "user_id" = "user"."id" 
and "nickname" like '%中%' 

二、对于MySQL数据库

1. 字符串拼接即可

MyBatis 映射文件写法：

<select id="findByNickname" resultMap="articleResult"   
    parameterType="java.lang.String">
		select *
		from article, `user`
		where user_id = `user`.id
		and nickname like "%"#{nickname}"%"
	</select>

日志sql输出：

select * 
from article, `user` 
where user_id = `user`.id
and nickname like "%"?"%"

2. 字符串连接符 ||

2.1 双引号

MyBatis 映射文件写法：

<select id="findByNickname" parameterType="java.lang.String" 
    resultMap="articleResult">
		select *
		from article, `user`
		where user_id = `user`.id
		and nickname like "%"||#{nickname}||"%"
	</select>

日志sql输出：

select * 
from article, `user`
where user_id = `user`.id
and nickname like "%"||?||"%"  

2.2 单引号

MyBatis 映射文件写法：

<select id="findByNickname" parameterType="java.lang.String" 
    resultMap="articleResult">
		select *
		from article, `user`
		where user_id = `user`.id
		and nickname like '%'||#{nickname}||'%'
	</select>

日志sql输出：

select * 
from article, `user`
where user_id = `user`.id
and nickname like '%'||?||'%' 

3. MySQL拼接函数concat() [单引号，双引号包裹%都可以]

MyBatis 映射文件写法：

<select id="findByNickname" resultMap="articleResult"   
    parameterType="java.lang.String">
		select *
		from article, `user`
		where user_id = `user`.id
		where nickname like concat("%", #{nickname}, "%")
	</select>

日志sql输出：

select * 
from article, `user` 
where user_id = `user`.id 
and nickname like concat("%", ?, "%")

4. MyBatis ${} [单引号，双引号包裹参数都可以]

MyBatis 映射文件写法：

<select id="findByNickname" resultMap="articleResult"   
    parameterType="java.lang.String">
		select *
		from article, `user`
		where user_id = `user`.id
		where nickname like '%${nickname}%'
	</select>

日志sql输出：

select * 
from article, `user` 
where user_id = `user`.id 
and nickname like '%晨%'

参考网址
https://blog.csdn.net/zhenwei1994/article/details/81876278点我跳转