JDBC PreparedStatement始终返回1作为自动生成的键 [英] JDBC PreparedStatement always returns 1 as auto generated key

问题描述

我有这段代码试图在数据库中插入一条记录:

I have this code trying to insert a record in the database:

try {
 Connection conn = getConnection();

 String sql = 
   "INSERT INTO myTable(userId,content,timestamp) VALUES(?,?,NOW())";
 PreparedStatement st = 
    conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

 st.setLong(1, userId);
 st.setString(2, content);
 id = st.executeUpdate(); //this is the problem line            
} catch(Exception e) {}

问题是，尽管正确插入了记录，但我希望id包含刚插入的记录的主键+ auto_increment id.但是，由于某些原因，它总是返回"1"作为id，这可能是因为在插入过程中userId的值为1的原因.

The problem is, though the record is inserted correctly, I want id to contain the primary key + auto_increment id of the record that was just inserted. However, for some reason, it always returns '1' as the id, possibly because the value of userId is 1 during the inserts.

我的表是InnoDB.起初userId是另一个表的外键，但是由于此后我删除了外键，甚至删除了userId列上的索引，但是我仍然得到1作为返回值.

My table is InnoDB. At first userId was a foreign key to another table, owever I've since deleted the foreign key and even the index on the userId column, but I'm still getting 1 as the return value.

有什么想法我在做什么错吗?

Any ideas what I'm doing wrong?

推荐答案

PreparedStatment.executeUpdate()

返回:
(1)SQL数据操作语言(DML)语句的行数或(2)不返回任何内容的SQL语句的行数

Returns:
either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

您需要改用execute()并将ResultSet与getGeneratedKeys()一起使用；它会包含您想要的数据.

You need to use execute() instead and get the ResultSet with getGeneratedKeys(); it's going to contain the data you want.

编辑以添加:我读了您的问题，因为表中有一个不是 userId

Edit to add: I read your question as there is an auto-increment field in the table that is not userId

这篇关于JDBC PreparedStatement始终返回1作为自动生成的键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！