By itsadok


2010-05-05 05:43:34 8 Comments

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?

11 comments

@Sanjeev Lahariya 2019-05-19 12:55:15

I got the same issue to update blob data -- need to update image into database. than i find some solution as below. for more details update image into database

 LobHandler lobHandler = new DefaultLobHandler();
 statusRes = jdbcTemplate.update("update  USERS set FILE_CONTENT = ?, FILE_NAME = ? WHERE lower(USER_ID) = ?",
               new Object[] {new SqlLobValue(image, lobHandler),fileName,userIdLower},
               new int[] {Types.BLOB,Types.VARCHAR,Types.VARCHAR});

@Breton F. 2018-02-13 20:29:29

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.

@Vicky 2013-02-03 04:39:12

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();
        }

    }
}

@itsadok 2013-02-03 12:19:09

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

@Avdhut 2018-10-07 09:40:14

this is great, it solved my problem

@Mukesh 2015-09-11 19:30:16

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);

}

@CinCout 2015-09-11 19:38:00

Mind explaining your answer?

@Eugene Han 2015-02-07 02:12:53

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

@Lisa 2014-09-05 17:53:01

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();
        }
    }

@itsadok 2014-09-07 12:04:00

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?

@adarshr 2012-10-05 14:43:55

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

@iMysak 2012-01-05 23:44:45

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
}

@itsadok 2012-01-09 08:24:21

Where's the blob?

@iMysak 2012-01-12 17:20:44

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

@BenCourliss 2011-03-03 22:16:42

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;
/

@itsadok 2012-10-09 07:26:04

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

@itsadok 2010-05-05 08:32:47

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.

@Snehal 2010-05-05 08:35:43

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

Related Questions

Sponsored Content

1 Answered Questions

How to insert and get key value data in blob file(MYsql)?

4 Answered Questions

1 Answered Questions

java.sql.Statement.EXECUTE_FAILED Scenario in Spring JdbcTemplate

1 Answered Questions

Using RowMapper and JdbcTemplate got NullPointerException

2 Answered Questions

1 Answered Questions

insert blob with spring jdbctemplate

5 Answered Questions

[SOLVED] JPA vs Spring JdbcTemplate

0 Answered Questions

Spring JDBCTemplate throwing BadSqlGrammarException

2 Answered Questions

[SOLVED] Spring JdbcTemplate Insert throws uncategorized SQLException

4 Answered Questions

[SOLVED] Loading Numbers from a File Instead of Words

  • 2011-05-21 21:16:30
  • Imnotanerd
  • 323 View
  • 2 Score
  • 4 Answer
  • Tags:   java swing file io

Sponsored Content