insert blob and return generated key

[caverns]

Can anyone advise me on how to return Generated Keys whilst also inserting a BLOB ?

My code to insert the BLOB isa as follows:

    getJdbcTemplate().execute
    (
				"insert into documentimage(document_id,indexedby,"+
    "indexedat,indexedwhen,filecreated,filemodified,filesize,filetype,"+
    "versioncomment,binaryimage) values(?,?,?,?,?,?,?,?,?,?)",
				new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
          protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException 
          {
            ps.setLong(1,docImageDTO.getDocumentId().longValue());
            ps.setString(2,docImageDTO.getIndexedBy());            
            ps.setString(3,docImageDTO.getIndexedAt());                        
            ps.setTimestamp(4,docImageDTO.getIndexedWhen());                                    
            ps.setTimestamp(5,docImageDTO.getFileCreated());                                                
            ps.setTimestamp(6,docImageDTO.getFileModified());                                                            
            ps.setBigDecimal(7,docImageDTO.getFileSize());            
            ps.setString(8,docImageDTO.getFileType());       
            ps.setString(9,docImageDTO.getVersionComment());                                    
            lobCreator.setBlobAsBinaryStream(ps, 10, is,10000 );
        }
      }

For everything else other thab BLOB's I've used code as follows:

public class DocumentCreate extends SqlUpdate
{
  public DocumentCreate(DataSource ds)
  {
    setDataSource(ds);
    
    setReturnGeneratedKeys(true);  
    setGeneratedKeysColumnNames(new String[]{"ID"});

and then retrieved the key using a KeyHolder



But I am unsure how to combine the two.

[redtiger]

Try this (Spring 2.0 RC2):

    public long insertImage(final ImageDescriptor image)
            throws DataAccessException {
        
        String sql = "INSERT INTO image "
                + "(name, description, order_num, content) "
                + "VALUES (?,?,?, ?)";
        Object[] params;
        try {
            params = new Object[] {
                    image.getName(), image.getDescription(),
                    image.getOrder(), image.getContent().getBytes() };
        }
        catch(IOException e) {
            throw new RuntimeException("Could not convert "
                    + "the MultipartFile to bytes.", e);
        }
        
        PreparedStatementCreatorFactory psc =
            new PreparedStatementCreatorFactory(sql);
        psc.addParameter(new SqlParameter("name", Types.VARCHAR));
        psc.addParameter(new SqlParameter("description", Types.VARCHAR));
        psc.addParameter(new SqlParameter("order_num", Types.INTEGER));
        psc.addParameter(new SqlParameter("content", Types.BLOB));
        
        KeyHolder generatedKeyHolder = new GeneratedKeyHolder();

        getJdbcTemplate().update(psc.newPreparedStatementCreator(params),
                generatedKeyHolder);
        
        return generatedKeyHolder.getKey().longValue();
    }

You can probably figure it out, but here is the ImageDescriptor class:

public class ImageDescriptor implements Serializable {

    public static final int SHORT_DESCRIPTION_MAX_LENGTH = 2000;
    private Long id;
    private String name;
    private String description;
    /** The order in which the images should be displayed. */
    private int order;
    private MultipartFile content;

    ...
}