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

代码

1. 当使用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这句

1. 当使用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函数，成功解决该问题。