jdbc of sqlite getClob()-- java.sql.SQLException: not implemented by SQLite JDBC driver

来源：互联网发布：栈的算法思想编辑：程序博客网时间：2024/05/29 19:50

代码

当使用jdbc的时候

Connection conn;Session s = sessionUtil.getSession();try {    conn = dataSource.getConnection();    PreparedStatement ps = conn            .prepareStatement("select * from T_ARTICLE a order by a.dateTime desc limit ? offset ?");    ps.setInt(1, ARTICLE_PER_PAFE);    ps.setInt(2, (page - 1) * ARTICLE_PER_PAFE);    ResultSet rs = ps.executeQuery();    while (rs.next()) {        Clob content = rs.getClob("content");    }}

错误就出现在getClob这句

当使用spring+hibernate的时候

String sql = "select * from T_ARTICLE a order by a.dateTime desc";Session s = sessionUtil.getSession(); SQLQuery query =s.createSQLQuery(sql); query.setMaxResults(ARTICLE_PER_PAFE);         query.setFirstResult((page-1)*ARTICLE_PER_PAFE);List<Article> articles = query.list(); s.close(); return articles;

错误就出现在query.list()

使用jdbc时的错误信息

java.sql.SQLException: not implemented by SQLite JDBC driver
at org.sqlite.jdbc4.JDBC4ResultSet.unused(JDBC4ResultSet.java:320)
at org.sqlite.jdbc4.JDBC4ResultSet.getClob(JDBC4ResultSet.java:349)
at com.mchange.v2.c3p0.impl.NewProxyResultSet.getClob(NewProxyResultSet.java:346)
at com.etiri.dao.impl.IArticleDao.getArticlesByPage(IArticleDao.java:106)
at com.etiri.service.impl.IArticleService.getArticlesByPage(IArticleService.java:31)
at com.etiri.controller.backstage.ArticleManageController.toArticleList(ArticleManageController.java:61)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:806)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:729)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:151)
……

错误原因

在sqlite jdbc驱动中org.sqlite.jdbc4.JDBC4ResultSet类中,getClob代码如下：

public Clob getClob(int col) throws SQLException {     throw unused(); }protected SQLException unused() {        return new SQLException("not implemented by SQLite JDBC driver");}

也就是说，实际上getClob根本没有实现……这个sqlite jdbc包是sqlite-jdbc-3.8.11.2.jar。一个专业的jdbc包居然会不实现函数而只是抛出异常，感觉很蹊跷啊！所以这个错误原因我也不是百分之百肯定，如果错了，欢迎看到的朋友指出。

解决方案

由于我是使用hibernate+spring，所以这个datasource是由spring注入的，针对这个错误只能使用jdbc的connection，无论是直接通过jdbc还是datasource只要能获得connection就成。Clob对象则通过字符流创建。

Article article = null;Connection conn;Session s = sessionUtil.getSession();try {    conn = dataSource.getConnection();    PreparedStatement ps = conn.prepareStatement("select * from TABLE_ARTICLE a where a.id = ?");    ps.setInt(1, id);    ResultSet rs = ps.executeQuery();    if (rs.next()) {        long length = rs.getLong("contentLength");        Clob content = null;        try {            Reader r = rs.getCharacterStream("content");            if(r != null){                BufferedReader br = new BufferedReader(r);                content =s.getLobHelper().createClob(br.readLine());            }else{                content = null;            }               r.close();        } catch (IOException e1) {            e1.printStackTrace();        }    article = new Article(content);    ps.close();    conn.close();} catch (SQLException e) {    e.printStackTrace();}return article;}

解决过程

遇上这个错误的时候，调试了一天，错误的原因就是not implemented，本来都打算换一个数据库，但是经人提醒不可能一个jdbc包竟然会这样的，想了一下也觉得赞同。在找解决办法途中，突然想到，sqlite包是jdbc4resultset类没实现getclob，jdbc3resultset根本没有这个函数，而数据库里clob肯定是早就存在的，它以前不用getclob方法，必然有其他向它读写的函数，后来就发现了getCharacterStream函数，成功解决该问题。

0 0