我正在编写一个程序，该程序应该使用JDBC程序对DB表进行插入/更新。插入/更新需要发生在两个独立的数据库实例上。有一个由程序读取的输入XML文件，该文件有大约1800条记录。对于每个记录，若记录已经存在于DB中，我需要根据从XML文件中读取的记录进行更新。否则请插入。我使用下面的程序来做这项工作，当我执行程序时，它并没有给出一致的结果。看起来if and else部分中的PreparedStatement for DB2实际上指向DB1，因为在成功插入/更新第一条记录的DB1之后，它无法对DB2执行同样的操作，即主键冲突，但DB2是一个不同的连接，我在那里没有记录，所以DB2的主键冲突没有意义！似乎连接信息在某种程度上被混淆了。下面是程序的外观。我只给出了程序的相关部分。PreparedStatment2和PreparedStatement4未给出预期结果。请注意，我已经验证了connection2，它确实指向DB2，而不是DB1。奇怪的是，每次我执行程序时，它都会开始为上次执行时不起作用的记录工作，所以每次执行程序都会让我克服循环中1条记录的问题，并开始为下一条记录提供问题！有人能帮助我理解为什么PreparedStatement2和4不能像预期的那样为DB2工作吗？
谢谢，Chanchal
Connection connection1 = null;
Connection connection2 = null;
Statement statement = null;
PreparedStatement preparedStatement1 = null;
PreparedStatement preparedStatement2 = null;
PreparedStatement preparedStatement3 = null;
PreparedStatement preparedStatement4 = null;
ResultSet resultSet1 = null;
try
{
connection1 = DriverManager.getConnection(properties.getProperty(RT_DB1_URL), properties.getProperty(RT_DB1_USER), properties.getProperty(RT_DB1_PWD));
connection2 = DriverManager.getConnection(properties.getProperty(RT_DB2_URL), properties.getProperty(RT_DB2_USER), properties.getProperty(RT_DB2_PWD));
//Get list
for (int temp = 0; temp < list.getLength(); temp++) {
//Get fieldValueA,fieldValueB, fieldValueC, fieldValueD values from a XML file
//Find if record exists in DB
String selectQuery = "select * from table_x where fieldA='" + fieldValueA + "'";
resultSet1 = statement.executeQuery(selectQuery);
if(resultSet1.next()){
// Update table
String query = "update table_x set fieldB = ?,fieldC = ?,fieldD = ? where fieldA = ?";
preparedStatement1 = connection1.prepareStatement(query);
preparedStatement2 = connection2.prepareStatement(query);
preparedStatement1.setString(1, fieldValueB);
preparedStatement1.setString(2, fieldValueC);
preparedStatement1.setString(3, fieldValueD);
preparedStatement1.setString(4, fieldValueA);
int k = preparedStatement1.executeUpdate();
logger.info("Records successfully updated in RT DB1 for fieldA=" + fieldValueA);
//Closing the statement as it exceeds oracle cursor limit otherwise with default cursor limit of 300
if (preparedStatement1 != null) preparedStatement1.close();
preparedStatement2.setString(1, fieldValueB);
preparedStatement2.setString(2, fieldValueC);
preparedStatement2.setString(3, fieldValueD);
preparedStatement2.setString(4, fieldValueA);
int k = preparedStatement2.executeUpdate();
logger.info("Records successfully updated in RT DB2 for fieldA=" + fieldValueA);
//Closing the statement as it exceeds oracle cursor limit otherwise with default cursor limit of 300
if (preparedStatement2 != null) preparedStatement2.close();
}
else{
//Insert to the table
String queryInsert = "insert into table_x(fieldA,fieldB,fieldC,fieldD)";
queryInsert = queryInsert + " values(?,?,?,?)";
preparedStatement3 = connection1.prepareStatement(queryInsert);
preparedStatement4 = connection2.prepareStatement(queryInsert);
preparedStatement3.setString(1, fieldValueA);
preparedStatement3.setString(2, fieldValueB);
preparedStatement3.setString(3, fieldValueC);
preparedStatement3.setString(4, fieldValueD);
int k = preparedStatement3.executeUpdate();
logger.info("Records successfully inserted in RT DB1 for fieldA=" + fieldValueA);
if (preparedStatement3 != null) preparedStatement3.close();
preparedStatement4.setString(1, fieldValueA);
preparedStatement4.setString(2, fieldValueB);
preparedStatement4.setString(3, fieldValueC);
preparedStatement4.setString(4, fieldValueD);
int k = preparedStatement4.executeUpdate();
logger.info("Records successfully inserted in RT DB2 for fieldA=" + fieldValueA);
if (preparedStatement4 != null) preparedStatement4.close();
} // End loop
} catch (Exception e) {
e.printStackTrace();
}
finally{
try { if (resultSet1 != null) resultSet1.close(); } catch (Exception e) {e.printStackTrace();};
try { if (statement != null) statement.close(); } catch (Exception e) {e.printStackTrace();};
try { if (connection1 != null) connection1.close(); } catch (Exception e) {e.printStackTrace();};
try { if (connection2 != null) connection2.close(); } catch (Exception e) {e.printStackTrace();};
}
在循环之前创建准备好的语句是可以的。因此，它们可以在每个循环中重复使用。此外，您还可以进行批处理，并在运行完所有数据后将所有数据发送到DB。如果有大约1000条记录，那么提前刷新批次可能是有意义的，但一次可以在一个批次内处理1800条记录。我无法测试这个代码，但我希望它能给你一些提示。
Connection connection1 = null;
Connection connection2 = null;
Statement statement = null;
PreparedStatement preparedStatement1 = null;
PreparedStatement preparedStatement2 = null;
PreparedStatement preparedStatement3 = null;
PreparedStatement preparedStatement4 = null;
ResultSet resultSet1 = null;
try
{
connection1 = DriverManager.getConnection(properties.getProperty(RT_DB1_URL), properties.getProperty(RT_DB1_USER), properties.getProperty(RT_DB1_PWD));
connection2 = DriverManager.getConnection(properties.getProperty(RT_DB2_URL), properties.getProperty(RT_DB2_USER), properties.getProperty(RT_DB2_PWD));
// Update table
String query = "update table_x set fieldB = ?,fieldC = ?,fieldD = ? where fieldA = ?";
preparedStatement1 = connection1.prepareStatement(query);
preparedStatement2 = connection2.prepareStatement(query);
String queryInsert = "insert into table_x(fieldA,fieldB,fieldC,fieldD)";
queryInsert = queryInsert + " values(?,?,?,?)";
preparedStatement3 = connection1.prepareStatement(queryInsert);
preparedStatement4 = connection2.prepareStatement(queryInsert);
//Get list
for (int temp = 0; temp < list.getLength(); temp++) {
//Get fieldValueA,fieldValueB, fieldValueC, fieldValueD values from a XML file
//Find if record exists in DB
String selectQuery = "select * from table_x where fieldA='" + fieldValueA + "'";
resultSet1 = statement.executeQuery(selectQuery);
if(resultSet1.next()){
preparedStatement1.setString(1, fieldValueB);
preparedStatement1.setString(2, fieldValueC);
preparedStatement1.setString(3, fieldValueD);
preparedStatement1.setString(4, fieldValueA);
preparedStatement1.addBatch();
logger.info("Records successfully added RT DB1 for fieldA=" + fieldValueA);
preparedStatement2.setString(1, fieldValueB);
preparedStatement2.setString(2, fieldValueC);
preparedStatement2.setString(3, fieldValueD);
preparedStatement2.setString(4, fieldValueA);
preparedStatement2.addBatch();
logger.info("Records successfully added in RT DB2 for fieldA=" + fieldValueA);
}
else{
//Insert to the table
preparedStatement3.setString(1, fieldValueA);
preparedStatement3.setString(2, fieldValueB);
preparedStatement3.setString(3, fieldValueC);
preparedStatement3.setString(4, fieldValueD);
preparedStatement3.addBatch();
logger.info("Records successfully added RT DB1 for fieldA=" + fieldValueA);
if (preparedStatement3 != null) preparedStatement3.close();
preparedStatement4.setString(1, fieldValueA);
preparedStatement4.setString(2, fieldValueB);
preparedStatement4.setString(3, fieldValueC);
preparedStatement4.setString(4, fieldValueD);
preparedStatement4.addBatch();
} // End loop
// now, we are flushing the batch to the DBs
int[] k1 = preparedStatement1.executeBatch();
int[] k2 = preparedStatement2.executeBatch();
int[] k3 = preparedStatement3.executeBatch();
int[] k4 = preparedStatement4.executeBatch();
logger.info("updated " + String.format("%,d",k1.lenght) + " Records in DB1 and " + String.format("%,d",k2.lenght) + " inserted " + String.format("%,d",k3.length) + " Records in DB1 and " + String.format("%,d",k4.length) + " in DB2");
} catch (Exception e) {
e.printStackTrace();
}
finally{
try { if preparedStatement1 != null ) preparedStatement1 } catch (Exception e) {e.printStackTrace();};
try { if preparedStatement2 != null ) preparedStatement2 } catch (Exception e) {e.printStackTrace();};
try { if preparedStatement3 != null ) preparedStatement3 } catch (Exception e) {e.printStackTrace();};
try { if preparedStatement4 != null ) preparedStatement4 } catch (Exception e) {e.printStackTrace();};
try { if (resultSet1 != null) resultSet1.close(); } catch (Exception e) {e.printStackTrace();};
try { if (statement != null) statement.close(); } catch (Exception e) {e.printStackTrace();};
try { if (connection1 != null) connection1.close(); } catch (Exception e) {e.printStackTrace();};
try { if (connection2 != null) connection2.close(); } catch (Exception e) {e.printStackTrace();};
}
)