PreparedStatement.RETURN_GENERATED_KEYS和Mysql

Question

我试图修复此代码以更新mysql数据库中的日志表5.6您知道如何从数据库中检索主密钥并将其分配给我的id变量(日志)吗？

谢谢

不带getter和setter的日志模型

private int id;
private Timestamp date;
private String userName;
private String event;
private String message;
private String audioPath;

数据库表

CREATE TABLE `log` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`date` datetime(2) NOT NULL DEFAULT '0000-00-00 00:00:00.00',
`user_name` char(60) NOT NULL,
`event` varchar(50) NOT NULL,
`message` text NOT NULL,
`audio_path` varchar(250) NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

添加条目日志

@Override
public int addEntry(Log log) throws SQLException {

    Connection conn = Database.getInstance().getConnection();

    PreparedStatement p = conn.prepareStatement(
            "insert into log ( id, date, user_name, event, message, audio_path) "
            + "values (?,?,?,?,?,?)", 
            PreparedStatement.RETURN_GENERATED_KEYS);

    p.setInt(1, p.getGeneratedKeys()); //error
    p.setTimestamp(2, log.getDate());
    p.setString(3, log.getUserName());
    p.setString(4, log.getEvent());
    p.setString(5, log.getMessage());
    p.setString(6, log.getAudioPath());

    int updated = p.executeUpdate();


    p.close();

    return updated;

}

Answer 1

由于id被声明为AUTO_INCREMENT，所以不必在查询中提及它：

String sql = "insert into log ( date, user_name, event, message, audio_path) values (?,?,?,?,?)";

这样，MySQL将自动生成新的id。

那么，你应该这样做：

PreparedStatement ps = connection.prepareStatement(sql, RETURN_GENERATED_KEYS)
ps.setTimestamp(1, log.getDate());
// ...
ps.setString(5, log.getAudioPath());

int updated = ps.executeUpdate();

ResultSet generatedKeysResultSet = ps.getGeneratedKeys();
// first row of this set will contain generated keys
// in our case it will contain only one row and only one column - generated id
generatedKeysResultSet.next(); // executing next() method to navigate to first row of generated keys (like with any other result set)
long id = generatedKeysResultSet.getLong(1); // since our row contains only one column we won't miss with the column index :)

log.setId(id);

return updated;

此外，在尝试使用示例之后，我建议正确地使用JDBC关闭MySQL Connection和PreparedStatement。有关如何正确创建和关闭资源，请参考this answer。

希望这能有所帮助