MySQL and Java - Repeat operations for multiple databases

Question

I have a program in Java which do some search operations on a database, this is the code:

public class DBSearch {

    public static void SearchDatabase() throws Exception{
      ArrayList<String> names = new ArrayList<String>();
      Connection c = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      String host = "localhost";
      String db = "mydatabase";
      String user = "root";
      String password = "password";
  Class.forName("com.mysql.jdbc.Driver");
  c = DriverManager.getConnection("jdbc:mysql://"+host+"/" + db +"?user="+user+"&password="+password);
        ps = c.prepareStatement("SELECT NAME FROM NAMES");
        rs = ps.executeQuery();
        names.clear()
   while ( rs.next() ) {
        names.add(rs.getString("NAME"));
    }
        for(i = 1; i < names.size(); i++){
        //Do some database operations

Now what I want to do is that at the end of these operations, the process starts back making the same operations in another database. My idea was to create an ArrayList called dblist containing all the database names, and then doing something like this:

...
for(i=1, i < dblist.size(); i++{
      Class.forName("com.mysql.jdbc.Driver");
      c = DriverManager.getConnection("jdbc:mysql://"+host+"/" + dblist.get(i) +"?user="+user+"&password="+password);

But the process seems not to loop for each database, it works with only the first database in the array and than it stops.

duffymo · Accepted Answer · 2015-06-25 09:51:13Z

1

What messages do you get? No one can help based on what you've posted.

This is ugly, inefficient code.

I'll offer a few recommendations:

External your database configurations. It'll be easier to reuse. You'll also be able to leverage connection pools.
Encapsulate the operation in an interface-based POJO that you can easily test.
This problem is easily parallelizable. Once your POJO is running and tested you can use a Callable to interrogate many databases at once using an Executable.

Sounds like a bad design. Why would you keep redundant data scattered across many databases?

edited Jun 25, 2015 at 9:51

answered Jun 25, 2015 at 9:39

duffymo

310k46 gold badges376 silver badges571 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Java Apprentice · Accepted Answer · 2015-06-26 16:22:26Z

Instead of having the variable on the outside of the for loop try placing it inside. Therefore, it will create a new connection every time.

Also check that you are getting the database information correct from dblist. Matter of fact make sure you are even getting the database information instead of running the same connection in the for loop.

Ex. Note that I use 0 in the for loop because the first index in an Array List is 0.

for(i=0, i < dblist.size(); i++
{
    dblist.get(i);
    //Separate the string you got from the ArrayList into host, user, pass and assign correct variables
    Class.forName("com.mysql.jdbc.Driver");
    //Variables you got from the list go in the next statement
    Connection c = DriverManager.getConnection("jdbc:mysql://"+host+"/" + db +"?user="+user+"&password="+password);
}

Not that I didnt have my get statement in the Connection (Im assuming your ArrayList contains the host, database, user, and pass)

Alternatively you could just store the whole connection string in the Array list and it would just be:

Connection c = DriverManager.getConnection(dblist.get(i));

Collectives™ on Stack Overflow

MySQL and Java - Repeat operations for multiple databases

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related