Apoorv Kashyap
Apoorv Kashyap

Reputation: 51

Spring jdbc template connection closed exception while reading clob object

I have 5k+ characters to read from each row in oracle DB as CLOB for a select list query . After executing the query using springjdbc the below exception is thrown while trying to convert the CLOB object to String.

    public List<MasterData> findByRqstId(int rqstId) {

            String sql = "SELECT * FROM MASTER_DATA WHERE REQUEST_ID = ?";

            List<MasterData> masterDataList = getSimpleJdbcTemplate().query(sql,
                    ParameterizedBeanPropertyRowMapper.newInstance(MasterData.class), rqstId);

            return masterDataList;
        }



    Caller Code



    masterDataList = masterDataDao.findByRqstId(rqstId);
            String outputResponse=null;

            if (masterDataList != null && masterDataList.size() > 0) {

                for (MasterData mData : masterDataList) {
                    Clob clob = mData.getOutputResponse();
                    InputStream in = clob.getAsciiStream();
                        StringWriter w = new StringWriter();
                        IOUtils.copy(in, w);
                        String clobAsString = w.toString();
                    outputResponse = clob.toString();

                }

            }

I am able to get the clob object. But when I try converting the object to string using the above code , I am getting connection closed exception.

Exception in thread "main" java.sql.SQLRecoverableException: Close Connection
    at oracle.sql.CLOB.getDBAccess(CLOB.java:1421)
    at oracle.sql.CLOB.getAsciiStream(CLOB.java:352)

In my knowledge the since the clob object is bigger in size the cache is unable to hold the data for the next operation. The following properties are making no difference.

    <property name="connectionProperties" value="defaultRowPrefetch=1000" />

    <property name="connectionProperties" value="defaultLobPrefetchSize=500000" /> 

Upvotes: 1

Views: 2157

Answers (2)

nibedita
nibedita

Reputation: 1

The reason of the exception is that when you are accessing the blob/clob the connection is already closed.

A solution could be:

  1. Set the data in Dao itself and return the VO
  2. Initiate the transaction through transaction manager (the caller must be a transaction manager).

Upvotes: 0

Apoorv Kashyap
Apoorv Kashyap

Reputation: 51

I am able to retrieve the clob object by convering it as String datatype .Internally I guess Spring takes care of the conversion but its working fine.

Upvotes: 1

Related Questions