CODEWITHSUNDEEP
javaspringjdbcspring-3
jonasespelita
jonasespelita

Reputation: 1650

Trying to read a BLOB as an InputStream but getting Connection Closed error. Spring3 getJdbcTemplate()

I'm moving an application to use the Spring3 framework and I have code that reads a BLOB column from an Oracle Database:

This works:

        String fileSqlStr =
                "select file_id, file_content from cpm_file where file_id = 4";
        PreparedStatement ps = conn.prepareStatement(fileSqlStr);
        ResultSet rs = ps.executeQuery();
        rs.next();
        int fileId = rs.getInt("file_id");
        InputStream fis = rs.getBinaryStream("file_content");
        ExlBOMImporter ei = new ExlBOMImporter(fis);

But when I try writing it with Spring using the JdbcTemplate bean:

 InputStream is = getJdbcTemplate().query(getFileContentSql, new RowMapper<InputStream>() {

                public InputStream mapRow(ResultSet rs, int rowNum) throws SQLException {
                    OracleLobHandler lobHandler = new OracleLobHandler();
                    return lobHandler.getBlobAsBinaryStream(rs, "file_content");
                }
            }, fileId).get(0);
 ExlImporter importer = new ExlBOMImporter(is);
 importer.process();

I'm getting an java.io.IOException: Closed Connection Exception.

I'm thinking that Spring must be closing the connection for the InputStream before I get around to process it. Would you guys have a better way of writing this?

Edit: Some more depth to the Exception:

java.io.IOException: Closed Connection
        at oracle.jdbc.driver.OracleBlobInputStream.needBytes(OracleBlobInputStream.java:204)
        at oracle.jdbc.driver.OracleBufferedStream.readInternal(OracleBufferedStream.java:169)
        at oracle.jdbc.driver.OracleBufferedStream.read(OracleBufferedStream.java:143)
        at org.apache.poi.util.IOUtils.readFully(IOUtils.java:92)
        at org.apache.poi.util.IOUtils.readFully(IOUtils.java:77)
        at oracle.jdbc.driver.OracleBlobInputStream.needBytes(OracleBlobInputStream.java:204)

Upvotes: 4

Views: 12702

Answers (3)

Gabriel Belingueres
Gabriel Belingueres

Reputation: 2975

As per ResultSet::getBinaryStream documentation, the value can be read in "chunks", that means that if you close the connection and then try to read enough bytes from the stream, it will raise an exception when the next chunk is needed but can not be returned from the database.

As an alternative, you may try reading the whole value by using the ResultSet::getBytes method, and then convert it to an InputStream if you need to (instantiating a new ByteArrayInputStream).

Upvotes: 0

Chris Johansen
Chris Johansen

Reputation: 181

Another (perhaps simpler) way of preventing Spring from closing your connection prematurely, is by starting a transaction before executing the query.

This can be done in code by inserting

session.beginTransaction();

Somewhere before executeQuery call.

Upvotes: 0

skaffman
skaffman

Reputation: 403441

Yes, Spring will tidy up the connection when it leaves the query method.

Easiest solution is to perform your processing inside the RowMapper, e.g.

getJdbcTemplate().query(getFileContentSql, new RowMapper<Void>() {
    public void mapRow(ResultSet rs, int rowNum) throws SQLException {
        OracleLobHandler lobHandler = new OracleLobHandler();
        InputStream inputStream = lobHandler.getBlobAsBinaryStream(rs, "file_content");
        ExlImporter importer = new ExlBOMImporter(inputStream);
        importer.process();
    }
}, fileId);

If you only want to handle the first row, then use a ResultSetExtractor instead of a RowMapper.

Upvotes: 4

Related Questions