gpt4 book ai didi

mysql - PreparedStatement.RETURN_GENERATED_KEYS 和 Mysql

转载 作者:搜寻专家 更新时间:2023-10-30 23:47:45 25 4
gpt4 key购买 nike

我正在尝试修复此代码以更新我在 mysql 数据库 5.6 中的日志表你知道如何从数据库中检索主键并将其分配给我的id变量(Log)

谢谢

没有 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;

}

最佳答案

由于 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 ConnectionPreparedStatement。请引用this answer关于如何正确创建和关闭资源。

希望对你有帮助

关于mysql - PreparedStatement.RETURN_GENERATED_KEYS 和 Mysql,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26097451/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com