Insert row from result set into another database -Jdbc

Question

Using jdbc I execute the request on one server and get resultSet1. Now I have created a table according to resultSet1 over another server (Server no: 2). After that, I want to insert ResultSet1 directly into the table created on server 2. What is the best way to do this? like me, are there any functions resultSet.insertRowInto () (a generic answer that does not use exact table data)?

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 need the resultSet to be also copied to table1 in db2.

Answer 1

JDBC-based solution using Java 8:

 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 are not using Java 8:

Then you cannot use columns.stream() , etc. and lambda expressions. Here's an alternative way to create an 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 + ")" 

Denial of responsibility:

I use string concatenation to generate the SQL statements above. Be VERY careful with this method to prevent SQL injection (and syntax errors) from starting! The table parameter MUST NOT be entered by the user!

Assumptions made for simplicity:

• Column names are not case sensitive.
• You do not have an excessive amount of data (otherwise, you should commit from time to time so that the UNDO / REDO logs are small)
• The JDBC driver supports setObject() and getObject() instead of the more specific types that may be required.
• You should use JDBC because any library that supports some write abstraction (like Hibernate , jOOQ , ActiveJDBC , etc.) will help a lot here.