java 将结果集中的行插入到不同的数据库 -Jdbc
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29720371/
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
Insert Row from resultSet to different database -Jdbc
提问by Vishnu
Using jdbc I am executing a query over one server and obtain the resultSet1. Now, I created a table according to the resultSet1 over another server (Server no :2). After this, I want to insert the ResultSet1 directly into the table created at Server 2 . What is the best way to do this ? like I just , are there any resultSet.insertRowInto() kind of functions (generalised answer that don't use the exact table data)?
使用 jdbc，我在一台服务器上执行查询并获取 resultSet1。现在，我根据另一台服务器（服务器编号：2）上的 resultSet1 创建了一个表。在此之后，我想将 ResultSet1 直接插入在 Server 2 上创建的表中。做这个的最好方式是什么 ？就像我一样，是否有任何 resultSet.insertRowInto() 类型的函数（不使用确切表数据的通用答案）？
Connection connection1, connection2;
connection1 = connectDB("192.168.40.1","db1","root","");
connection2 = connectDB("192.168.45.1","db2","root","");
//I have table1 in db1 and db2 and their structure is same
stmt = connection1.createStatement();
ResultSet = stmt.executeQuery("Select * from table1");
Now I require the resultSet to be copied to table1 in db2 also.
现在我还需要将 resultSet 复制到 db2 中的 table1。
回答by Lukas Eder
A JDBC-based solution that uses Java 8:
使用 Java 8 的基于 JDBC 的解决方案：
public void copy(String table, Connection from, Connection to) throws SQLException {
try (PreparedStatement s1 = from.prepareStatement("select * from " + table);
ResultSet rs = s1.executeQuery()) {
ResultSetMetaData meta = rs.getMetaData();
List<String> columns = new ArrayList<>();
for (int i = 1; i <= meta.getColumnCount(); i++)
columns.add(meta.getColumnName(i));
try (PreparedStatement s2 = to.prepareStatement(
"INSERT INTO " + table + " ("
+ columns.stream().collect(Collectors.joining(", "))
+ ") VALUES ("
+ columns.stream().map(c -> "?").collect(Collectors.joining(", "))
+ ")"
)) {
while (rs.next()) {
for (int i = 1; i <= meta.getColumnCount(); i++)
s2.setObject(i, rs.getObject(i));
s2.addBatch();
}
s2.executeBatch();
}
}
}
If you're not using Java 8:
如果您没有使用 Java 8：
Then cannot use columns.stream(), etc. and lambda expressions. Here's an alternative way to create the INSERTstatement:
然后不能使用columns.stream(), etc. 和 lambda 表达式。这是创建INSERT语句的另一种方法：
StringBuilder columnNames = new StringBuilder();
StringBuilder bindVariables = new StringBuilder();
for (int i = 1; i <= meta.getColumnCount(); i++)
if (i > 1) {
columnNames.append(", ");
bindVariables.append(", ");
}
columnNames.append(meta.getColumnName(i));
bindVariables.append('?');
}
String sql = "INSERT INTO " + table + " ("
+ columnNames
+ ") VALUES ("
+ bindVariables
+ ")"
Disclaimer:
免责声明：
I'm using string concatenation to generate SQL statements above. Be VERYcareful with this technique to prevent running into SQL injection(and syntax errors)! The tableparameter MUST NOTbe user input!
我正在使用字符串连接来生成上面的 SQL 语句。使用这种技术要非常小心，以防止遇到SQL 注入（和语法错误）！该table参数必须不能是用户输入！
Assumptions made for simplicity's sake:
为简单起见做出的假设：
- Column names are case-insensitive
- You don't have an excessive amount of data (otherwise you should commit from time to time to keep UNDO / REDO logs small)
- Your JDBC driver will support
setObject()andgetObject(), instead of the more concrete types, which might be necessary. - You must use JDBC, because any library that supports some sort of record abstraction (e.g. Hibernate, jOOQ, ActiveJDBC, etc.) would greatly help here.
- 列名不区分大小写
- 您没有过多的数据（否则您应该不时提交以保持 UNDO / REDO 日志较小）
- 您的 JDBC 驱动程序将支持
setObject()和getObject()，而不是更具体的类型，这可能是必要的。 - 您必须使用 JDBC，因为任何支持某种记录抽象（例如Hibernate、jOOQ、ActiveJDBC等）的库在这里都会有很大帮助。
