Clever Geek Handbook

Attempted to read BLOB as InputStream, but receive "Closed connection error" message. Spring3 getJdbcTemplate ()

I am moving an application to use Spring3 framework and I have code that reads a column BLOB

from Oracle database:

It 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 to write it with Spring using a 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 am getting an exception java.io.IOException: Closed Connection

.

I think Spring has to close the connection for InputStream

before I can process it. Do you guys have a better way to write this?

Edit: Extra depth to 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)
+3


source to share


3 answers


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

.

The simplest solution is to do your processing internally RowMapper

like



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 process the first line, use ResultSetExtractor

instead RowMapper

.

+4


source


Another (possibly simpler) way to prevent your w760 connection from closing prematurely is by starting a transaction before executing the request.

This can be done in code by inserting



session.beginTransaction();

Somewhere before the call to executeQuery.

0


source


According to the documentation, the ResultSet::getBinaryStream

value can be read in "chunks", which means that if you close the connection and then try to read enough bytes from the stream, it will throw an exception when the next chunk is needed but cannot be returned from the database.

Alternatively, you can try to read the entire value using a method ResultSet::getBytes

and then convert it to an InputStream if you need to (create a new one ByteArrayInputStream

).

0


source






More articles:


All Articles