mybatis中关于oracle数据库的like操作
假设code列表如下:001002 002001 100102
假设输入的code为:001
第一种:全局模糊查询,能将上述code全部查到
SELECT
*
FROM
user
WHERE
code like '%${code}%'
SELECT
*
FROM
user
WHERE
code like CONCAT('%',concat(#{code},'%'))
SELECT
*
FROM
user
WHERE
code like CONCAT(concat('%',#{code}),'%')
SELECT
*
FROM
user
WHERE
code like '%'||#{code}||'%'
第二种:向前模糊查询,查到的数据必须以001开头:001002
SELECT
*
FROM
user
WHERE name like '${code}%'
SELECT
*
FROM
user
WHERE
code like CONCAT('',concat(#{code},'%'))
SELECT
*
FROM
user
WHERE
code like CONCAT(concat('',#{code}),'%')
SELECT
*
FROM
user
WHERE
code like ''||#{code}||'%'
第三种:向后模糊查询,查到的数据必须以001结尾:002001
SELECT
*
FROM
user
WHERE name like '%${code}'
SELECT
*
FROM
user
WHERE
code like CONCAT(concat('%',#{code}),'')
SELECT
*
FROM
user
WHERE
code like CONCAT('%',concat(#{code},''))
SELECT
*
FROM
user
WHERE
code like '%'||#{code}||''
${}是一个拼接符,不建议使用,因为不安全,可能会引起sql注入。
建议使用其它几种