CODEWITHSUNDEEP
javaspringjdbcblobjdbctemplate
Sid
Sid

Reputation: 215

Connection Closed error on querying BLOBs from JdbcTemplate

I am querying a BLOB object from database and trying to write it in file system but I keep running into connection closed error. Here's the code

FileOutputStream out;

out = new FileOutputStream(filePathtoCreate+File.separator+filename);

        Blob inBlob= jdbcTemplate.queryForObject("select BLOB_CONTENT from Table_A where name = ?" , Blob.class,new Object[] { filename});
        InputStream in = inBlob.getBinaryStream();
        byte[] buf = new byte[1024];
        int len = 0;
        while ((len = in.read(buf)) != -1) {
            out.write(buf, 0, len);
        }
        in.close();
        out.close();
        } catch (SQLException | IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

When I run this code I get 

java.sql.SQLRecoverableException: Closed Connection
    at oracle.sql.BLOB.getDBAccess(BLOB.java:1122)
    at oracle.sql.BLOB.getBinaryStream(BLOB.java:265)

I do not get this issue if I go by regular JDBC Connection, so why is this happening and how do I resolve it. Thanks in advance.

Upvotes: 1

Views: 821

Answers (1)

Ori Marko
Ori Marko

Reputation: 58774

You can read Blob inside jdbcTemplate's query with RowMapper, example:

jdbcTemplate.query("select * from Report where id =?",
              new Object[]{id}, (resultSet, i) -> {
                  return toReport(resultSet);
              });

private Report toReport(ResultSet resultSet) throws SQLException {
  InputStream contentStream = resultSet.getClob("CONTENT")
                                       .getAsciiStream();
  String content =
          new Scanner(contentStream, "UTF-8").useDelimiter("\\A").next();
  report.setContent(content);
  Blob blob = resultSet.getBlob("IMAGE");

Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the query, mapping each row to a result object via a RowMapper.

Upvotes: 1

Related Questions