dblink 在java中查询,作为一个dblink事务执行多个查询

I am working in a Java application where I need to execute these the two queries (as Strings in java) at the same time and rollback the transaction if there where errors.

SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2',

'INSERT INTO table3(field4)

VALUES (5)') AS result;

SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2',

'UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436') AS result;

UPDATE

I created one String with the two queries separated with ; as in the comments suggest

UPDATE

I have tried JDBC atomic transactions as the code in java. I force the second sql fail but even if I am specifying .setAutoCommit(false); the dblink affected the other database with the first query. I tried the same code with NO dblink transactions and the rollback works well. dblink is the problem.

Java UPDATE

public static boolean ejecutarTransaccionDblink(String sql) {

boolean estado = false;

try {

Statement sentencia = conexion.createStatement();

conexion.setAutoCommit(false);

if (sql.length() != 0) {

if (sentencia.execute(sql)) {

conexion.commit();

estado = true;

}

}

} catch (SQLException ex) {

System.out.println(ex.toString());

try {

estado = false;

conexion.rollback();

} catch (SQLException ex1) {

}

} finally {

try {

conexion.setAutoCommit(true);

return estado;

} catch (SQLException ex) {

return estado;

}

}

}

Thanks for your help.

解决方案

In order to run the queries in a transaction, you simply need to set the auto-commit feature to false on the connection (remembering to set it back to true when you're done, especially if the connection is retrieved from a connection pool - and therefore reused).

The code is relatively simple:

ResultSet resultado = null;

String statement1 = "SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2','INSERT INTO table3(field4) VALUES (5)') AS result";

String statement2 = "SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2','UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436') AS result";

try {

// set auto-commit to false, to indicate start of transaction

conexion.setAutoCommit(false);

// run whatever queries you want on the connection, in a transaction, e.g. :

Statement sentencia = conexion.createStatement();

resultado = sentencia.executeQuery(sql);

//manually commit the transaction when you're done

conexion.commit();

return resultado;

} catch (SQLException ex) {

System.out.println("Error Consulta:" + ex);

// ensure transaction is rolled-back in case of error. (note: you might want to add an NPE check here

con.rollback();

return null;

} finally {

// close any statements / preparedStatements, etc. Note you MUST do this in the finally block, to ensure your connection won't stay in transaction.

con.setAutoCommit(true);

}

Hope that helps

UPDATE

As @a_horse_with_no_name pointed out, dblink_exec connects to a remote db, so the above is not complete, as it only handles transactions in the first db.

I believe the answer should lie with using named connections with dblink_exec where the process involves:

opening a new connection with dblink_connect

starting a transaction in new named connection with dblink_exec

executing query 1 with dblink_exec in previously opened connection

executing query 2 with dblink_exec in previously opened connection

committing transaction in previously opened connection

Therefore, the code would look like this:

SELECT dblink_connect('myconn','hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2');

SELECT dblink_exec('myconn','BEGIN');

SELECT dblink_exec('myconn', 'INSERT INTO table3(field4) VALUES (5)');

SELECT dblink_exec('myconn', 'UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436');

SELECT dblink_exec('myconn','COMMIT');

The thing is, this is all untested, so @KazMiller could you please give this a try?