Single thread writing to different database with different connection parameters

Question

I am working on a project in which I have three tables in a different database with different schemas. So that means I have three different connection parameters for those three tables to connect using JDBC-

Let's suppose-

For Table1-

Username:- A
Password:- B
URL:       C

Columns-
ID1         String
Account1    String

For Table2-

Username:- P
Password:- Q
URL:-      R

Columns-
ID2         String
Account2    String

For Table3-

Username:- T
Password:- U
URL:-      V

Columns-
ID3         String
Account3    String

And I am supposed to insert in all the three tables or any one of them using JDBC.

Below are the three use cases I have-

1. From the command prompt if suppose I am passing Table1 only, then I am suppose to insert only in Table1 columns by making connection to Table1.
2. And if I am passing Table1, Table2 from the command prompt then I am suppose to insert in both Table1 and Table2 columns by making connection to Table1 and Table2.
3. And if I am passing Table1, Table2 and Table3 then I am suppose to enter in all the three tables using there respective connection parameter

I am not able to understand how to write code for the above particular scenario in such a cleaner way so that it can be extended in near future as well if I come up with four tables. I can have a one constant file which can store the SQL that needs to be executed for any of the three tables and some other constant thing as well.

public static void main(String[] args) {


}


 class Task implements Runnable {

    private Connection dbConnection = null;
    private PreparedStatement preparedStatement = null;

    public Task() {

    }

    @Override
    public void run() {

    dbConnection = getDbConnection();

    //prepare the statement and execute it

    }
  }


    private Connection getDBConnection() {

    Connection dbConnection = null;

          Class.forName(Constants.DRIVER_NAME);
      dbConnection = DriverManager.getConnection( , , );

      return dbConnection;
    }

Can anyone provide some thoughts on this how should I proceed forward?

Note:-

Column in each table will differ a lot. Like in some tables, column can be 10 and in some other table, column can be 20.

Answer 1

Create databases.properties file with content like this:

# Table 1
table1.url: jdbc:mysql://localhost:3306/garden
table1.user: gardener
table1.password: shavel
table1.table: fruits
table1.column.id: fruitID
table1.column.color: fruitColor
table1.column.weight: fruitWeight
# ... More fruit columns here ...

# Table 2
table2.url: jdbc:mysql://otherhost:3306/forest
table2.user: forester
table2.password: axe
table2.table: trees
table2.column.id: treeID
table2.column.height: treeHeight
# ... More tree columns here ...

# ... More tables here ...

Then do something like this:

public static void main (String [] args)
{
    Properties databasesProperties = new Properties ();
    databasesProperties.load ("databases.properties");

    for (String arg: args)
    {
        String url = databasesProperties.get (arg + ".url");
        String user = databasesProperties.get (arg + ".user");
        String password= databasesProperties.get (arg + ".password");
        String table = databasesProperties.get (arg + ".table");

        String columnPrefix = arg + ".column."
        Map <String, String> columns = new HashMap <String, String> ();
        for (String key: databasesProperties.stringPropertyNames ())
        {
            if (key.startsWith (columnPrefix))
                columns.put (
                    key.substring (columnPrefix.length ()), 
                    databasesProperties.get (key));
        }

        doInsert (url, user, password, table, columns);
    }
}

Later you can always add more tables into your databases.properties file.

Answer 2

Save your Database properties in a class file DBPropery.java.

final class DBProperty
{
    static String[]  urls = {
                        "C",
                        "R",
                        "V"
                    }; //You can add more URLs here.
    static String[] driver= {
                        "Driver1",
                        "Driver2",
                        "Driver3"
                    };//You can add more drivers string
    static String[]  table = {
                        "Table1",
                        "Table2",
                        "Table3"
                    };//You can add more table names here According to URLs mentioned in urls array.
    static String[]  user = {
                        "A",
                        "P",
                        "T"
                    };//You can add more user names here according to URls mentioned in urls array.
    static String[]  pwd = {
                        "B",
                        "Q",
                        "U"
                    };//You can add more Password here according to URls mentioned in urls array.
    static String[] queries = {
                        "Query for Table1",
                        "Query for Table2",
                        "Query for Table3",
                    };//You can add more queries here for more tables according to URls mentioned in urls array.
    static int[]  columns ={
                        2,
                        2,
                        2
                    };//You can change the column numbers according to need . 0th index belongs to Table1 , 1 to table2....so on. 
                      //If you add more tables , add corresponding columns count to next index.
    static String[] columnValues ={
                                "1^John",
                                "34^Vicky",
                                "65^Ethen"
                            };//String at each index represents a row in corresponding table in table[] array. each column is seperated by delimiter "^".
}

Make all Changes in DBProperty.java file.
Then proceed with following class file

import java.sql.*;
import java.util.*;
class MultiTableInsert implements Runnable
{
    Map<String,Integer> columnsInTable;
    Map<String,String>  tableDriver;
    Map<String,String>  rowForTable;
    Map<String,String>  queryForTable;
    Map<String,String>  urlForTable;
    Map<String,String>  userForTable;
    Map<String,String>  pwdForTable;
    String[]                tables ;
    public MultiTableInsert(String... tables)//Loading all Database Settings here..
    {
        this.tables = tables;
        columnsInTable  = new LinkedHashMap<String,Integer>();
        rowForTable = new LinkedHashMap<String,String>();
        tableDriver = new LinkedHashMap<String,String>();
        urlForTable = new LinkedHashMap<String,String>();
        userForTable= new LinkedHashMap<String,String>();
        pwdForTable = new LinkedHashMap<String,String>(); 
        for (int i = 0 ; i < DBProperty.urls.length ; i++ )
        {
            try
            {
                tableDriver.put(DBProperty.table[i],DBProperty.driver[i]);
                queryForTable.put(DBProperty.table[i],DBProperty.queries[i]);
                columnsInTable.put(DBProperty.table[i],DBProperty.columns[i]);
                rowForTable.put(DBProperty.table[i],DBProperty.columnValues[i]);
                urlForTable.put(DBProperty.table[i],DBProperty.urls[i]);
                userForTable.put(DBProperty.table[i],DBProperty.user[i]);
                pwdForTable.put(DBProperty.table[i],DBProperty.pwd[i]);
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
            }
        }
    }
    @Override
    public void run()
    {
        insertIntoTable(tables);
    }
    private void insertIntoTable(String... tables)
    {
        for (String tble : tables )
        {
            Connection con = null;
            PreparedStatement pStmt = null;
            try
            {
                Class.forName(tableDriver.get(tble));
                con = DriverManager.getConnection(urlForTable.get(tble),userForTable.get(tble),pwdForTable.get(tble)); 
                pStmt = con.prepareStatement(queryForTable.get(tble));
                int columns = columnsInTable.get(tble);
                String sRow = rowForTable.get(tble);
                StringTokenizer tokenizer = new StringTokenizer(sRow,"^");
                for (int i = 0; i < columns ; i++)
                {
                    pStmt.setString(i+1,(String)tokenizer.nextElement());
                }
                pStmt.execute();
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
            }
            finally
            {
                try
                {
                    con.close();
                }catch (Exception ex){}
                try
                {
                    pStmt.close();
                }catch (Exception ex){}
            }
        }
    }
    public static void main(String[] args) 
    {
        int length = args.length;
        int THREAD_COUNTS = 10;//Number of threads you want to start.
        switch (length)
        {
            case 0: 
                    System.out.println("Usage: javac MultiTableInsert Table1/Table2/Table3 <Table1/Table2/Table3> <Table1/Table2/Table3>");
                    System.exit(0);
            case 1:
                    for (int i = 0 ; i < THREAD_COUNTS ; i++)
                    {
                        MultiTableInsert mti = new MultiTableInsert(args[0]);
                        Thread th = new Thread(mti,"Thread"+i);//Create New Thread
                        th.start();                             //Start Thread
                    }
                    break;
            case 2:
                    for (int i = 0 ; i < THREAD_COUNTS ; i++)
                    {
                        MultiTableInsert mti = new MultiTableInsert(args[0],args[1]);//Create New Thread 
                        Thread th = new Thread(mti,"Thread"+i);                      //Start Thread     
                        th.start();
                    }
                    break;
            default:
                    for (int i = 0 ; i < THREAD_COUNTS ; i++)
                    {
                        MultiTableInsert mti = new MultiTableInsert(args[0],args[1],args[2]);//Create New Thread 
                        Thread th = new Thread(mti,"Thread"+i);                              //Start Thread     
                        th.start();
                    }
                    break;
        }
    }
}