Java Spring JdbcTemplate - 插入 blob 并返回生成的密钥

Spring JdbcTemplate - Insert blob and return generated key

提问by itsadok

From the Spring JDBC documentation, I know how to insert a blob using JdbcTemplate

从 Spring JDBC 文档中，我知道如何使用 JdbcTemplate 插入 blob

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?

有没有办法将两者结合起来？

采纳答案by BenCourliss

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

我来到这里寻找相同的答案，但对接受的内容并不满意。所以我做了一些挖掘并想出了我在 Oracle 10g 和 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

这还需要以下抽象类，部分基于 Spring 的 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/

此外，您在 Oracle 中创建的表应该有一个使用序列和触发器的 id 自动递增列。触发器是必要的，因为否则你必须使用 Spring 的 NamedParameterJdbcOperations（在你的 SQL 中执行 sequence.nextval），它似乎不支持 KeyHolder（我用它来检索自动生成 ID）。有关更多信息，请参阅此博客文章（不是我的博客）：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;
/

回答by itsadok

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

我最终只执行了两个查询，一个用于创建行，另一个用于更新 blob。

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

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

查看 Spring 源代码并提取所需的部分，我想出了这个：

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.

我不能说我完全理解这里发生了什么。我不确定在这种简单的情况下是否需要使用复杂的方法来提取生成的密钥，而且当代码变得如此繁琐时，我也不完全清楚使用 JdbcTemplate 的好处。

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

无论如何，我测试了上面的代码并且它有效。就我而言，我认为这会使我的代码过于复杂。

回答by Snehal

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:

如果您的底层数据库是 mysql，您可以自动生成主键。然后要将记录插入到您的数据库中，您可以使用以下语法进行插入：

INSERT INTO lob_table (a_blob) VALUES (?)

回答by iMysak

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
}

回答by adarshr

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

2012 年，SimpleJdbcTemplate已弃用。这就是我所做的：

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:

SQL 看起来像这样：

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

回答by Vicky

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

    }
}

回答by Lisa

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

所有这些对我来说似乎太复杂了。这很有效而且很简单。它用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();
        }
    }

回答by Eugene Han

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"

这仅在 MySql 上进行了测试，我只粘贴了相关部分。运行我的测试类后，结果如下所示：“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());

回答by Mukesh

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

}

回答by Breton F.

Another solution with lambda (which is not required):

lambda 的另一个解决方案（不是必需的）：

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.

注意。抱歉，这不包括 KeyGenerator。