Java Oracle 事务

发布于 2024-12-05 16:04:06 字数 652 浏览 1 评论 0原文

我想将数据插入数据库到不同的表中。由于限制，我必须按特定顺序执行此操作。这意味着，首先插入表 a，然后插入表 b，然后插入表 c，...，并且不会混合表。但我正在编写一个程序，它获取多个 csv 文件并应将它们导入数据库，但程序不知道什么是正确的顺序。所以我认为事务是正确的方式，因为我听说数据一致性必须只存在于事务结束时。但这不起作用

我的代码看起来像这样：

Connection connection = DriverManager.getConnection(url, user, pw);
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
statement.addBatch("INSERT INTO c ....");
statement.addBatch("INSERT INTO a ....");
statement.addBatch("INSERT INTO b ....");
statement.addBatch("INSERT INTO a ....");
// ...
statement.executeBatch();
statement.close();
connection.commit();

但我会得到 ORA-02291（完整性约束违规）:-(

原文

I want to insert data into a database into different tables. Because of constraints I have to do this in a specific order. That means, first insert into table a, then b, then c , .... and no mixing of the tables.
But I'm writing a program, which get multiple csv files and should import them into the database, but the program can't know what is the right order. So I thought transaction would be the right way, because I heard, that the data consistency must only exists at the end of the transaction. But that doesn't works

My code looks like that:

Connection connection = DriverManager.getConnection(url, user, pw);
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
statement.addBatch("INSERT INTO c ....");
statement.addBatch("INSERT INTO a ....");
statement.addBatch("INSERT INTO b ....");
statement.addBatch("INSERT INTO a ....");
// ...
statement.executeBatch();
statement.close();
connection.commit();

But I will get ORA-02291 (integrity constraint violation) :-(

分享到QQ

分享到微博

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

发布评论

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

悲欢浪云 2024-12-12 16:04:06

您需要将约束设置为 deferable ，这样在提交之前不会对其进行检查。这里有一篇关于它的好文章

> drop table c
table C dropped.
> drop table p
table P dropped.
> create table p (id number primary key)
table P created.
> create table c (id number primary key, p_id number)
table C created.
> alter table c add constraint pk_p foreign key (p_id) references p (id) deferrable
table C altered.
> insert into c values ( 1, 1 )
1 rows inserted.
> insert into p values ( 1 )
1 rows inserted.
> commit
commited.

You need to make the constraint deferrable , this way it is not checked until the commit. There is a good article about it here

> drop table c
table C dropped.
> drop table p
table P dropped.
> create table p (id number primary key)
table P created.
> create table c (id number primary key, p_id number)
table C created.
> alter table c add constraint pk_p foreign key (p_id) references p (id) deferrable
table C altered.
> insert into c values ( 1, 1 )
1 rows inserted.
> insert into p values ( 1 )
1 rows inserted.
> commit
commited.

回复收藏 0 原文