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

Question

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.

Just use update method, for example this one: JdbcTemplate#update instead of BatchUpdate. Update updates a single record, batchUpdate updates multiple records using JDBC batch. — krokodilko
@krokodiko : Thank you! That is exactly what I needed to know. You were first in, so if you provide an official answer, I will vote for your answer. Everyone has been helpful. I initially tried Spring's update methods, but due to a problem with my parameterized list, I misinterpreted the exception and determined that UPDATE was not the right method. I had not realized that with Spring JDBC, it is simply a single or batch update. I looked for a more specific method and could not find one, so your short answer gave me the insight I needed to solve my issue. Thank you for your answer! — Michael M

Marmite Bomber Marmite Bomber · Accepted Answer · 2018-08-06T21:39:35

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

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

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.

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

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}"

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

2 Answers