Opening connections to the two mysql databases at the same time

I have a java JDBC program,which opens connection to mysql database and do some operations.

I want to connect to two databases, one in local machine and another in remote machine.

I am able to connect one at a time, but I want to open both the connections at the same time, at one shot.

This is my java program:

public class DatabaseOperations {

    Connection con = null;
    String driver = "com.mysql.jdbc.Driver";
    String user = "root";
    String password = "root";
    //tring url = "jdbc:mysql://192.168.2.26:3306/time_entries_test";
    String dbName = "time_entries_test";
    String connect1 = "jdbc:mysql://192.168.2.26:3306/" + dbName + "?user=" + user + "&password=" + password + "&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=10";
    String connect2 = "jdbc:mysql://localhost:3306/" + dbName + "?user=" + user + "&password=" + password + "&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=10";

    public Connection createConnection() {
        try {
            Class.forName(driver);
            //con = DriverManager.getConnection(connect2);
            con = DriverManager.getConnection(connect2);
        } catch (ClassNotFoundException cnfe) {
            cnfe.printStackTrace();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return con;
    }
}

Here connect1 and connect2 are two url strings to which I want to open connections.

Answers


You can't have single con reference to two database connections. You definitely need to maintain two different references.

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

No alternative.


Update 1:

how do i return both con1 and con2,so that i can use both there in my method to do operations??

Solution 1: Create multiple methods that returns local and remote connections.

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 both connection on every call and return them, you better use a list object to return..

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 I am not sure every time you will be requiring both connections. At times you may be checking some data in only one database.


Need Your Help

Soundcloud API PHP Integration

php api soundcloud

I am currently trying to stream tracks from soundcloud to my new web app. I currently am able to get the album cover but now I am need to get the user name, track title, and permalink to also show up

if else route on ajax data not working

php javascript jquery ajax

In the javascript below. the like_add function takes the else route. intead of taking the if route. even though it returns success. so my question is why? the data returns success but it doesnt mov...