iBATIS 动态insert插入语句+特殊字符过滤

最近工作中在处理iBATIS insert语句的时候由于特殊字符的原因，很多入库操作都出现了MySql异常。通过$（sql拼接）代替#（动态传参）的方式解决了特殊字符问题，像代码中fileName，aimParentname，oldFileName，groupPath等字段顺利的插入了单引号等特殊字符：

	<insert id="insert" parameterClass="GroupFileOperLog">
		insert
		into group_fileoper_log (FILE_OPER_LOG_ID, GROUP_ID, OPER_ID,
		USER_FILE_ID, FILE_NAME,
		IS_FOLDER, AIM_PARENTID, AIM_PARENTNAME, OPER_TYPE, OLD_FILE_NAME, GROUP_PATH,
		CREATE_TIME)
		values (#fileOperLogId:DECIMAL#, #groupId:DECIMAL#,
		#operId:DECIMAL#, #userFileId:DECIMAL#,
		"$fileName$", #isFolder:DECIMAL#, #aimParentid:DECIMAL#, "$aimParentname$",
		#operType:DECIMAL#, "$oldFileName$", "$groupPath$",
		#createTime:TIMESTAMP#)
	</insert>

但是使用$的字段却必须传参，否则拼出来的sql 再经过中间件（MyCat）解析之后无法执行。

[2017-07-28 16:42:15,095] [DEBUG] PreparedStatement - {pstm-100006} Executing Statement:    insert   into group_fileoper_log (FILE_OPER_LOG_ID, GROUP_ID, OPER_ID,   USER_FILE_ID, FILE_NAME,   IS_FOLDER, AIM_PARENTID, AIM_PARENTNAME, OPER_TYPE, OLD_FILE_NAME, GROUP_PATH,   CREATE_TIME)   values (?, ?,   ?, ?,   "", ?, ?, "迪斯尼动画\'故事150册(原版绘本+原声音频)",   ?, "asfeeee", "dddddddd",   ?)  
[2017-07-28 16:42:15,095] [DEBUG] PreparedStatement - {pstm-100006} Parameters: [11342113001, 113651134375, 332344446, 415501560146377, 1, 813651559952804, 7, 2017-07-28 16:42:14.755]
[2017-07-28 16:42:15,095] [DEBUG] PreparedStatement - {pstm-100006} Types: [java.lang.Long, java.lang.Long, java.lang.Long, java.lang.Long, java.lang.Long, java.lang.Long, java.lang.Long, java.sql.Timestamp]
[2017-07-28 16:42:15,107] [DEBUG] NewPooledConnection - com.mchange.v2.c3p0.impl.NewPooledConnection@4ecd200f handling a throwable.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: com.foundationdb.sql.parser.SQLParserException: Lexical error at line 1, column 266.  Encountered: "," (44), after : "\"\""
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)
	at $Proxy73.execute(Unknown Source)

所以不得不引入动态insert语句， 代码如下：

	
  <insert id="insertSelective" parameterClass="GroupFileOperLog">   
        INSERT INTO group_fileoper_log ( 
        FILE_OPER_LOG_ID   
        <isNotEmpty property="groupId">,GROUP_ID</isNotEmpty>  
        <isNotEmpty property="operId">,OPER_ID</isNotEmpty>  
        <isNotEmpty property="userFileId">,USER_FILE_ID</isNotEmpty> 
        <isNotEmpty property="fileName">,FILE_NAME</isNotEmpty>  
        <isNotEmpty property="isFolder">,IS_FOLDER</isNotEmpty>  
        <isNotEmpty property="aimParentid">,AIM_PARENTID</isNotEmpty>  
        
        <isNotEmpty property="aimParentname">,AIM_PARENTNAME</isNotEmpty>  
        <isNotEmpty property="operType">,OPER_TYPE</isNotEmpty> 
        <isNotEmpty property="oldFileName">,OLD_FILE_NAME</isNotEmpty>  
        <isNotEmpty property="groupPath">,GROUP_PATH</isNotEmpty>  
        <isNotEmpty property="createTime">,CREATE_TIME</isNotEmpty>  
        <![CDATA[ ) VALUES (  ]]>  
        #fileOperLogId:DECIMAL# 
        <isNotEmpty property="groupId">,#groupId:DECIMAL#</isNotEmpty>  
        <isNotEmpty property="operId">,#operId:DECIMAL#</isNotEmpty>  
        <isNotEmpty property="userFileId">,#userFileId:DECIMAL#</isNotEmpty> 
        <isNotEmpty property="fileName">,"$fileName$"</isNotEmpty>  
        <isNotEmpty property="isFolder">,#isFolder:DECIMAL#</isNotEmpty>  
        <isNotEmpty property="aimParentid">,#aimParentid:DECIMAL#</isNotEmpty>  
        
        <isNotEmpty property="aimParentname">,"$aimParentname$"</isNotEmpty>  
        <isNotEmpty property="operType">,#operType:DECIMAL#</isNotEmpty> 
        <isNotEmpty property="oldFileName">,"$oldFileName$"</isNotEmpty>  
        <isNotEmpty property="groupPath">,"$groupPath$"</isNotEmpty>  
        <isNotEmpty property="createTime">,#createTime:TIMESTAMP#</isNotEmpty>   
        <![CDATA[  )  ]]>  
  </insert>

打印的sql语句：

insert   into group_fileoper_log (FILE_OPER_LOG_ID, GROUP_ID, OPER_ID,   USER_FILE_ID, FILE_NAME,   IS_FOLDER, AIM_PARENTID, AIM_PARENTNAME, OPER_TYPE, OLD_FILE_NAME, GROUP_PATH,  
 CREATE_TIME)   values (?, ?,   ?, ?,   '411-You\'re the Greatest, Charlie Brown', ?, ?, '迪斯尼动画\'故事150册(原版绘本+原声音频)', 
  ?, 'e', '/国内外优秀育儿分享2/迪斯尼动\'画故事150册(原版绘本+原声音频)',   ?)