Clash Royale CLAN TAG#URR8PPP

How to insert/update a single record using a MERGE statement with Spring JDBC

I have an update/insert SQL query that I created using a MERGE statement. Using either JdbcTemplate or NamedParameterJdbcTemplate, does Spring provide a method that I can use to update a single record, as opposed to a Batch Update?

Since this query will be used to persist data from a queue via a JMS listener, I'm only dequeuing one record at a time, and don't have need for the overhead of a batch update.

If a batch is the only way to do it through Spring JDBC, that's fine... I just want to make certain I'm not missing something simpler.

update

Update

batchUpdate

2 Answers
2

Just use one of update methods, for example this one: JdbcTemplate#update instead of BatchUpdate.
Update updates a single record, batchUpdate updates multiple records using JDBC batch

update

Update

batchUpdate

You can use a SQL MERGE statment using only a one row query containing your parameters.

MERGE

For example if you have a table COMPANYcontaing IDas a key and NAMEas an attribute, the MERGE statement would be:

COMPANY

ID

NAME

MERGE

merge into company c
using (select ? id, ? name from dual) d
on (c.id = d.id)
when matched then update
set c.name = d.name
when not matched then insert (c.id, c.name)
values(d.id, d.name)


If your target table contains the parametrised key, the name will be updated, otherwise a new record will be inserted.

name

With JDBCTemplate you use the update method to call the MERGEstatement, as illustrated below (using Groovy script)

update

MERGE

def id = 1
def name = 'NewName'

String mergeStmt = """merge into company c
using (select ? id, ? name from dual) d
on (c.id = d.id)
when matched then update
set c.name = d.name
when not matched then insert (c.id, c.name)
values(d.id, d.name)""";

def updCnt = jdbcTemplate.update(mergeStmt, id, name);

println "merging $id, name $name, merged rows $updCnt"


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.