mybatis中兼容Oracle、Sqlserver、mysql的模糊查询（like语句）的解决办法

由于公司的java项目，最近有mysql往Oracle迁移的工作，另外一些同时兼容Oracle、Sqlserver双数据库的系统，在新功能开发时也遇到了关于模糊查询的坑，这里总结一下。

1、mybatis中拼接like语句最规范的写法：

S.SSV_VALUE like  CONCAT('%',CONCAT(#{filters.param},'%'))

之所以要用两个CONCAT，是因为Oracle中，CONCAT函数最多只能拼接两个变量，所以前后加上%，则需要两个CONCAT函数执行。

如果是mysql，则CONCAT支持多个变量拼接，下面这样写是没有问题的：

S.SSV_VALUE like  CONCAT('%',#{filters.param},'%')

但Oracle这样写就会报参数个数无效的错误

如果Oracle想要拼接多个变量，需要用||

规范写法为：

S.SSV_VALUE like  '%'||#{filters.param}||'%'

 sqlserver2008版本不支持CONCAT函数！！也不支持双竖线。只有2012版本开始才支持CONCAT函数。

2008版本的sqlserver需要使用+号拼接字符串：

Oracle使用+号，会先将字符串转义，如果转义不了则会报错。所以，如果是数字，则是做加法：

 非数字报错：

而Mysql则非常有趣的是，会自动屏蔽掉非数字部分做加法，并且会自动赋值0，如：

所以，只有sqlserver的规范写法，是用+号：

S.SSV_VALUE like  '%'+ #{filters.param} +'%'

2、兼容三种数据库的直接写法：

有人就会想，如果直接把变量拼接在两个百分号以内，不就不存在不同数据库之间的拼接函数差异化问题了吗？

S.SSV_VALUE like  '%#{filters.param}%'

很抱歉的说，这样的写法是会报错的。因为 #{} 写法，mybatis会调用jdbc的预编译方式处理，而且#{}在引号内，是不会识别的，所以实际上like后面接的内容就是'%#{filters.param}%'，#{filters.param}并不会转成实际的参数值。

然后就有人说，那不用预编译的方式好了，用${}方式，直接替换：

S.SSV_VALUE like  '%${filters.param}%'

这种方式本身没什么大问题，很多mybatis的封装框架，常用的tk.mybatis也包含这种写法。但问题在于：

filters.param是我前段传过来的内容，这样直接加入sql语句，必定会导致SQL注入问题！

所以，这种${}的方式还是不推荐，建议用第一种方式，先判断数据库类型，再通过不同的SQL的ID去调用就可以了。

3、第一种方式，在查询条件包含单引号时，查询结果正常：

 

但是包含%时，就不正常了：

很明显，我传入的%按关键字处理了，所以把所有结果都查出来了，而不是作为值的一部分，只查出%123这条数据。

最后想来想去，还是干脆直接拼接sql语句来的更为好操控。简而言之呢，就是用like...escape语法，通过转义的方式，拼接sql。

唯独需要注意的是，中括号在oracle下不是关键字，所以转义会报错，而mysql和sqlserver下则需要转移。通用方法如下：

public class StringUtil {

    /**
     * 需要替换的字符mysql、sqlserver
     */
    private static String[] searchList = {"%","_","[","]","/"};

    /**
     * 需要替换的字符oracle
     */
    private static String[] searchListForOracle = {"%","_","/"};
    /**
     * 替换后的字符mysql、sqlserver
     */
    private static String[] replacementList = {"/%","/_","/[","/]", "//"};

    /**
     * 替换后的字符oracle
     */
    private static String[] replacementListForOracle = {"/%","/_", "//"};


    /**
     * %_[]/这个几个符号需要转义，其中/是定义的转移符
     * 同时在like语句后需要添加  ESCAPE '/'
     * '这个符号不要转，因为后续处理使用预编译方式
     * @param value
     * @return
     */
    public static String escapeForSql(String value) {
        if(value ==null || "".equals (value)) {
            return value;
        }
        int index=0;
        for(String oldStr: searchList){
            value = value.replace (oldStr, replacementList[index++]);
        }

        return "%"+value+"%";
    }

    /**
     * Oracle符号替换（不需要替换[]，否则会报错）
     * %_/这个几个符号需要转义，其中/是定义的转移符
     * 同时在like语句后需要添加  ESCAPE '/'
     * '这个符号不要转，因为后续处理使用预编译方式
     * @param value
     * @return
     */
    public static String escapeForSql_Oracle(String value) {
        if(value ==null || "".equals (value)) {
            return value;
        }
        int index=0;
        for(String oldStr: searchListForOracle){
            value = value.replace (oldStr, replacementListForOracle[index++]);
        }

        return "%"+value+"%";
    }

    public static void main(String[] args) {
        String newValue = "中国[]\\'/?22_123%1中";

        System.out.println("mysql,sqlserver转换结果："+escapeForSql(newValue));
        System.out.println("oracle转换结果："+escapeForSql_Oracle(newValue));
    }
}

转好之后，只要在mybatis中这样写：

S.SSV_VALUE like  #{filters.param}   ESCAPE '/'

不用担心关键字问题，同时也不用担心SQL注入问题，还不用在mybatis中分别为三种数据库写三个SQL脚本了，啧啧，真好~

看看效果：

条件：

结果：

条件：

结果：