Java JDBC How to - Use JDBC to select data from one table and insert into another table in different db








Question

We would like to know how to use JDBC to select data from one table and insert into another table in different db.

Answer

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
//from  w  w w  . j a  v a 2s . c  o m
public class Main {
  public static void main(String[] argv) throws Exception {
    Connection con = DriverManager.getConnection(
        "jdbc:postgresql://localhost:5432/old", "user", "pass");
    Connection con1 = DriverManager.getConnection(
        "jdbc:postgresql://localhost:5432/new", "user", "pass");

    String sql = "INSERT INTO users(" + "name," + "active," + "login,"
        + "password)" + "VALUES(?,?,?,?)";

    Statement statement = con.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

    PreparedStatement pstmt = con1.prepareStatement(sql);

    ResultSet rs = statement.executeQuery("SELECT * FROM users");
    while (rs.next()) {
      String nm = rs.getString(2);
      Boolean ac = rs.getBoolean(3);
      String log = rs.getString(4);
      String pass = rs.getString(5);
      pstmt.setString(1, nm);
      pstmt.setBoolean(2, ac);
      pstmt.setString(3, log);
      pstmt.setString(4, pass);
      pstmt.executeUpdate();
    }
    con.close();
    con1.close();
  }
}