一·異常資訊：
===2018-01-24 10:36:10.256 ERROR org.springframework.aop.interceptor.SimpleAsyncUncaughtExceptionHandler Line:37 - Unexpected error occurred invoking async method 'public void com..service.SmsCallBackListenner.callback(com..pojo.SendInterfaceListEvent)'.
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO SEND (..) VALUES(?,?,?,?,?,?,?,?,?,?,?);]; SQL state [null]; error code [0]; Error; nested exception is java.sql.SQLException: Error
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:90)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:655)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:668)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:956)
at com..service.SmsCallBackListenner.insertSms(SmsCallBackListenner.java:44)
at com..service.SmsCallBackListenner.callback(SmsCallBackListenner.java:40)
at com..service.SmsCallBackListenner$$FastClassBySpringCGLIB$$7d92c1dc.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(AsyncExecutionInterceptor.java:115)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Error
at com.alibaba.druid.pool.DruidDataSource.handleConnectionException(DruidDataSource.java:1392)
at com.alibaba.druid.pool.DruidPooledConnection.handleException(DruidPooledConnection.java:136)
at com.alibaba.druid.pool.DruidPooledStatement.checkException(DruidPooledStatement.java:71)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.setClob(DruidPooledPreparedStatement.java:607)
at com..dao.InsertSmsSendBatchPreparedStatementSetter.setValues(InsertSmsSendBatchPreparedStatementSetter.java:29)
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:966)
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:956)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:639)
... 13 common frames omitted
Caused by: java.lang.ClassCastException: com.alibaba.druid.proxy.jdbc.ClobProxyImpl cannot be cast to oracle.sql.CLOB
at oracle.jdbc.driver.OraclePreparedStatement.setClob(OraclePreparedStatement.java:6805)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setClob(OraclePreparedStatementWrapper.java:162)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setClob(FilterChainImpl.java:3054)
at com.alibaba.druid.filter.FilterAdapter.preparedStatement_setClob(FilterAdapter.java:1195)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setClob(FilterChainImpl.java:3051)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.setClob(PreparedStatementProxyImpl.java:297)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.setClob(DruidPooledPreparedStatement.java:605)
... 17 common frames omitted
二·背景描述
oracle 資料庫，查詢帶有clob類型字段的A表，并存儲到 實體類，實體類對應的字段 類型也為 oracle.sql.CLOB。 經過業務處理後 把該字段的值插入到 到 B表 的也為clob類型字段時 報出 如上異常。
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency> <dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6g</artifactId>
<version>11.2.0.4.0</version>
</dependency> <dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency> 三·異常分析
主要是 使用的druid 連接配接池導緻的，JdbcTemplate本身沒有直接關系。 我們看到錯誤日志 ，有一個類型轉換異常。
Caused by: java.lang.ClassCastException: com.alibaba.druid.proxy.jdbc.ClobProxyImpl cannot be cast to oracle.sql.CLOB
oracle 資料庫驅動 clob 使用的是oracle.sql.CLOB 而 Druid 我們 從 ClobProxyImpl 和 DruidPooledPreparedStatement 可以看到 使用的是 java.sql.Clob 而 這并沒有關系 ，會在 驅動包裡的 OraclePreparedStatement 裡把clob 強轉成 CLOB
繼續看源碼 ：在FilterChainImpl 裡的connection_createClob 方法：
@Override
public Clob connection_createClob(ConnectionProxy connection) throws SQLException {
if (this.pos < filterSize) {
return nextFilter().connection_createClob(this, connection);
}
Clob clob = connection.getRawObject().createClob();
return wrap(connection, clob);
}
最後 return的 是 FilterChainImpl 下的 wrap：
public ClobProxy wrap(ConnectionProxy conn, Clob clob) {
if (clob == null) {
return null;
}
if (clob instanceof NClob) {
return wrap(conn, (NClob) clob);
}
return new ClobProxyImpl(dataSource, conn, clob);
}
導緻 類型轉換異常的原因就是這樣。 至于為什麼這麼做，并不清楚，可能我們使用的姿勢并不對，druid 可能并不允許我們這麼做。
四·解決方案
從資料庫讀取的clob字段的時候 ，實體類 相應字段 使用String 類型存儲，讀取的時候 也 通過getSting 去 擷取clob的值：
@Override
public Object mapRow(ResultSet resultSet, int i) throws SQLException {
SendInterface sendInterface = new SendInterface();
sendInterface.setSendId(resultSet.getString("SEND_ID"));
sendInterface.setMobileTo(resultSet.getString("MOBILE_TO"));
sendInterface.setSendMsg(resultSet.getString("SEND_MSG"));
sendInterface.setExtendNumber(resultSet.getString("EXTEND_NUMBER"));
sendInterface.setSysTime(resultSet.getDate("SYS_TIME"));
sendInterface.setPreSendTime(resultSet.getDate("PRE_SEND_TIME"));
return sendInterface;
}
存儲 字元串 在 表 clob字段的時候 也不需要你把 String 轉為 clob 再存 ，因為Druid 都 替你做了，
DruidLobCreator：
@Override
public void setClobAsAsciiStream(PreparedStatement ps, int paramIndex, InputStream asciiStream, int contentLength)
throws SQLException {
if (asciiStream != null) {
Clob clob = ps.getConnection().createClob();
OutputStream out = clob.setAsciiStream(1);
final int BUFFER_SIZE = 4096;
try {
byte[] buffer = new byte[BUFFER_SIZE];
int bytesRead = -1;
while ((bytesRead = asciiStream.read(buffer)) != -1) {
out.write(buffer, 0, bytesRead);
}
out.flush();
} catch (Exception e) {
throw new SQLException("setClob error", e);
} finally {
JdbcUtils.close(asciiStream);
JdbcUtils.close(out);
}
ps.setClob(paramIndex, clob);
} else {
ps.setClob(paramIndex, (Clob) null);
}
}
@Override
public void setClobAsString(PreparedStatement ps, int paramIndex, String content) throws SQLException {
Clob clob = ps.getConnection().createClob();
clob.setString(1, content);
ps.setClob(paramIndex, clob);
}
![Spring+jdbcTemplate簡單的CRUD[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)