如何将表从一个数据库复制到另一个数据库? [英] How to copy table from one database to another?

问题描述

我需要从一个数据库中获取一张表并将其上传到另一个数据库.因此，我创建了两个单独的connection.这是我的代码

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

ResultSet是一个就绪的java对象.我希望有一种方法可以将其添加到批处理或类似的代码中，然后将它们添加到 executeUpdate ，，但不要将结果集写入某些临时空间( List ， csv 等)和插入

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

推荐答案

最简单的方法是使用

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();
        }
    }
}

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

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.

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

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.

这篇关于如何将表从一个数据库复制到另一个数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！