How to fetch a CLOB column using dbUtils in oracle11g?

Question

I can be fetch CLOB values from database but if values is more then 32k it is getting error :

java.sql.SQLRecoverableException: Closed Connection
        at oracle.sql.CLOB.getDBAccess(CLOB.java:1510)
        at oracle.sql.CLOB.getSubString(CLOB.java:317)

Code :

List<Map<String, Object>> resultListMap = null;
try {
                    
    new DbUtilsBeanListHandlerImpl();
    DbUtils.loadDriver(driver);
    Properties connectionProperties = new Properties();
    connectionProperties.put("user", userName);
    connectionProperties.put("password", password);
    conn = DriverManager.getConnection(url, connectionProperties);
    QueryRunner query = new QueryRunner();
          resultListMap = query.query(conn, dbQuery,  new MapListHandler());              

} catch (SQLException se) {
    logger.error("SQLException to connect Database "+se.getMessage(), se);
} finally {
    DbUtils.closeQuietly(conn);
}

How to fetcha CLOB object more then 32k using dbUtils?

Answer 1

The problem isn't so much a problem with Apache Commons DbUtils, but a general problem involving CLOBs.

Normally, when executing a query that returns a CLOB value, the JDBC driver will only load part of the CLOB value when it fetches the row. In your case it seems it fetches about 32K characters. For small CLOB values this saves a round-trip to the database to get the value. However, if the value is larger, the driver won't have fetched the whole value, and you'll need to make a subsequent call to the database to fetch the rest of the CLOB.

In your case, it appears that this happens after the code you've provided above runs. At this point, the database connection has been closed, so it's too late to read the rest of the CLOB data, hence you get an exception about a closed connection.

One way to change this is to replace the default row processor used with DbUtils. The following version pre-fetches CLOB values and stores them as strings. According to this answer, calling ResultSet.getString() on a CLOB value is enough to fetch the entire value:

import java.sql.Clob;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.dbutils.BasicRowProcessor;

public class ClobAwareRowProcessor extends BasicRowProcessor {

    @Override
    public Map<String, Object> toMap(ResultSet resultSet) throws SQLException {
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int columnCount = resultSetMetaData.getColumnCount();

        Map<String, Object> map = new HashMap<>();

        for (int index = 1; index <= columnCount; ++index) {
            String columnName = resultSetMetaData.getColumnName(index);
            Object object = resultSet.getObject(index);
            if (object instanceof Clob) {
                object = resultSet.getString(index);
            }

            map.put(columnName, object);
        }

        return map;
    }
}

To use, replace new MapListHandler() with new MapListHandler(new ClobAwareRowProcessor()).