Record a bug Solution Process: Use of $ and # In mybatis,

Record a bug Solution Process: Use of $ and # In mybatis,

I. Summary

When using sqlMap in mybatis for SQL queries, you often need to dynamically pass parameters. Dynamic SQL is one of the powerful features of mybatis and an important reason for its superiority over other ORM frameworks. Before pre-compiling SQL statements, mybatis dynamically parses the SQL statements and parses them into a BoundSql object. It also processes dynamic SQL statements. In the dynamic SQL parsing phase, # {}and $ {} may have different performances. # {} is resolved as a parameter tag of A JDBC precompiled statement (prepared statement.

# {} Is parsed as a parameter placeholder?. $ {} Is only replaced by a broken string. variables are replaced in the dynamic SQL parsing phase.

Ii. Bug description

Frontend input parameters:

skip:0take:10ruleName:A,B,C

Business Layer processing:

Package SQL;/*** escape Multiple front-end selection parameters into SQL statement content */public class SQLUtil {private final static String REPLACECHAR_COMMA = ","; private final static String REPLACECHAR_SEMICOLON = "; "; public static void main (String [] args) {String s1 =" A, B, C "; String s2 =" a B C "; System. out. println ("separated by commas:" + formatInStr (s1); System. out. println ("separated by spaces:" + formatInStr (s2);} private static String formatInStr (String queryStr) {Return queryInStr (sliptQueryStr (queryStr);} private static String [] sliptQueryStr (String queryStr) {if (null = queryStr | "". equals (queryStr. trim () return null; queryStr = queryStr. replaceAll (SQLUtil. REPLACECHAR_COMMA ,""). replaceAll (REPLACECHAR_SEMICOLON, ""); return queryStr. split ("\ s +");} private static String queryInStr (String [] queryStrs) {if (null = queryStrs | 0 = queryStrs. Length) return null; StringBuffer buf = new StringBuffer (); for (int I = 0; I <queryStrs. length; I ++) {if (I! = 0) buf. append (","); buf. append ("'"). append (queryStrs [I]). append ("'");} return buf. toString ();}}

Mapper layer processing:

// Handle errors <if test = "ruleName! = Null and ruleName! = ''"> AND a. rule_name IN (# {ruleName}) </if> // correct processing <if test = "ruleName! = Null and ruleName! = ''"> AND a. rule_name IN ($ {ruleName}) </if>

Log description:

[DEBUG] [2016-08-02 17:42:42.226] [qtp1457334982-157] java.sql.Connection - ==>  Preparing: SELECT a.id, a.is_valid, a.rule_lable, a.rule_name, a.type, b.sp_id, b.sp_name, 
　　　　　　a.rule_content, c.user_name, a.gmt_modified, a.ordering FROM idc_logistics_assign_rules a LEFT JOIN app_user c on c.work_no=a.modifier and c.is_deleted='n', 
　　　　　　idc_sp_info b WHERE a.is_deleted = 'n' AND b.is_deleted = 'n' AND a.sp_id = b.sp_id AND a.rule_name IN (?) ORDER BY ordering asc limit ?, ? [DEBUG] [2016-08-02 17:42:42.226] [qtp1457334982-157] java.sql.PreparedStatement - ==> Parameters: 'A','B'(String), 0(Integer), 10(Integer)

Result Analysis: The mapper layer pre-compiles SQL statements, and # has a placeholder ?, However, $ is directly replaced.

Refer:Http://www.tuicool.com/articles/VvyMN3A