How to create two different database connections?

Question

I need to pass data from one database (DB2) to the other (Mysql), and I’m thinking about how to do this, as in the database I have the data I do not have access to some tools, how to do the backup, I can only do SELECT, I thought of making two connections with the JDBC, take the records and already insert them in Mysql, you have how? Or better I save in a txt and then switch to Mysql?

Answer 1

You can open two connections with different databases and then insert the read data from one database into the other more or less like this:

String urlMySql = "jdbc:mysql://localhost:3306/seu_database_mysql";
String urlDb2 = "jdbc:db2://localhost:50000/seu_database_db2";

Connection connMySql = DriverManager.getConnection(urlMySql);
Connection connDb2 = DriverManager.getConnection(urlDb2);

PreparedStatement selectDb2 = connDb2.prepareStatement("SELECT * FROM TABELA");
ResultSet rsDb2 = selectDb2.executeQuery();

while (rsDb2.next()) {
    PreparedStatement insertMySql = connMySql.prepareStatement("INSERT INTO OUTRA_TABELA VALUES...");
    insertMySql.setXXX(rsDb2.getXXX(...));
    insertMySql.executeUpdate();
}

Hence, all you have to do is manage the Connections, PreparedStatements and ResultSets as usual (including by closing them in a block finally or using the try-with-resources).

It is also valid to encapsulate Connections in Daos, put connections in pools or separate any database operation into multiple classes and/or multiple methods. Just keep in mind that there may be more than one connection to the active database at the same time (along with their PreparedStatements and ResultSets).

Answer 2

You can’t have a single con referencing two database connections. You definitely need to keep two different references.

Connection con1 = DriverManger.getConnection( connect1 );  
Connection con2 = DriverManger.getConnection( connect2 );  

There is no alternative.

Solution 1: Create multiple methods to return local and remote connection.

public Connection getLocalConnection() {
  ...
  Connection localCon = DriverManger.getConnection( connect1 );  
  ...
  return localCon;
}

public Connection getRemoteConnection() {
  ...
  Connection remoteCon = DriverManger.getConnection( connect2 );  
  ...
  return remoteCon;
}

Solution 2 : If you want to generate as much connection on each call and return them, you’d better use a list object to go back:

public List<Connection> createConnection() {
  ...
  Connection localCon = DriverManger.getConnection( connect1 );  
  Connection remoteCon = DriverManger.getConnection( connect2 );  
  ...
  List<Connection> connectionsList = new ArrayList<Connection>( 2 );
  connectionsList.add( localCon );
  connectionsList.add( remoteCon );
  ...
  return connectionsList;
}

I prefer to use Solution 1, because sometimes you might be checking some data in a single database.