CLOB和BLOB的字段录入和读取

存储操作

private JdbcTemplate jdbcTemplate;
private DefaultLobHandler lobHandler;
//忽略setter和getter操作, 并且注入到spring的Ioc容器中
public void updt() throws IOException{
  final File blobIn = new File("spring2004.jpg");
  final InputStream blobIs = new FileInputStream(blobIn);
  final File clobIn = new File("large.txt");
  final InputStream clobIs = new FileInputStream(clobIn);
  final InputStreamReader clobReader = new InputStreamReader(clobIs);
  jdbcTemplate.execute(
    "INSERT INTO lob_table (id, a_clob, a_blob) VALUES (?, ?, ?)",
    new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
      protected void setValues(PreparedStatement ps, LobCreator lobCreator)
          throws SQLException {
        ps.setLong(1, 1L);
        lobCreator.setClobAsCharacterStream(ps, 2, clobReader, (int)clobIn.length());
        lobCreator.setBlobAsBinaryStream(ps, 3, blobIs, (int)blobIn.length());
      }
    }
  );
  blobIs.close();
  clobReader.close();
}

读取操作

List<Map<String, Object>> l = jdbcTemplate.query(
  "select id, a_clob, a_blob from lob_table",
  new RowMapper<Map<String, Object>>() {
    public Map<String, Object> mapRow(ResultSet rs, int i) throws SQLException {
      Map<String, Object> results = new HashMap<String, Object>();
      String clobText = lobHandler.getClobAsString(rs, "a_clob");
      results.put("CLOB", clobText);
      byte[] blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob");
      results.put("BLOB", blobBytes);
      return results;
    }
  }
);