Insert Row from resultSet to different database -Jdbc

Question

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)?

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.

Answer 1

A JDBC-based solution that uses 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're not using Java 8:

Then cannot use columns.stream(), etc. and lambda expressions. Here's an alternative way to create the INSERT statement:

   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 VERY careful with this technique to prevent running into SQL injection (and syntax errors)! The table parameter MUST NOT be user input!

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() and getObject(), 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.