Oracle - 在同一查询中更新记录并返回更新日期

2022-01-17 00:00:00 sql-update oracle java jdbctemplate

我正在使用带有 Spring 的 JdbcTemplate 和 Oracle 12.1 的 Java 8，

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 时间

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.

恕我直言，这个特殊问题使 其他答案中提出的 plain 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.

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

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

相关文章