带准备好的语句的返回时间戳记 [英] Return Timestamp With Prepared Statement

问题描述

我有一个自动生成的时间戳，每次在mysql表中插入或更新一条记录时都会创建该时间戳.有没有一种方法可以类似于我使用密钥持有者返回新创建的ID的方式来返回此时间戳?

I have an auto generated timestamp that is created each time a record is inserted or updated in a mysql table. Is there a way to return this timestamp in a way similar to how I would use a keyholder to return a newly created id?

KeyHolder keyHolder = new GeneratedKeyHolder();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

//Insert Contact
jdbcTemplate.update(new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(SQL_ADD, Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, contact.getFirstName());
        preparedStatement.setString(2, contact.getLastName());
        preparedStatement.setInt(3, contact.getOrganizationId());
        preparedStatement.setString(4, contact.getType());
        preparedStatement.setInt(5, contact.getUserId());
        return preparedStatement;
        }   
}, keyHolder);
//Use keyholder to obtain newly created id
contact.setId(keyHolder.getKey().intValue());

是否有某种方法也可以返回新的时间戳而不必重新查询表?我一直在寻找将ID与ID一起作为密钥返回给密钥持有人的方法，但似乎并没有将其作为密钥返回?

Is there some way to also return the new timestamp without having to requery the table? I have been looking for ways to return it along with the id as a key in the keyholder, but it doesn't seem to be returned as a key?

推荐答案

在MySQL数据库服务器端，解决此问题的方法很少.您可以从在表上创建TRIGGER开始.由于TRIGGER有限制并且不能返回数据，因此可以将TIMESTAMP值设置为变量:

There are few options for solving this issue on the MySQL database server side. You could start with creating a TRIGGER on the table. As TRIGGER has a restriction and cannot return data, you can set the TIMESTAMP value to a variable:

DEMILITER //
CREATE TRIGGER ai_tbl_name AFTER INSERT ON tbl_name
FOR EACH ROW
BEGIN

SET @TimeStamp = NEW.timestamp_column;

END;//
DELIMITER ;

要获取此时间戳记值，请运行以下命令:

To retrieve this timestamp value, run the following command:

SELECT @TimeStamp;

由于变量存储在内存中，因此无需再次打开任何表.

Since the variables are stored in the memory, there will be no need to open any tables again.

您走得更远.您可以在MySQL中创建一个STORED PROCEDURE来自动完成上述所有操作(示例代码，因为我不知道您表的详细信息):

You go even further. You could create a STORED PROCEDURE in MySQL to automate all the above (sample code, as I do not know your table's details):

DELIMITER //
DROP PROCEDURE IF EXISTS sp_procedure_name //
CREATE PROCEDURE sp_procedure_name (IN col1_val VARCHAR(25),
                                    IN col2_val VARCHAR(25),
                                    IN col3_val INT)
BEGIN
    INSERT INTO tbl_name (col1, col2, col3)
    VALUES (col1_val, col2_val, col3_val);

    SELECT @TimeStamp;
END; //
DELIMITER ;

您可以使用以下代码运行此过程:

You can run this procedure with the following code:

CALL sp_procedure_name(col1_val, col2_val, col3_val);

由于我对Java不熟悉，因此您需要在代码方面完成它.

As I'm not familiar with the Java, you'll need to finish it up with your side of code.

这篇关于带准备好的语句的返回时间戳记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！