java - Spring JdbcTemplate - 插入 blob 并返回生成的 key

Question

从 Spring JDBC 文档中，我知道如何 insert a blob using JdbcTemplate

final File blobIn = new File("spring2004.jpg");
final InputStream blobIs = new FileInputStream(blobIn);
jdbcTemplate.execute(
  "INSERT INTO lob_table (id, a_blob) VALUES (?, ?)",
  new AbstractLobCreatingPreparedStatementCallback(lobhandler) {                         
      protected void setValues(PreparedStatement ps, LobCreator lobCreator) 
          throws SQLException {
        ps.setLong(1, 1L);
        lobCreator.setBlobAsBinaryStream(ps, 2, blobIs, (int)blobIn.length());           
      }
  }
);
blobIs.close();

还有如何 retrieve the generated key of a newly inserted row :

KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
    new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps =
                connection.prepareStatement(INSERT_SQL, new String[] {"id"});
            ps.setString(1, name);
            return ps;
        }
    },
    keyHolder);

// keyHolder.getKey() now contains the generated key

有没有办法将两者结合起来？

Answer 1

我来到这里寻找相同的答案，但对接受的答案不满意。因此，我进行了一些挖掘，并提出了这个我在 Oracle 10g 和 Spring 3.0 中测试过的解决方案

public Long save(final byte[] blob) {
  KeyHolder keyHolder = new GeneratedKeyHolder();
  String sql = "insert into blobtest (myblob) values (?)"; //requires auto increment column based on triggers
  getSimpleJdbcTemplate().getJdbcOperations().update(new AbstractLobPreparedStatementCreator(lobHandler, sql, "ID") {
    @Override
    protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException {
      lobCreator.setBlobAsBytes(ps, 1, blob);
    }
  }, keyHolder);

  Long newId = keyHolder.getKey().longValue();
  return newId;
}

这还需要以下抽象类，部分基于 Spring 的 AbstractLobCreatingPreparedStatementCallback

public abstract class AbstractLobPreparedStatementCreator implements PreparedStatementCreator {
  private final LobHandler lobHandler;
  private final String sql;
  private final String keyColumn;
  public AbstractLobPreparedStatementCreator(LobHandler lobHandler, String sql, String keyColumn) {
    this.lobHandler = lobHandler;
    this.sql = sql;
    this.keyColumn = keyColumn;
  }
  public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
    PreparedStatement ps = con.prepareStatement(sql, new String[] { keyColumn });
    LobCreator lobCreator = this.lobHandler.getLobCreator();
    setValues(ps, lobCreator);
    return ps;
  }
  protected abstract void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException;
}

此外，您在 Oracle 中创建的表应该有一个使用序列和触发器的 id 自动递增列。触发器是必要的，因为否则您必须使用 Spring 的 NamedParameterJdbcOperations(在 SQL 中执行 sequence.nextval)，它似乎不支持 KeyHolder(我用它来检索自动生成 ID)。有关更多信息，请参阅此博客文章(不是我的博客):http://www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle/

create table blobtest (
id number primary key,
myblob blob);

create sequence blobseq start with 1 increment by 1;

CREATE OR REPLACE TRIGGER blob_trigger
BEFORE INSERT
ON blobtest
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT blobseq.nextval INTO :NEW.ID FROM dual;
end;
/