Spring JdbcTemplate - Insert blob and return generated key

Question

From the Spring JDBC documentation, I know how to 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();

And also how to 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

Is there a way I could combine the two?

Answer 1

I ended up just performing two queries, one to create the row and one to update the blob.

int id = insertRow();
updateBlob(id, blob);

Looking at the Spring source code and extracting the needed parts, I came up with this:

final KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
getJdbcTemplate().execute(
    "INSERT INTO lob_table (blob) VALUES (?)",
    new PreparedStatementCallback() {
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
            LobCreator lobCreator = lobHandler.getLobCreator();
            lobCreator.setBlobAsBinaryStream(ps, 2, blobIs, (int)blobIn.length());

            int rows = ps.executeUpdate();
            List generatedKeys = generatedKeyHolder.getKeyList();
            generatedKeys.clear();
            ResultSet keys = ps.getGeneratedKeys();
            if (keys != null) {
                try {
                    RowMapper rowMapper = new ColumnMapRowMapper();
                    RowMapperResultSetExtractor rse = new RowMapperResultSetExtractor(rowMapper, 1);
                    generatedKeys.addAll((List) rse.extractData(keys));
                }
                finally {
                    JdbcUtils.closeResultSet(keys);
                }
            }
            if (logger.isDebugEnabled()) {
                logger.debug("SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys");
            }
            return new Integer(rows);
        }
    }
);

I can't say I fully understand what is going on here. I'm not sure if the complicated method to extract the generated key is necessary in this simple case, and I'm not entirely clear about the benefit of even using JdbcTemplate when the code gets this hairy.

Anyway, I tested the above code and it works. For my case, I decided it would complicate my code too much.

Answer 2

In case your underlying database is mysql, you can autogenerate your primary key. Then to insert a record into your db, you can use the following syntax for insertion:

INSERT INTO lob_table (a_blob) VALUES (?)