Oracle-在同一查询中更新记录并返回更新日期 [英] Oracle - update record and return updated date in same query

问题描述

我正在将Java 8与Spring的JdbcTemplate和Oracle 12.1结合使用，

I'm using Java 8 with Spring's JdbcTemplate and Oracle 12.1,

我想更新记录并获取准确的时间记录已更新

I want to update record and get the exact time record was updated

jdbcTemplate.update(UPDATE_SQL, null);

当前它返回(int)受影响的行数，但是我想要确切的更新日期

Currently it returns (int) the number of rows affected, but I want the exact updated date

我是否必须发送新请求以获取当前时间(可能不准确)?

Must I send a new request to get current time which may be inaccurate?

更确切的做法是将更新日期保存在列中，然后执行另一个SQL

More exact will be to save in column updated date, but then to execute another SQL

是否还有另一种方法可以在一个查询中获取更新的日期?

Is there another option to get updated date in one query?

很明显，我也不想使用代码中的获取日期(如new Date())，这也是因为服务器时间与DB Time不同/可能与DB Time不同

Obviously, I don't want to use get date from code also (as new Date()) also because server time is/can be different than DB Time

推荐答案

与普通的JDBC 相比，您决定最可能使用JDBCTemplate来简化代码.

You decided to use JDBCTemplate most probably to simplify the code in comparison to plain JDBC.

恕我直言，此特殊问题使其他答案中提出的普通JDBC 解决方案更加简单，所以我绝对建议从JDBCTemplate获取数据库连接并以JDBC方式进行插入.

This particular problem IMHO makes the plain JDBC solution as proposed in other answer much simpler, so I'd definitively recommend to get the database connection from JDBCTemplate and make the insert in a JDBC way.

我想到的最简单的使用JDBCTemplate的解决方案是将插入内容包装在PROCEDURE中，并将时间戳记作为OUT参数返回.

The simplest solution using JDBCTemplate that comes to my mind is to wrap the insert in a PROCEDURE and return the timestamp as an OUT parameter.

简单示例(根据需要调整时间logik)

Simple example (Adjust the time logik as required)

create procedure insert_with_return_time (p_str VARCHAR2, p_time OUT DATE) as
BEGIN 
   insert into identity_pk(pad) values(p_str);
   p_time := sysdate;
END;
/

使用SimpleJdbcCall

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("insert_with_return_time");
SqlParameterSource params = new MapSqlParameterSource().addValue("p_str", str);
Map<String, Object> out = jdbcCall.execute(params);

Map包含返回值，例如[P_TIME:2019-10-19 11:58:10.0]

但是我只能重复一遍，在这个特殊的用例中，恕我直言 JDBC是JDBCTemplate的救助;)

But I can only repeat, in this particular use case is IMHO JDBC a rescue from JDBCTemplate;)

这篇关于Oracle-在同一查询中更新记录并返回更新日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！