Announcement Announcement Module
Collapse
No announcement yet.
insert blob and return generated key Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • insert blob and return generated key

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

    My code to insert the BLOB isa as follows:

    Code:
        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:

    Code:
    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.

  • #2
    Try this (Spring 2.0 RC2):

    Code:
        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:
    Code:
    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;
    
        ...
    }

    Comment

    Working...
    X