这是我昨天在整合MySQL Fabric和MyBatis时遇到的问题，花了大半天才解决的问题，解决的过程中在网上查找了很久，都没有找到解决的方案。现在记下来，希望能够帮助有同样问题的朋友。如果各位朋友有更好的解决方案，也请告诉我。
1. 问题描述
这个问题是在整合MySQL和MyBatis的时候遇到的。
首先说一下我使用的jar包的版本，MySQL Connector用的是5.1.36，myBatis用的是3.2.8。我也试过将MySQL Connector升级到5.1.40，然并卵。
看看MyBatis中数据源的设置如下：
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${mybatis.driverClassName}" />
<property name="url" value="${mybatis.url}" />
<property name="username" value="${mybatis.username}" />
<property name="password" value="${mybatis.password}" />
<property name="minIdle" value="${mybatis.minIdle}" /> <!-- 队列中的最小等待数 -->
<property name="maxIdle" value="${mybatis.maxIdle}" /> <!-- 队列中的最大等待数 -->
<property name="maxWait" value="${mybatis.maxWait}" /> <!-- 最长等待时间，单位毫秒 -->
<property name="maxActive" value="${mybatis.maxActive}" /> <!-- 最大活跃数 -->
<property name="initialSize" value="${mybatis.initialSize}" /><!-- 初始大小 -->
<property name="validationQuery" value="${mybatis.validationQuery}" />
<property name="testWhileIdle" value="${mybatis.testWhileIdle}" />
<property name="timeBetweenEvictionRunsMillis" value="${mybatis.timeBetweenEvictionRunsMillis}" />
<property name="numTestsPerEvictionRun" value="${mybatis.numTestsPerEvictionRun}" />
<property name="minEvictableIdleTimeMillis" value="${mybatis.minEvictableIdleTimeMillis}" />
</bean>
对应的属性值定义如下：
mybatis.driverClassName=com.mysql.fabric.jdbc.FabricMySQLDriver
mybatis.url=jdbc:mysql:fabric://10.8.48.230:32274/basicservice?fabricServerGroup=spfood&fabricUsername=admin&fabricPassword=admin123#
mybatis.username=spfood
mybatis.password=spfood123#
mybatis.initialSize=5
mybatis.minIdle=1
mybatis.maxIdle=10
mybatis.maxWait=3000
mybatis.maxActive=50
#SQL查询,用来验证从连接池取出的连接
mybatis.validationQuery=SELECT 1
#指明连接是否被空闲连接回收器(如果有)进行检验，如果检测失败，则连接将被从池中去除
mybatis.testWhileIdle=true
#在空闲连接回收器线程运行期间休眠的时间值,以毫秒为单位，一般比minEvictableIdleTimeMillis小
mybatis.timeBetweenEvictionRunsMillis=300000
#在每次空闲连接回收器线程(如果有)运行时检查的连接数量，最好和maxActive一致
mybatis.numTestsPerEvictionRun=50
#连接池中连接，在时间段内一直空闲，被逐出连接池的时间(1000*60*60 = 1 hour)，以毫秒为单位
mybatis.minEvictableIdleTimeMillis=3600000
在查询的时候，一直遇到如下的错误信息：
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: java.lang.NullPointerException
### The error may involve com.spfood.basicservice.idgeneration.domain.IDGenIDInfo.updateByPrimaryKey-Inline
### The error occurred while setting parameters
### SQL: update IDGen_IDInfo set Next_Value = ?, Step_Length = ? where Id_Type = ?
### Cause: java.lang.NullPointerException
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:76)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:421)
at com.sun.proxy.$Proxy19.update(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:270)
at com.spfood.kernel.dao.impl.BaseDaoImpl.updateById(BaseDaoImpl.java:273)
... 43 more
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: java.lang.NullPointerException
### The error may involve com.spfood.basicservice.idgeneration.domain.IDGenIDInfo.updateByPrimaryKey-Inline
### The error occurred while setting parameters
### SQL: update IDGen_IDInfo set Next_Value = ?, Step_Length = ? where Id_Type = ?
### Cause: java.lang.NullPointerException
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:154)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:408)
... 46 more
Caused by: java.lang.NullPointerException
at com.mysql.jdbc.StatementImpl$CancelTask.<init>(StatementImpl.java:86)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1893)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1193)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.mysql.jdbc.MultiHostConnectionProxy$JdbcInterfaceProxy.invoke(MultiHostConnectionProxy.java:91)
at com.sun.proxy.$Proxy27.execute(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62)
at com.sun.proxy.$Proxy28.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69)
at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:50)
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)
... 51 more
2. 查找问题根源
可以看到，具体的错误来自PreparedStatement.java:1193 和 StatementImpl.java:86， 查找到这两个地方的代码如下：
StatementImpl.java:84-86行
Properties props = StatementImpl.this.connection.getProperties();
Enumeration<?> keys = props.propertyNames();
PreparedStatement.java:1192-1895
if (locallyScopedConnection.getEnableQueryTimeouts() && this.timeoutInMillis != 0 && locallyScopedConnection.versionMeetsMinimum(5, 0, 0)) {
timeoutTask = new CancelTask(this);
locallyScopedConnection.getCancelTimer().schedule(timeoutTask, this.timeoutInMillis);
}
Debug一下，可以看到，在StatementImpl.java中的第84行，getProperties()返回的值为null，跟踪进去，可以看到connection的具体类是FabricMySQLConnectionProxy，该类的getProperties方法实现如下：
public Properties getProperties() {
return null;
}
这个错误就是在创建CancelTask的时候失败。关于CancelTask，网上的资料大把，请自行搜索。
3. 解决方法
那么，有两种方式解决这个问题。
3.1 修改myBatis配置
我们可以看到在 PreparedStatement.java:1192这一行中的条件，如果this.timeoutInMillis为0，那么就不会创建CancelTask。我们只需要在MyBatis的配置中将这个值设置为0就可以了。
<configuration>
<settings>
<setting name="cacheEnabled" value="true" />
<setting name="lazyLoadingEnabled" value="false" />
<setting name="multipleResultSetsEnabled" value="true" />
<setting name="useColumnLabel" value="true" />
<setting name="defaultExecutorType" value="REUSE" />
<setting name="defaultStatementTimeout" value="0" />
<setting name="logImpl" value="LOG4J"/>
</settings>
</configuration>
就是上面的配置中的defaultStatementTimeout这一项。
3.2 扩展MySQL Connector的代码
如果还想继续使用CancelTask的功能，那就只有自行扩展MySQL Connector的代码了。
首先想到的是修改FabricMySQLConnectionProxy。我们编写一个自己的ConnectionProxy，继承FabricMySQLConnectionProxy，覆盖它的getProperties和getCancelTimer方法就可以了，如下：
public class AfragFabricMySQLConnectionProxy extends FabricMySQLConnectionProxy
implements FabricMySQLConnection, FabricMySQLConnectionProperties{
/**
*
*/
private static final long serialVersionUID = 8626818655234189033L;
private transient Timer cancelTimer;
public AfragFabricMySQLConnectionProxy(Properties props) throws SQLException {
super(props);
}
public Timer getCancelTimer() {
synchronized (getConnectionMutex()) {
if (this.cancelTimer == null) {
boolean createdNamedTimer = false;
// Use reflection magic to try this on JDK's 1.5 and newer, fallback to non-named timer on older VMs.
try {
Constructor<Timer> ctr = Timer.class.getConstructor(new Class[] { String.class, Boolean.TYPE });
this.cancelTimer = ctr.newInstance(new Object[] { "MySQL Statement Cancellation Timer", Boolean.TRUE });
createdNamedTimer = true;
} catch (Throwable t) {
createdNamedTimer = false;
}
if (!createdNamedTimer) {
this.cancelTimer = new Timer(true);
}
}
return this.cancelTimer;
}
}
public Properties getProperties() {
return new Properties();
}
}
那么，怎么让driver知道要使用我们的ConnectionProxy呢？那就需要修改FabricMySQLDriver类了。同样的，我们创建自己的Driver类，继承FabricMySQLDriver类。如下,主要修改的地方有两个：
- 修改connection方法，使其返回我们的AfragFabricMySQLConnectionProxy，而不是原来的FabricMySQLConnectionProxy。
- 在类的静态块中，首先Deregister FabricMySQLDriver，同时注册自己。不然DriverManager在解析url的时候，还是会使用老的FabricMySQLDriver类。
这样修改后，应该没有问题了吧？但是有时候还是会报错，在跟踪一下，可以看到在connection方法中，有时候会返回JDBC4FabricMySQLConnectionProxy，该类继承了FabricMySQLConnectionProxy。因此，我们需要创建自己的JDBC4 ConnectionProxy，实现getCancelTimer和getProperties方法。
实现后的Driver类和JDBC4 ConnectionProxy类如下：
Driver类：
public class AfragFabricMySQLDriver extends FabricMySQLDriver{
// Deregister FabricMySQLDriver and Register ourselves with the DriverManager
static {
try {
deregisterFabricMySQLDriver();
DriverManager.registerDriver(new AfragFabricMySQLDriver());
} catch (SQLException ex) {
throw new RuntimeException("Can't register driver", ex);
}
}
public static void deregisterFabricMySQLDriver() throws SQLException{
Enumeration<Driver> drivers = DriverManager.getDrivers();
while (drivers.hasMoreElements()){
Driver driver = drivers.nextElement();
if (driver instanceof FabricMySQLDriver){
DriverManager.deregisterDriver(driver);
}
}
}
/**
* @throws SQLException
*/
public AfragFabricMySQLDriver() throws SQLException {
super();
}
public Connection connect(String url, Properties info) throws SQLException {
Properties parsedProps = parseFabricURL(url, info);
if (parsedProps == null) {
return null;
}
parsedProps.setProperty(FABRIC_PROTOCOL_PROPERTY_KEY, "http");
if (com.mysql.jdbc.Util.isJdbc4()) {
try {
Constructor<?> jdbc4proxy = Class.forName("personal.afrag.AfragJDBC4FabricMySQLConnectionProxy").getConstructor(
new Class[] { Properties.class });
return (Connection) com.mysql.jdbc.Util.handleNewInstance(jdbc4proxy, new Object[] { parsedProps }, null);
} catch (Exception e) {
throw (SQLException) new SQLException(e.getMessage()).initCause(e);
}
}
return new AfragFabricMySQLConnectionProxy(parsedProps);
}
Properties parseFabricURL(String url, Properties defaults) throws SQLException {
if (!url.startsWith("jdbc:mysql:fabric://")) {
return null;
}
// We have to fudge the URL here to get NonRegisteringDriver.parseURL() to parse it for us.
// It actually checks the prefix and bails if it's not recognized.
// jdbc:mysql:fabric:// => jdbc:mysql://
return super.parseURL(url.replaceAll("fabric:", ""), defaults);
}
}
JDBC4 Connection Proxy:
public class AfragJDBC4FabricMySQLConnectionProxy extends JDBC4FabricMySQLConnectionProxy implements JDBC4FabricMySQLConnection, FabricMySQLConnectionProperties {
/**
*
*/
private static final long serialVersionUID = 6404998348296596764L;
/**
* @param props
* @throws SQLException
*/
public AfragJDBC4FabricMySQLConnectionProxy (Properties props) throws SQLException {
super(props);
}
private transient Timer cancelTimer;
public Timer getCancelTimer() {
synchronized (getConnectionMutex()) {
if (this.cancelTimer == null) {
boolean createdNamedTimer = false;
// Use reflection magic to try this on JDK's 1.5 and newer, fallback to non-named timer on older VMs.
try {
Constructor<Timer> ctr = Timer.class.getConstructor(new Class[] { String.class, Boolean.TYPE });
this.cancelTimer = ctr.newInstance(new Object[] { "MySQL Statement Cancellation Timer", Boolean.TRUE });
createdNamedTimer = true;
} catch (Throwable t) {
createdNamedTimer = false;
}
if (!createdNamedTimer) {
this.cancelTimer = new Timer(true);
}
}
return this.cancelTimer;
}
}
}