Java學習-066-Mybatis + druid 報錯： com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'code LIKE "%"?"%"

 查詢資料庫時，報錯資訊如下所示： 

1 ### Error querying database.  Cause: java.sql.SQLException: sql injection violation, syntax error: syntax error, error in :'code LIKE "%"?"%"
 2          
 3        ', expect RPAREN, actual QUES pos 103, line 3, column 31, token QUES : select count(0) from (SELECT * FROM api_case WHERE delflag = 0
 4          
 5             AND code LIKE "%"?"%"
 6          
 7          
 8          
 9          
10             AND valid = ?) tmp_count
11 ### The error may exist in file [E:\office\script\jcca-dtops\jcca-dtops-admin\target\classes\mybatis\mapper\api\ApiCaseMapper.xml]
12 ### The error may involve com.jcca.mapper.api.ApiCaseMapper.findAllByConditions_COUNT
13 ### The error occurred while executing a query
14 ### SQL: select count(0) from (SELECT * FROM api_case WHERE delflag = 0                       AND code LIKE "%"?"%"                                                     AND valid = ?) tmp_count
15 ### Cause: java.sql.SQLException: sql injection violation, syntax error: syntax error, error in :'code LIKE "%"?"%"
16          
17        ', expect RPAREN, actual QUES pos 103, line 3, column 31, token QUES : select count(0) from (SELECT * FROM api_case WHERE delflag = 0
18          
19             AND code LIKE "%"?"%"
20          
21          
22          
23          
24             AND valid = ?) tmp_count
25 ; uncategorized SQLException; SQL state [null]; error code [0]; sql injection violation, syntax error: syntax error, error in :'code LIKE "%"?"%"
26          
27        ', expect RPAREN, actual QUES pos 103, line 3, column 31, token QUES : select count(0) from (SELECT * FROM api_case WHERE delflag = 0
28          
29             AND code LIKE "%"?"%"
30          
31          
32          
33          
34             AND valid = ?) tmp_count; nested exception is java.sql.SQLException: sql injection violation, syntax error: syntax error, error in :'code LIKE "%"?"%"
35          
36        ', expect RPAREN, actual QUES pos 103, line 3, column 31, token QUES : select count(0) from (SELECT * FROM api_case WHERE delflag = 0
37          
38             AND code LIKE "%"?"%"
39          
40          
41          
42          
43             AND valid = ?) tmp_count] with root cause
44 com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'code LIKE "%"?"%"
45          
46        ', expect RPAREN, actual QUES pos 103, line 3, column 31, token QUES
47     at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:287)
48     at com.alibaba.druid.sql.parser.SQLParser.accept(SQLParser.java:295)
49     at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.parseTableSource(MySqlSelectParser.java:248)
50     at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.parseFrom(MySqlSelectParser.java:89)
51     at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.query(MySqlSelectParser.java:193)
52     at com.alibaba.druid.sql.parser.SQLSelectParser.query(SQLSelectParser.java:236)
53     at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:88)
54     at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseSelect(MySqlStatementParser.java:284)
55     at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:248)
56     at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:182)
57     at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:624)
58     at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:578)
59     at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:793)
60     at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:259)
61     at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
62     at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:930)
63     at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122)
64     at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
65     at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341)
66     at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:349)      

檢視相應的Mapper，相應的查詢語句如下所示：

<select id="findAllByConditions" resultType="com.example.TestCase">
        SELECT * FROM test_case WHERE delflag = 0
        <if test="code != '' and code != null">
            AND code LIKE "%"#{code}"%"
        </if>
        <if test="name != '' and name != null">
            AND name LIKE "%"#{name}"%"
        </if>
        <if test="url != '' and url != null">
            AND url LIKE "%"#{url}"%"
        </if>
        <if test="valid != '' and valid != null">
            AND valid = #{valid}
        </if>
    </select>      

發現是參數變量引用有誤，修改mapper資訊，改為如下所示的引用方式即可。

<select id="findAllByConditions" resultType="com.example.TestCase">
        SELECT * FROM test_case WHERE delflag = 0
        <if test="code != '' and code != null">
            AND code LIKE '%${code}%'
        </if>
        <if test="name != '' and name != null">
            AND name LIKE '%${name}%'
        </if>
        <if test="url != '' and url != null">
            AND url LIKE '%${url}%'
        </if>
        <if test="valid != '' and valid != null">
            AND valid = #{valid}
        </if>
    </select>      

修改後的方式為'%${code}%'，或者使用concat函數拼接參數也可以，示例：CONCAT(CONCAT('%',#{code}), '%')

我本渺小，但山峰，我一次次絕頂！

PS：若有錯誤，敬請告知，不勝感激！

Copyright @範豐平 版權所有，如需轉載請标明本文原始連結出處，嚴禁商業用途！ 我的個人部落格連結位址:http://www.cnblogs.com/fengpingfan