读书人
栏目▼

hibernate clob保留

发布时间： 2012-10-27 10:42:26 作者： rapoo

hibernate clob保存

? 费了比较多的精力终于解决了这个疑难问题，在百度上查阅了大量博客，论坛，一直没有放弃。通过自己的反复试验，像福尔摩斯抽丝剥茧一样终于找到问题的原因，确实很有必要记述下来，下面将解决该问题的来龙去脉细细道来。

? ? ? ? ? ? 我们的网管平台的作业计划采集总是在运行了一段时间之后出现了java.sql.SQLException: 关闭的连接问题。异常堆栈如下:

?

[java] view plaincopyprint?
  1. java.sql.SQLException:?关闭的连接??????at?oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)??
  2. ????at?oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)??????at?oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)??
  3. ????at?oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)??????at?oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:439)??
  4. ????at?oracle.sql.CLOB.getDBAccess(CLOB.java:1083)??????at?oracle.sql.CLOB.getAsciiStream(CLOB.java:228)??
  5. ????at?org.hibernate.lob.SerializableClob.getAsciiStream(SerializableClob.java:45)??????at?com.wri.hy.itmanagerv2.dg.dao.TaskParamDao.queryAllTaskParam(TaskParamDao.java:99)??
  6. ????at?com.wri.hy.itmanagerv2.dg.autotask.dataservce.TaskParamService.queryAllTaskParam(TaskParamService.java:33)??????at?com.wri.hy.itmanagerv2.dg.autotask.sched.SchedController.init(SchedController.java:96)??
  7. ????at?com.wri.hy.itmanagerv2.dg.autotask.sched.SchedReadDB.run(SchedReadDB.java:59)??????at?java.util.concurrent.Executors$RunnableAdapter.call(Unknown?Source)??
  8. ????at?java.util.concurrent.FutureTask$Sync.innerRunAndReset(Unknown?Source)??????at?java.util.concurrent.FutureTask.runAndReset(Unknown?Source)??
  9. ????at?java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(Unknown?Source)??????at?java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(Unknown?Source)??
  10. ????at?java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown?Source)??????at?java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown?Source)??
  11. ????at?java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown?Source)??????at?java.lang.Thread.run(Unknown?Source)??

?

? ? ? ? ? ? ? 查网上说“关闭的连接”是因为连接池中已经没有打开的Connection了，这个连接池是以前的同事仿造bonecp写的，应该不会有啥问题啊，我在连接池代码里加了很多打印日志，还是没看出有什么问题来，过了很久我就把思路放在分析其它做数据库保存或者修改操作的代码，看是否在保存过程中的问题引发了连接被关闭。发现在做采集数据保存时候出现了如下异常。

?

[java] view plaincopyprint?
  1. org.springframework.dao.DataAccessResourceFailureException:?Hibernate?operation:?Could?not?execute?JDBC?batch?update;?SQL?[insert?into?ITMANAGERV2_PLANRESULTINFO?(TASK_ID,?RESULT,?getTime,?EXCEPTION_FLAG,?EXCEPTION_LINE,?ID)?values?(?,??,??,??,??,??)];?Io?异常:?Software?caused?connection?abort:?socket?write?error;?nested?exception?is?java.sql.BatchUpdateException:?Io?异常:?Software?caused?connection?abort:?socket?write?error???Caused?by:?java.sql.BatchUpdateException:?Io?异常:?Software?caused?connection?abort:?socket?write?error??
  2. ????at?oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:602)??????at?oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9350)??
  3. ????at?oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:210)??????at?org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)??
  4. ????at?org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)??????at?org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:91)??
  5. ????at?org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:86)??????at?org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:171)??
  6. ????at?org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2048)??????at?org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2427)??
  7. ????at?org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:51)??????at?org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)??
  8. ????at?org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)??????at?org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)??
  9. ????at?org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)??????at?org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)??
  10. ????at?org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)??????at?org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:394)??
  11. ????at?org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:367)??????at?org.springframework.orm.hibernate3.HibernateTemplate.saveOrUpdateAll(HibernateTemplate.java:688)??
  12. ????at?com.wri.hy.itmanagerv2.dg.dao.ResultLogDao.saveCollections(ResultLogDao.java:13)??????at?com.wri.hy.itmanagerv2.dg.autotask.sched.ResultLogThread.run(ResultLogThread.java:157)??
  13. ????at?java.lang.Thread.run(Unknown?Source)??????at?java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown?Source)??
  14. ????at?java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown?Source)??????at?java.lang.Thread.run(Unknown?Source)??

? ? ? ? ? ? ? oh my god，一句简单的hibernateTemplate的saveOrUpdateAll居然会引起这个异常，在百度，谷歌上根据这个异常找来找去也找不到一个说得有用的。只好老老实实开始分析存储代码，存储的POJO是这样定义的。

?

[java] view plaincopyprint?
  1. public?class?PlanResultInfo?{?????
  2. ????private?Long?id;????????????//id????????
  3. ????private?PlanTaskInfo?taskInfo;??????//所属作业计划任务????????
  4. ????private?Clob?result;????????????//执行结果????????
  5. ????private?String?getTime;?????????//获取时间????????
  6. ????private?String?exception_flag;??????//异常标识????????
  7. ????private?String?exception_line;??????//异常行号????????
  8. ????/**??????*?存储执行的结果??
  9. ?????*/??????private?transient?String?strResult;???
  10. ???????public?String?getException_flag()?{??
  11. ????????return?exception_flag;??????}??
  12. ???????public?void?setException_flag(String?exception_flag)?{??
  13. ????????this.exception_flag?=?exception_flag;??????}??
  14. ???????public?String?getException_line()?{??
  15. ????????return?exception_line;??????}??
  16. ???????public?void?setException_line(String?exception_line)?{??
  17. ????????this.exception_line?=?exception_line;??????}??
  18. ???????public?String?getGetTime()?{??
  19. ????????return?getTime;??????}??
  20. ???????public?void?setGetTime(String?getTime)?{??
  21. ????????this.getTime?=?getTime;??????}??
  22. ???????public?Long?getId()?{??
  23. ????????return?id;??????}??
  24. ???????public?void?setId(Long?id)?{??
  25. ????????this.id?=?id;??????}??
  26. ???????public?PlanTaskInfo?getTaskInfo()?{??
  27. ????????return?taskInfo;??????}??
  28. ???????public?void?setTaskInfo(PlanTaskInfo?taskInfo)?{??
  29. ????????this.taskInfo?=?taskInfo;??????}??
  30. ???????public?Clob?getResult()?{??
  31. ????????return?result;??????}??
  32. ???????public?void?setResult(Clob?result)?{??
  33. ????????this.result?=?result;??????}??
  34. ???????public?String?getStrResult()?{??
  35. ????????return?strResult;??????}??
  36. ???????public?void?setStrResult(String?strResult)?{??
  37. ????????this.strResult?=?strResult;??????}??
  38. ???????}??

? ?其中result是个clob类型的字段，在hibernate的配置文件中result字段是这样定义的。

[java] view plaincopyprint?
  1. <property?name="result"?type="java.sql.Clob"?update="true"?insert="true">??????????????<column?name="RESULT"></column>??
  2. ????????</property>??


? ? ? ? ? ? 这样写感觉没问题啊，怎么会Software caused connection abort: socket write error，后来发现保存进clob的结果有时候String长度非常大，length都有几十万，算起来有100多K，这种情况下就会出现异常，但中小量的数据是能够正常插入的。但是clob不是号称能保存4G的数据吗。我决定死马当活马医了，参照网上别的clob保存方式来重构代码。下面是第一版重构代码：

[java] view plaincopyprint?
  1. Session?session?=?null;??????????Transaction?tx?=?null;??
  2. ????????java.io.Writer?writer?=?null;??????????Reader?reader?=?null;??
  3. ????????try?{??????????????session?=?this.getSessionFactory().openSession();??
  4. ????????????planResultInfo.setResult(Hibernate.createClob("?"));??????????????tx?=?session.beginTransaction();??
  5. ????????????//?保存维护作业计划结果??????????????session.save(planResultInfo);??
  6. ????????????session.flush();??????????????//?刷新??
  7. ????????????session.refresh(planResultInfo,?LockMode.UPGRADE);??????????????????????????tx.commit();??
  8. ???????????????LOGGER.infoT("END?TASK?"?+?planResultInfo.getTaskInfo().getId());}catch?(Exception?e)?{??
  9. ????????????LOGGER.exception(e);??????????????//?回滚事务??
  10. ????????????session.getTransaction().rollback();??????????}?finally?{??
  11. ??????????????????????????if?(session?!=?null)?{??
  12. ????????????????if?(session.isOpen())?{??????????????????????//?关闭session??
  13. ????????????????????session.close();??????????????????}??
  14. ????????????}??????????}??


? ? ? ? 很可惜，保存大的clob又出现了新鲜异常：

?

[java] view plaincopyprint?
  1. org.hibernate.exception.GenericJDBCException:?Could?not?execute?JDBC?batch?update??????at?org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)??
  2. ????at?org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)??????at?org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)??
  3. ????at?org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)??????at?org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)??
  4. ????at?org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)??????at?org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)??
  5. ????at?org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)??????at?org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)??
  6. ????at?com.wri.hy.itmanagerv2.dg.dao.ResultLogDao.saveCollection(ResultLogDao.java:50)??????at?com.wri.hy.itmanagerv2.dg.autotask.sched.ResultLogThread.run(ResultLogThread.java:159)??
  7. ????at?java.lang.Thread.run(Unknown?Source)??????at?java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown?Source)??
  8. ????at?java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown?Source)??????at?java.lang.Thread.run(Unknown?Source)??
  9. ?Caused?by:?java.sql.BatchUpdateException:?无法从套接字读取更多的数据??????at?oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:345)??
  10. ????at?oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10844)??????at?org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)??
  11. ????at?org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)??????...?11?more??

? ? ? ? ? ? ? ? ? ?在网上查有关这个异常的问题也无所获，后来又是换ojdbc14.jar，也同样没作用。然后在网上看到有人在博客中写了一种写流的办法存clob，就出了重构第二版：

?

[java] view plaincopyprint?
  1. ?????????????Session?session?=?null;??Transaction?tx?=?null;??
  2. java.io.Writer?writer?=?null;????
  3. try?{??????session?=?this.getSessionFactory().openSession();??
  4. ????planResultInfo.setResult(Hibernate.createClob("?"));??????tx?=?session.beginTransaction();??
  5. ????//?保存维护作业计划结果??????session.save(planResultInfo);??
  6. ????session.flush();??????//?刷新??
  7. ????session.refresh(planResultInfo,?LockMode.UPGRADE);????
  8. ????org.hibernate.lob.SerializableClob?cb?=?(org.hibernate.lob.SerializableClob)?planResultInfo??????????????.getResult();??
  9. ????java.sql.Clob?wrapClob?=?(java.sql.Clob)?cb.getWrappedClob();????
  10. ????if?(wrapClob?instanceof?oracle.sql.CLOB)?{??????????oracle.sql.CLOB?clob?=?(oracle.sql.CLOB)?wrapClob;??
  11. ????????writer?=?new?BufferedWriter(clob.getCharacterOutputStream());????
  12. ????????String?contentStr?=?planResultInfo.getStrResult();??????????LOGGER.infoT("result?size?:?"?+?contentStr.length());??
  13. ????????writer.write(contentStr);???????????????????????????????writer.flush();??
  14. ????}????
  15. ????//?保存维护作业计划日志??????session.save(planLogInfo);??
  16. ??????tx.commit();??
  17. ??????LOGGER.infoT("END?TASK?"?+?planResultInfo.getTaskInfo().getId());??
  18. }?catch?(Exception?e)?{??????LOGGER.exception(e);??
  19. ????//?回滚事务??????session.getTransaction().rollback();??
  20. }?finally?{??????try?{??
  21. ??????????????????if?(null?!=?writer)??
  22. ????????????writer.close();??????}?catch?(IOException?ioe)?{??
  23. ????????LOGGER.exception(ioe);??????}??
  24. ????if?(session?!=?null)?{??????????if?(session.isOpen())?{??
  25. ????????????//?关闭session??????????????session.close();??
  26. ????????}??????}??
  27. }??

?

? ? ? ? ? 这个可是人家博客上写的言之凿凿的啊，可是保存还是出现异常(这个和起初的保存出的异常一模一样)。

[java] view plaincopyprint?
  1. java.io.IOException:?Io?异常:?Software?caused?connection?abort:?socket?write?error??????at?oracle.jdbc.driver.DatabaseError.SQLToIOException(DatabaseError.java:519)??
  2. ????at?oracle.jdbc.driver.OracleClobWriter.write(OracleClobWriter.java:122)??????at?java.io.Writer.write(Unknown?Source)??
  3. ????at?java.io.Writer.write(Unknown?Source)??????at?com.wri.hy.itmanagerv2.dg.dao.ResultLogDao.saveCollection(ResultLogDao.java:64)??
  4. ????at?com.wri.hy.itmanagerv2.dg.autotask.sched.ResultLogThread.run(ResultLogThread.java:159)??????at?java.lang.Thread.run(Unknown?Source)??
  5. ????at?java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown?Source)??????at?java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown?Source)??
  6. ????at?java.lang.Thread.run(Unknown?Source)??


? ? ? ? ? ? ?这里很明显看到是java.io.Writer.write出现的异常，可能以前的博客作者没有保存大数据量的clob数据，这样会造成连接中断，最后一次重构终于成功，代码如下:

[java] view plaincopyprint?
  1. Session?session?=?null;??????????Transaction?tx?=?null;??
  2. ????????java.io.Writer?writer?=?null;??????????Reader?reader?=?null;??
  3. ????????try?{??????????????session?=?this.getSessionFactory().openSession();??
  4. ????????????planResultInfo.setResult(Hibernate.createClob("?"));??????????????tx?=?session.beginTransaction();??
  5. ????????????//?保存维护作业计划结果??????????????session.save(planResultInfo);??
  6. ????????????session.flush();??????????????//?刷新??
  7. ????????????session.refresh(planResultInfo,?LockMode.UPGRADE);?????
  8. ????????????org.hibernate.lob.SerializableClob?cb?=?(org.hibernate.lob.SerializableClob)?planResultInfo??????????????????????.getResult();??
  9. ????????????java.sql.Clob?wrapClob?=?(java.sql.Clob)?cb.getWrappedClob();?????
  10. ????????????if?(wrapClob?instanceof?oracle.sql.CLOB)?{??????????????????oracle.sql.CLOB?clob?=?(oracle.sql.CLOB)?wrapClob;??
  11. ????????????????writer?=?new?BufferedWriter(clob.getCharacterOutputStream());?????
  12. ????????????????String?contentStr?=?planResultInfo.getStrResult();??????????????????LOGGER.infoT("result?size?:?"?+?contentStr.length());??
  13. ????????????????//?//?截取前200000个字符，不然会出现异常??????????????????//?if?(contentStr.length()?>?200000)?{??
  14. ????????????????//?contentStr?=?contentStr.substring(0,?200000);??????????????????//?}??
  15. ???????????????????reader?=?new?BufferedReader(new?StringReader(contentStr));??
  16. ????????????????char[]?buffer?=?new?char[1024];?????
  17. ????????????????int?length;??????????????????while?((length?=?reader.read(buffer))?>?0)?{??
  18. ????????????????????writer.write(buffer,?0,?length);??????????????????????writer.write(contentStr);??
  19. ????????????????????writer.flush();??????????????????}??
  20. ???????????????????//?writer.write(contentStr);??
  21. ???????????????????//?OutputStream?outputStream?=?clob.getAsciiOutputStream();??
  22. ????????????????//??????????????????//?InputStream?inputStream?=?new?BufferedInputStream(??
  23. ????????????????//?new?ByteArrayInputStream(contentStr.getBytes()));??????????????????//?byte[]?buff?=?new?byte[10240];??
  24. ????????????????//??????????????????//?int?len;??
  25. ????????????????//??????????????????//?while?((len?=?inputStream.read(buff))?>?0)?{??
  26. ????????????????//?outputStream.write(buff,?0,?len);??????????????????//?outputStream.flush();??
  27. ????????????????//?}??????????????????//?inputStream.close();??
  28. ????????????????//?outputStream.close();?????
  29. ????????????}?????
  30. ????????????//?保存维护作业计划日志??????????????session.save(planLogInfo);??
  31. ???????????????tx.commit();??
  32. ???????????????LOGGER.infoT("END?TASK?"?+?planResultInfo.getTaskInfo().getId());??
  33. ????????}?catch?(Exception?e)?{??????????????LOGGER.exception(e);??
  34. ????????????//?回滚事务??????????????session.getTransaction().rollback();??
  35. ????????}?finally?{??????????????try?{??
  36. ????????????????if?(null?!=?reader)??????????????????????reader.close();??
  37. ????????????????if?(null?!=?writer)??????????????????????writer.close();??
  38. ????????????}?catch?(IOException?ioe)?{??????????????????LOGGER.exception(ioe);??
  39. ????????????}??????????????if?(session?!=?null)?{??
  40. ????????????????if?(session.isOpen())?{??????????????????????//?关闭session??
  41. ????????????????????session.close();??????????????????}??
  42. ????????????}??????????}??

?

? ? ? ? ? ?这里要重点说明，对于大数据量的clob写入，必须用缓冲流循环写入字符数组，虽然执行时间长的，但可以执行成功，不会出现异常，

? ? ? ? ? ?总结：对于hibernate clob保存，如果clob中的数据量较小，普通saveorUpdate即可保存成功，但对于大数据量会导致连接断开，从而导致耗尽连接池中的连接，改用流来写入，也同样不能直接写入全部String，也会引起connection abort，此时需要按缓冲流读写，降低连接过程中的io效率，这样就能保证插入大数据量的clob信息。

? ? ? ? ? ? 这就是从解决java.sql.SQLException: 关闭的连接最终解决大数据量clob保存的流的保存方式的一个解决方案。希望能给其它解决clob保存的同仁一些参考。

读书人网 >软件架构设计

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net