在线文档：
动态SQL http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html
1. Error setting null for parameter #19 with JdbcType OTHER
问题：
org.springframework.jdbc.UncategorizedSQLException: Error setting null for parameter #19 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型
; uncategorized SQLException for SQL []; SQL state [null]; error code [17004]; 无效的列类型; nested exception is java.sql.SQLException: 无效的列类型
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)
at com.sun.proxy.$Proxy49.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:240)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:51)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
at com.sun.proxy.$Proxy94.add(Unknown Source)
at com.ihome.basicbiz.tforder.dao.IPurchaseOrderDaoTest.testAdd(IPurchaseOrderDaoTest.java:59)
...................
完整堆栈：
org.springframework.jdbc.UncategorizedSQLException: Error setting null for parameter #19 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型 ; uncategorized SQLException for SQL []; SQL state [null]; error code [17004]; 无效的列类型; nested exception is java.sql.SQLException: 无效的列类型 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371) at com.sun.proxy.$Proxy49.insert(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:240) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:51) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52) at com.sun.proxy.$Proxy94.add(Unknown Source) at com.ihome.basicbiz.tforder.dao.IPurchaseOrderDaoTest.testAdd(IPurchaseOrderDaoTest.java:59) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:73) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:224) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:68) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192) Caused by: java.sql.SQLException: 无效的列类型 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:147) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:209) at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3462) at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4337) at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4326) at org.apache.commons.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:108) at org.apache.commons.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:108) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:70) at com.sun.proxy.$Proxy103.setNull(Unknown Source) at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:43) at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:81) at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:80) at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:61) at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:74) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:47) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152) at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:141) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358) ... 35 more
异常出处：
org.apache.ibatis.type.BaseTypeHandler#setParameter()
public void setParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException { if (parameter == null) { if (jdbcType == null) { throw new TypeException("JDBC requires that the JdbcType must be specified for all nullable parameters."); } try { ps.setNull(i, jdbcType.TYPE_CODE); } catch (SQLException e) { throw new TypeException("Error setting null for parameter #" + i + " with JdbcType " + jdbcType + " . " + "Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. " + "Cause: " + e, e); } } else { setNonNullParameter(ps, i, parameter, jdbcType); } }
异常原因：
在做插入的时候，有一个参数（即parameter) 的值为null，而在sqlMap里面又没有给对应的字段设置 jdbcType。
MyBatis在处理这种没有指定jdbcType的字段时，会默认使用org.apache.ibatis.type.JdbcType#OTHER(Types.OTHER)来处理，这样的话ps.setNull(i, jdbcType.TYPE_CODE);就会报java.sql.SQLException: 无效的列类型
怎样定位是哪个字段的问题：
我们从异常中可以看到是第19个参数出了问题（Error setting null for parameter #19 with JdbcType OTHER），怎么去数是哪个字段呢？
1 insert into XXX_ORDER 2 (TRADE_NO, 3 UUID, 4 ACCOUNT_INFO_NO, 5 TRADE_ACCOUNT_NO, 6 OUT_TRADE_NO, 7 OUT_ORDER_NO, 8 FUND_ORDER_NO, 9 CUSTOMER_ID, 10 CUSTOMER_NAME, 11 BENEFIT_CODE, 12 BIZ_PRODUCT_CODE, 13 PROJECT_CODE, 14 TRUST_NO, 15 TRUST_NAME, 16 MERCHANT_NO, 17 AMOUNT, 18 CLEAR_DATE, 19 OPEN_DATE, 20 APPLY_TIME, 21 CREATE_TIME, 22 UPDATE_TIME, 23 IOU_NO) 24 values 25 (get_order_no('PCS', 'SEQ_PURCHASE_ORDER'), 26 #{uuid, jdbcType = VARCHAR}, 27 #{accountInfoNo, jdbcType = VARCHAR}, 28 #{tradeAccountNo, jdbcType = VARCHAR}, 29 #{outTradeNo, jdbcType = VARCHAR}, 30 #{outOrderNo, jdbcType = VARCHAR}, 31 #{fundOrderNo, jdbcType = VARCHAR}, 32 #{customerId, jdbcType = INTEGER}, 33 #{customerName, jdbcType = VARCHAR}, 34 #{benefitCode, jdbcType = VARCHAR}, 35 #{bizProductCode, jdbcType = VARCHAR}, 36 #{projectCode, jdbcType = VARCHAR}, 37 #{trustNo, jdbcType = VARCHAR}, 38 #{trustName, jdbcType = VARCHAR}, 39 #{merchantNo, jdbcType = VARCHAR}, 40 #{amount}, 41 #{clearDate, jdbcType = DATE}, 42 #{openDate, jdbcType = DATE}, 43 #{applyTime, jdbcType = TIMESTAMP}, 44 #{iouNo})
由于我们是用PreparedStatement去绑定参数的，需要把不需要绑定的字段剔除，剔除后的结果如下：
1 UUID, 2 ACCOUNT_INFO_NO, 3 TRADE_ACCOUNT_NO, 4 OUT_TRADE_NO, 5 OUT_ORDER_NO, 6 FUND_ORDER_NO, 7 CUSTOMER_ID, 8 CUSTOMER_NAME, 9 BENEFIT_CODE, 10 BIZ_PRODUCT_CODE, 11 PROJECT_CODE, 12 TRUST_NO, 13 TRUST_NAME, 14 MERCHANT_NO, 15 AMOUNT, 16 CLEAR_DATE, 17 OPEN_DATE, 18 APPLY_TIME, 19 IOU_NO) 20 values 21 (get_order_no('PCS', 'SEQ_PURCHASE_ORDER'), 22 #{uuid, jdbcType = VARCHAR}, 23 #{accountInfoNo, jdbcType = VARCHAR}, 24 #{tradeAccountNo, jdbcType = VARCHAR}, 25 #{outTradeNo, jdbcType = VARCHAR}, 26 #{outOrderNo, jdbcType = VARCHAR}, 27 #{fundOrderNo, jdbcType = VARCHAR}, 28 #{customerId, jdbcType = INTEGER}, 29 #{customerName, jdbcType = VARCHAR}, 30 #{benefitCode, jdbcType = VARCHAR}, 31 #{bizProductCode, jdbcType = VARCHAR}, 32 #{projectCode, jdbcType = VARCHAR}, 33 #{trustNo, jdbcType = VARCHAR}, 34 #{trustName, jdbcType = VARCHAR}, 35 #{merchantNo, jdbcType = VARCHAR}, 36 #{amount}, 37 #{clearDate, jdbcType = DATE}, 38 #{openDate, jdbcType = DATE}, 39 #{applyTime, jdbcType = TIMESTAMP}, 40 #{iouNo})
可以清楚的看到出问题的第19个参数是iouNo，问题找到！
解决办法：
将iouNo加上jdbcType就可以了，即 #{iouNo, jdbcType=VARCHAR}
2. MyBatis中在<if>标签中使用enum和String做equals比较
mybatis version:3.2.4
mybatis-spring version:1.2.2
<if>标签中使用Enum和String做equals比较，始终不成功，尝试了如下几种，都是失败告终：
1. <if test="enumItem == 'xxStr'">
2. <if test='enumItem == "xxStr"'>
3. <if test="enumItem == 'xxStr'.toString()">
mybatis version:3.4.4
mybatis-spring version:1.3.0
<if>标签中使用Enum和String做equals比较，使用如下方式，得到解决：
1. <if test="enumItem.toString() == 'xxStr'">
如果是简单的String与String做比较的话，使用<if test="strParam == 'xxStr'">即可
3. MyBatis中insert返回主键
MySQL：
<insert id="add" parameterType="xxx.Foo" useGeneratedKeys="true" keyProperty="id"> insert into user(id, name) values((#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}) </insert>
或者：
<insert id="add" parameterType="xxx.Foo" > <selectKey resultType="java.lang.Long" order="AFTER" keyProperty="id"> SELECT LAST_INSERT_ID() </selectKey> insert into user(id, name) values((#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}) </insert>
Oracle：
<insert id="add" parameterType="xxx.Foo"> <selectKey resultType="java.lang.Long" order="BEFORE" keyProperty="id"> SELECT SEQ_TEST.NEXTVAL FROM DUAL </selectKey> insert into user(id, name) values((#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}) </insert>
4. Mybatis批量插入返回主键
参考：http://blog.csdn.net/u014336799/article/details/52023887
实例：
Dao : int batchAdd(List<JobLog> list); Mapper： <insert id="batchAdd" parameterType="com.kvn.dal.core.pojo.JobLog" useGeneratedKeys="true" keyProperty="id"> insert into job_log (code, cron, description, start_time, job_status, execute_type, global_single, count, remark) values <foreach item="item" collection="list" separator=","> (#{item.code,jdbcType=VARCHAR}, #{item.cron,jdbcType=VARCHAR}, #{item.description,jdbcType=VARCHAR}, now(), 'PROCESSING', #{item.executeType,jdbcType=VARCHAR}, #{item.globalSingle,jdbcType=BOOLEAN}, 0, #{item.remark,jdbcType=VARCHAR}) </foreach> </insert> 测试：jobLogDao.batchAdd(list); System.out.println("=====>" + JSON.toJSONString(list)); 结果： =====>[{"code":"x","count":0,"cron":"q","description":"d","executeType":"s","globalSingle":false,"id":42},{"code":"x","count":0,"cron":"q","description":"d","executeType":"s","globalSingle":false,"id":43}]
注意：
1、升级Mybatis版本到3.3.1。
2、在Dao中不能使用@param注解。
3、Mapper.xml中使用list变量接受Dao中的集合。
5. Spring-MyBatis Mapper文件默认扫描路径
Spring-MyBatis自动扫描com.a.b.XXMapper.java接口时，还会自动扫描相应的Mapper文件。
扫描路径为：路径为com/a/b/XXMapper.xml
即：与XXMapper.java路径相同，名字相同的xml文件
这样，可以省去mapper.xml扫描的配置