Spring Jdbc查询执行 [英] Spring Jdbc query execution

问题描述

有人知道我可以使用哪种Spring Jdbc模板方法来执行此"upsert"，或者另一种方法也可以在一个数据库调用中执行操作吗?

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

然后在我的Dao中仅需使用

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

这篇关于Spring Jdbc查询执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！