JDBC：在同一事务中创建的 PK 上的外键

发布于 2024-11-09 02:11:16 字数 1672 浏览 0 评论 0原文

我的 MySQL 数据库中有两个表，它们是这样创建的：

CREATE TABLE table1 (
  id int auto_increment,
  name varchar(10),
  primary key(id)
) engine=innodb

and

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 中的主键）

现在在我的代码中，我想将条目添加到一个事务中的两个表中。所以我将 autoCommit 设置为 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
   }

当我执行上面的代码时，我得到一个异常：

MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails

在我提交之前，主键似乎在事务中不可用。

我在这里错过了什么吗？我真的认为生成的主键应该在事务中可用。

该程序使用 mysql-connector 5.1.14 和 MySQL 5.5.8 在 Glassfish 3.0.1 上运行

非常感谢任何帮助！

问候，哈格

原文

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

and

CREATE TABLE table2 (
  id_fk int,
  stuff varchar(30),
  CONSTRAINT fk_id FOREIGN KEY(id_fk) REFERENCES table1(id) ON DELETE CASCADE
) engine=innodb

(These are not the original tables. The point is that table2 has a foreign key referencing the primary key in table 1)

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.

The program runs on a Glassfish 3.0.1 using mysql-connector 5.1.14 and MySQL 5.5.8

Any help is really appreciated!

Regards,
hage

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

街道布景 2024-11-16 02:11:16

您错过了返回的更新 id 的某些内容，您必须这样做：

Long hageId = null;

try {
    result = insertTable1.executeUpdate();
} catch (Throwable e) {
    ...
}

ResultSet rs = null;

try {
    rs = insertTable1.getGeneratedKeys();
    if (rs.next()) {
        hageId = rs.getLong(1);
    }
 ...

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);
    }
 ...

回复收藏 0 原文