Java 如何将表从一个数据库复制到另一个数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22624711/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to copy table from one database to another?
提问by Borat Sagddiev
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 。这是我的代码
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 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, csvetc.) and the insert
ResultSet 是一个现成的 java 对象。我希望有一种方法将它添加到批次或这样的事情和executeUpdate，但不写结果集到一些临时空间（List，csv等等），并插入
回答by Wyzard
The simplest way to do this is with a prepared statementfor 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 table2as you iterate through the results from table1, so there's no need to store the whole result set.
table2当您遍历来自 的结果时table1，行会被插入，因此无需存储整个结果集。
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 将所有挂起的插入保存在本地内存中，这似乎是您试图避免的。因此，在这种情况下，一次一行插入是您最好的选择。
回答by Alex R
If you don't want to manually list out all the field names for every table in the database, you should be able to do this two-step process instead:
如果您不想手动列出数据库中每个表的所有字段名称，您应该可以执行以下两步过程：
- Copy the table schema using the answer in this question.
- Use
resultSet.getMetaData()to get the list of fields, and use that to drive a modified version of the SELECT/INSERT code in @Wyzard's answer.
- 使用此问题中的答案复制表架构。
- 使用
resultSet.getMetaData()获得的字段列表，并用它来在@ Wyzard的回答带动SELECT /插入代码的修改版本。
I will post code here if I get it working.
如果我让它工作，我会在这里发布代码。
