继续使用Java中的异常处理Postgres事务 [英] Continue Postgres transaction with exceptions in Java

问题描述

下面的插入方法插入数据库（PostgreSQL）表中的链接，但如果发生错误，其余的事务将受到影响，并且不起作用。例外是因为字段URL是唯一的。

PostgreSQL是否可以继续交易，即使有异常？

< （int i = 0，n = page.getLinks（）。length; i< n; i ++）{
linkBD.insert（page.getLink（i） ，idPage）;
}

public boolean insert（Link link，int idPage）{
try {
String sql =INSERT INTO link（id，idPage，url，linkText，
+visited，broken）VALUES（nextval（'idLinkSqc'），？，？，？，？，？）;

PreparedStatement pstm = Conn.conn.prepareStatement（sql）;

pstm.setInt（1，idPage）;
pstm.setString（2，link.getUrl（））;
pstm.setString（3，link.getLinkText（））;
pstm.setBoolean（4，false）;
pstm.setBoolean（5，false）;

pstm.execute（）;
Conn.commit（）;
pstm.close（）;
返回true;
} catch（Exception e）{
System.out.println（Erro insertionindo link no banco de dados：+ e.getMessage（））;
System.out.println（Erro no link：+ link.getUrl（））;
返回false;
}
}

错误讯息葡萄牙语：transaçãoatual foi interrompida，comandos ignoradosatéo fim do bloco detransação

Google翻译翻译，我想是正确的： 当前交易被中止，命令被忽略，直到事务块结束

解决方案

如果失败在 SAVEPOINT 内。以下是psql中的一个示例：

 ＃创建临时表foo（i int primary key）; 
 CREATE TABLE 
  

开始事务并插入一行：

 ＃begin; 
 BEGIN 
＃insert into foo values（1）; 
 INSERT 0 1 
  

启动一个保存点，插入同一行两次。这将导致错误：

 ＃savepoint bar; 
 SAVEPOINT 
＃insert into foo values（2）; 
 INSERT 0 1 
＃insert into foo values（2）; 
错误：重复键值违反唯一约束foo_pkey
  

回滚到保存点，然后插入另一行。

 ＃回滚到保存点栏; 
 ROLLBACK 
＃insert into foo values（3）; 
 INSERT 0 1 
  

提交并看看有什么？

 ＃commit; 
 COMMIT 
＃select * from foo; 
i 
 --- 
 1 
 3 
（2行）
  

The insert method below insert links on a table in the database (PostgreSQL), but if a error occurs, the rest of transaction is affected and dont works. The exception is because the field URL is unique.

It's possible in PostgreSQL continue the transaction even with the exceptions?

for (int i = 0, n = page.getLinks().length; i < n; i++) {
     linkBD.insert(page.getLink(i), idPage);
}            

public boolean insert(Link link, int idPage) {
    try {
        String sql = "INSERT INTO link (id,idPage,url,linkText,"
        + "visited,broken) VALUES(nextval('idLinkSqc'),?,?,?,?,?)";

        PreparedStatement pstm = Conn.conn.prepareStatement(sql);

        pstm.setInt(1, idPage);
        pstm.setString(2, link.getUrl());
        pstm.setString(3, link.getLinkText());
        pstm.setBoolean(4, false);
        pstm.setBoolean(5, false);

        pstm.execute();            
        Conn.commit();
        pstm.close();            
        return true;
    } catch (Exception e) {
        System.out.println("Erro inserindo link no banco de dados: " + e.getMessage());
        System.out.println("Erro no link: "+link.getUrl());
        return false;
    }
}

The error message in portuguese: transação atual foi interrompida, comandos ignorados até o fim do bloco de transação

The translation of Google Translate, I think is right: current transaction is aborted, commands ignored until end of transaction block

解决方案

It is possible to continue if the failure was inside a SAVEPOINT. Here's an example in psql:

# create temporary table foo (i int primary key);
CREATE TABLE

Begin a transaction and insert a row:

# begin;
BEGIN
# insert into foo values(1);
INSERT 0 1

Start a savepoint, the insert the same row twice. This will cause an error:

# savepoint bar;
SAVEPOINT
# insert into foo values(2);
INSERT 0 1
# insert into foo values(2);
ERROR:  duplicate key value violates unique constraint "foo_pkey"

Roll back to the savepoint, then insert another row.

# rollback to savepoint bar;
ROLLBACK
# insert into foo values(3);
INSERT 0 1

Commit and see what's there:

# commit;
COMMIT
# select * from foo;
 i 
---
 1
 3
(2 rows)

这篇关于继续使用Java中的异常处理Postgres事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！