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

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

<insert id="insert" parameterClass="GroupFileOperLog">insertinto 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册(原版绘本+原声音频)',   ?)