如何将表从一个数据库复制到另一个数据库？

I need to take a table from one database and upload it to a different database. So, I create two separate connection . Here is my code

我需要从一个数据库中取一个表并将其上传到另一个数据库。所以，我创建了两个单独的连接。这是我的代码

Connection connection1 = // set up connection to dbms1
Statement statement = connection1.createStatement();
ResultSet result = statement.executeQuery("select * from ............. "); 

Connection connection2 =  // set up connection to dbms2
// Now I want to upload the ResultSet result into the second database
Statement statement2 = connection2.createStatement("insert into table2 " + result);
statement2.executeUpdate();

The above last lines do not work How can i do this ? The bottomline is how to reuse a ready resultset

以上最后一行不起作用我该怎么做？底线是如何重用ready结果集

ResultSet is a ready java object . I hope there is a way add it to batch or something like this and executeUpdate , but not to write the result set to some temporary space (List, csv etc.) and the insert

ResultSet是一个现成的java对象。我希望有一种方法可以将它添加到批处理或类似的东西和executeUpdate，但不能将结果集写入某个临时空间（List，csv等）和插入

1 个解决方案

#1

The simplest way to do this is with a prepared statement for the insert. It lets you create a single statement object that can be used to run the query multiple times with different parameter values.

最简单的方法是使用插入的预准备语句。它允许您创建单个语句对象，该对象可用于使用不同的参数值多次运行查询。

try (final Statement statement1 = connection1.createStatement();
     final PreparedStatement insertStatement = 
     connection2.prepareStatement("insert into table2 values(?, ?)"))
{
    try (final ResultSet resultSet =
         statement1.executeQuery("select foo, bar from table1"))
    {
        while (resultSet.next())
        {
            // Get the values from the table1 record
            final String foo = resultSet.getString("foo");
            final int bar = resultSet.getInt("bar");

            // Insert a row with these values into table2
            insertStatement.clearParameters();
            insertStatement.setString(1, foo);
            insertStatement.setInt(2, bar);
            insertStatement.executeUpdate();
        }
    }
}

The rows are inserted into table2 as you iterate through the results from table1, so there's no need to store the whole result set.

当您遍历table1的结果时，行将插入到table2中，因此无需存储整个结果集。

You can also use the prepared statement's addBatch() and executeBatch() methods to queue up all the inserts and send them to the database all at once, instead of sending a separate message to the database for each individual inserted row. But that forces JDBC to hold all the pending inserts in memory locally, which it seems you're trying to avoid. So the one-row-at-a-time inserts are your best bet in this case.

您还可以使用预准备语句的addBatch（）和executeBatch（）方法对所有插入进行排队，并将它们一次性发送到数据库，而不是为每个插入的行向数据库发送单独的消息。但这迫使JDBC在本地保存所有挂起的内存，这似乎是你试图避免的。因此，在这种情况下，一次一行插入是最好的选择。

#1