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?

Soler

Answer 1

I came here looking for the same answer, but wasn't satisfied with what was accepted. So I did a little digging around and came up with this solution that I've tested in Oracle 10g and 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;
}

this also requires the following abstract class, based in part on Spring's 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;
}

Also, the table you create in Oracle should have an auto-incremented column for the id using a sequence and trigger. The trigger is necessary because otherwise you'd have to use Spring's NamedParameterJdbcOperations (to do the sequence.nextval in your SQL) which doesn't seem to have support for KeyHolder (which I use to retrieve the auto-gen id). See this blog post (not my blog) for more info: 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;
/

Carthusian

Accepting this answer because of all of the upvotes. You should replace getSimpleJdbcTemplate().getJdbcOperations() with getJdbcTemplate(), since SimpleJdbcTemplate is now deprecated. – Soler

Answer 2

All of this seemed way too complicated to me. This works and is simple. It uses org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.support.lob.DefaultLobHandler;


    public void setBlob(Long id, byte[] bytes) {
        try {
            jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
            MapSqlParameterSource parameters = new MapSqlParameterSource();
            parameters.addValue("id", id);
            parameters.addValue("blob_field", new SqlLobValue(new ByteArrayInputStream(bytes), bytes.length, new DefaultLobHandler()), OracleTypes.BLOB);
            jdbcTemplate.update("update blob_table set blob_field=:blob_field where id=:id", parameters);
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

Introversion

The question was about inserting a new row with a blob and getting the newly-generated key back. Your code updates an existing row with a blob. Can I assume you meant just insert the row without the blob as a first step and then do this? – Soler

Answer 3

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.

Soler

Answer 4

In 2012, SimpleJdbcTemplate is deprecated. This is what I did:

KeyHolder keyHolder = new GeneratedKeyHolder();

List<SqlParameter> declaredParams = new ArrayList<>();

declaredParams.add(new SqlParameter(Types.VARCHAR));
declaredParams.add(new SqlParameter(Types.BLOB));
declaredParams.add(new SqlParameter(Types.VARCHAR));
declaredParams.add(new SqlParameter(Types.INTEGER));
declaredParams.add(new SqlParameter(Types.INTEGER));

PreparedStatementCreatorFactory pscFactory = 
    new PreparedStatementCreatorFactory(SQL_CREATE_IMAGE, declaredParams);

pscFactory.setReturnGeneratedKeys(true);

getJdbcTemplate().update(
    pscFactory.newPreparedStatementCreator(
        new Object[] {
            image.getName(), 
            image.getBytes(), 
            image.getMimeType(), 
            image.getHeight(),
            image.getWidth() 
        }), keyHolder);

image.setId(keyHolder.getKey().intValue());

The SQL looks like this:

INSERT INTO image (name, image_bytes, mime_type, height, width) VALUES (?, ?, ?, ?, ?)

Fallible

Answer 5

package com.technicalkeeda.dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobHandler;

public class ImageDaoImpl implements ImageDao {

    private DataSource dataSource;

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(this.dataSource);
    }

    @Override
    public void insertImage() {
        System.out.println("insertImage" + jdbcTemplate);

        try {
            final File image = new File("C:\\puppy.jpg");
            final InputStream imageIs = new FileInputStream(image);

            LobHandler lobHandler = new DefaultLobHandler(); 

            jdbcTemplate.update(
                     "INSERT INTO trn_imgs (img_title, img_data) VALUES (?, ?)",
                     new Object[] {
                       "Puppy",
                       new SqlLobValue(imageIs, (int)image.length(), lobHandler),
                     },
                     new int[] {Types.VARCHAR, Types.BLOB});


        } catch (DataAccessException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

    }
}

Strepitous

You inserted a BLOB, but didn't get the generated key. – Soler

Answer 6

This is tested on MySql only and I only pasted the relevant part. After Running my test class, the result is shown below: "record added via template.update(psc,kh): 1 added and got key 36"

final byte[] bytes = "My Binary Content".getBytes();
final ByteArrayInputStream bais = new ByteArrayInputStream(bytes);        
PreparedStatementCreator psc = new PreparedStatementCreator() {
        PreparedStatement ps = null;
        public PreparedStatement createPreparedStatement(
                Connection connection) throws SQLException {
            dummy.setStringCode("dummy_jdbc_spring_createPS_withKey_lob");
            ps = connection
                    .prepareStatement(
                            "INSERT INTO DUMMY (dummy_code, dummy_double, dummy_date, dummy_binary) VALUES (?, ?, ?,?)",
                            Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, dummy.getStringCode());
            ps.setDouble(2, dummy.getDoubleNumber());
            ps.setDate(3, dummy.getDate());
            new DefaultLobHandler().getLobCreator().setBlobAsBinaryStream(
                    ps, 4, bais, bytes.length);

            return ps;
        }
    };
KeyHolder holder = new GeneratedKeyHolder();
System.out.println("record added via template.update(psc,kh): "
            + template.update(psc, holder)+" added and got key " + holder.getKey());

Satang

Answer 7

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 (?)

Whip

Answer 8

Another solution with lambda (which is not required):

jdbcTemplate.update(dbcon -> {
    PreparedStatement ps = dbcon.prepareStatement("INSERT INTO ...");
    ps.setString(1, yourfieldValue);
    ps.setBinaryStream(2, yourInputStream, yourInputStreamSizeAsInt));
    return ps;
});

NB. Sorry this does not include KeyGenerator.

Underlet

Answer 9

Maybe some like this:

public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
private SimpleJdbcInsert insertActor;

public void setDataSource(DataSource dataSource) {
    this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    this.insertActor =
            new SimpleJdbcInsert(dataSource)
                    .withTableName("t_actor")
                    .usingGeneratedKeyColumns("id");
}

public void add(Actor actor) {
    Map<String, Object> parameters = new HashMap<String, Object>(2);
    parameters.put("first_name", actor.getFirstName());
    parameters.put("last_name", actor.getLastName());
    Number newId = insertActor.executeAndReturnKey(parameters);
    actor.setId(newId.longValue());
}

//  ... additional methods
}

Cancellation

Does actor.getLastName() can't be a blob? – Cancellation

Answer 10

Please use:

addValue("p_file", noDataDmrDTO.getFile_data(), Types.BINARY)

noDataDmrDTO.getFile_data() is byte array.


{
 simpleJdbcCall =
          new SimpleJdbcCall(jdbcTemplate).withProcedureName("insert_uploaded_files").withCatalogName("wct_mydeq_stg_upld_pkg")
              .withSchemaName("WCT_SCHEMA");

 SqlParameterSource sqlParms =
        new MapSqlParameterSource().addValue("p_upload_idno", Integer.parseInt("143"))
            .addValue("p_file_type_idno", Integer.parseInt(noDataDmrDTO.getFile_type_idno())).addValue("p_file_name", noDataDmrDTO.getFile_name())
            .addValue("p_file", noDataDmrDTO.getFile_data(), Types.BINARY).addValue("p_comments", noDataDmrDTO.getComments())
            .addValue("p_userid", noDataDmrDTO.getUserid());


    simpleJdbcCallResult = simpleJdbcCall.execute(sqlParms);

}

Underlay

Mind explaining your answer? – Drennan