JDBC:在同一事务中创建的PK上的外键 [英] JDBC: foreign key on PK created in same transaction
问题描述
我的MySQL数据库中有两个表,它们是这样创建的:
I have two tables in my MySQL database, which were created like this:
CREATE TABLE table1 (
id int auto_increment,
name varchar(10),
primary key(id)
) engine=innodb
和
CREATE TABLE table2 (
id_fk int,
stuff varchar(30),
CONSTRAINT fk_id FOREIGN KEY(id_fk) REFERENCES table1(id) ON DELETE CASCADE
) engine=innodb
(这些不是原始表。重点是table2有一个引用表1中主键的外键)
(These are not the original tables. The point is that table2 has a foreign key referencing the primary key in table 1)
现在在我的代码中,我想在一个事务中向两个表添加条目。所以我将 autoCommit
设置为false:
Now in my code, I would like to add entries to both of the tables within one transaction. So I set autoCommit
to false:
Connection c = null;
PreparedStatement insertTable1 = null;
PreparedStatement insertTable2 = null;
try {
// dataSource was retreived via JNDI
c = dataSource.getConnection();
c.setAutoCommit(false);
// will return the created primary key
insertTable1 = c.prepareStatement("INSERT INTO table1(name) VALUES(?)",Statement.RETURN_GENERATED_KEYS);
insertTable2 = c.prepareStatement("INSERT INTO table2 VALUES(?,?)");
insertTable1.setString(1,"hage");
int hageId = insertTable1.executeUpdate();
insertTable2.setInt(1,hageId);
insertTable2.setString(2,"bla bla bla");
insertTable2.executeUpdate();
// commit
c.commit();
} catch(SQLException e) {
c.rollback();
} finally {
// close stuff
}
我执行上面的代码,我得到一个例外:
When I execute the code above, I get an Exception:
MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails
在提交之前,似乎主键在事务中不可用。
It seems like the primary key is not available in the transaction before I commit.
我在这里遗漏了什么吗?我真的认为生成的主键应该在事务中可用。
Am I missing something here? I really think the generated primary key should be available in the transaction.
程序使用mysql-connector 5.1.14和MySQL 5.5.8在Glassfish 3.0.1上运行
The program runs on a Glassfish 3.0.1 using mysql-connector 5.1.14 and MySQL 5.5.8
非常感谢任何帮助!
问候,
hage
Regards, hage
推荐答案
您错过了返回更新ID的内容,您必须这样做:
You missed something for the returned updated id , you have to do like this :
Long hageId = null;
try {
result = insertTable1.executeUpdate();
} catch (Throwable e) {
...
}
ResultSet rs = null;
try {
rs = insertTable1.getGeneratedKeys();
if (rs.next()) {
hageId = rs.getLong(1);
}
...
这篇关于JDBC:在同一事务中创建的PK上的外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!