StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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</p> <pre><code>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; } </code></pre> <p>this also requires the following abstract class, based in part on Spring's AbstractLobCreatingPreparedStatementCallback</p> <pre><code>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; } </code></pre> <p>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: <a href="http://www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle/" rel="noreferrer">http://www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle/</a></p> <pre><code>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; / </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload