Does anyone know how what Spring Jdbc template method I could use to execute this 'upsert' or an alternative approach that would also perform the operations in one database call?
UPDATE jasper_report SET Uri = 'update' WHERE ReportId = 99;
IF @@ROWCOUNT = 0 AND Exists(Select 1 FROM report Where Id = 99)
BEGIN
INSERT INTO jasper_report (ReportId, Uri) VALUES (99, 'insert')
END;
Turns out I was close but forget a step.
I had to change the query itself to:
BEGIN
UPDATE jasper_report SET Uri = ? WHERE ReportId = ?
IF @@ROWCOUNT = 0 AND EXISTS(SELECT 1 FROM report WHERE Id = ?)
BEGIN
INSERT INTO jasper_report (ReportId, Uri) VALUES (?, ?)
END
END
Then in my Dao only needed to use Spring's JdbcTemplate update method. It looks something like this:
@Repository("jasperReportsDao")
public class JasperReportsDaoImpl extends JdbcTemplate implements JasperReportsDao {
@Override
public void saveJasperReport(JasperReport report) {
// If a record already exists, do an update, otherwise, do an insert
int rowsAffected = this.update(UPSERT_JASPER_REPORT, new Object[] { report.getUri(), report.getId(),
report.getId(), report.getId(), report.getUri()} );
if(log.isDebugEnabled()) { log.debug("Rows affected: " + rowsAffected); }
}
}
Shouldn't that be Not Exists?
At any rate I think it would work fine without the Not Exists, since @@ROWCOUNT is already giving you that info:
UPDATE jasper_report SET Uri = 'update' WHERE ReportId = 99;
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO jasper_report (ReportId, Uri) VALUES (99, 'insert')
END;
