CLOB和BLOB的字段录入和读取

最新推荐文章于 2021-02-07 21:09:16 发布

RaylnGuan

最新推荐文章于 2021-02-07 21:09:16 发布

阅读量403

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/rayln/article/details/39152775

本文介绍如何利用Spring的JdbcTemplate组件进行LOB（大型对象）数据的存储和读取，包括使用File作为数据源创建Blob和Clob实例，并通过自定义的LobHandler实现数据的流式处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

存储操作

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;
    }
  }
);