Threads and Databases

[Leslie Chaim]

Hello everybody,
I am really new in the Java world. Recently, a friend told me about this ranch and I love it. I like metamorphic illustrations
I have this application to develop, and I am trying not to re-invent-the-wheel here getting the best OO-approach possible. I have some ideas and read a number of books on the subject I am now 'almost' ready to proceed. Since I found out about the barn here I though I will share my problems with you. Since this is my first real Java program I am about to write. Any suggestions or ideas are greatly appreciated.
Currently, I have a C program that accepts database connections as arguments from the command line. The program then reads a file (from stdin) that is structured consisting of header with SQL instructions, followed by data sections and reference points to each SQL statement.
The program is to load the data to all database connections specified on the command line, and to commit to each database only if all INSERTS succeeded for all database connections. If ANY database had a problem inserting the data, the program issues a ROLLBACK. Thus, we have our data replica properly in sync.
The problem with this program is speed. The entire database related work is done in a queued fasion. Each connection's work is done in a loop in a serialized way. Using threads and with out multi-processor machine, if we could set up that each database connection should do its work in its own thread and have a kind of Boss/Worker relation:
The Boss will create n threads per specified connection on the command line. Then the Boss can supply the SQL instructions for each Worker, then supply the data, and the Boss should wait for all workers to get the data in the database in their own thread. When all workers are done, the Boss will inspect if there were no errors in any of the workers, and then instruct the workers to either commit or rollback.
Not Done Yet, Yes for now this will run from one machine but in reality these databases are on different machines, and I would really like to be able to use the resources from the host machine for each database. Therefore, when I design my class I want to keep in mind to eventually be able to do some cool stuff with RMI.
Thanks,

[Mr. C Lamont Gilbert]

Sounds like your on the right track. I have done lots of database stuff in Java as well as socket and RMI stuff. I would use the same organization your are speaking of. Let the main server spawn a thread to handle the connection to each remote database. make the treads and kick them off, then wait on them to tell you when their done. when all are done check the success of each. if unsuccessful, inform the threads to roll back the changes. Then allow the threads to end their lives.
RMI is perfect for that and you will find that RMI is quite easy to use for a distributed technology.

[George Brown]

A few thoughts...
You say <i>The Boss will create n threads per specified connection</i> - wouldn't it be safer to have 1 thread per Connection ? And where you're using the same type of connection for multiple threads you would need to use a connection pool.
Also, are your databases of the same type? You mention a replica and keeping the data in sync so I guess what you're doing is some sort of custom data replication mechanism.
Are you assuming that the different databases may be of a different type, ie. one Sybase, one DB2, one Informix, etc, (it may be a good future-proofing assumption) and making sure that you can factor multiple JDBC connection pools (for example) into your design?

[George Brown]

You may also want to look at what DbConnectionBroker from http://www.javaexchange.com/ offers for concurrent database connections.

[Leslie Chaim]

OK, let me clarify, I want
the boss to create n threads for each database connection string on the command line. As CL points out:

Let the main server spawn a thread to handle the connection to each remote database.

So yes George, I want a separate thread per connection, and indeed, it is

some sort of custom data replication mechanism.

I am not really assuming that the databases may be of different type; in my case, they are both Oracle databases. Nevertheless, the application will still be generic and (I like this) 'Future proofed' because all the SQL instructions is in the input file. All this application will read the instructions applying it to the databases. And yes, I will be using JDBC.

Well, in my original posting, I simply stated where my design thoughts are bound. From the responses I have gotten so far, I feel that I am on the right track.
I will list the things that I am looking for by presenting what I want the main() function to do:

Validate the command line arguments for options and database connections specified as UID=scott/tiger@box1 UID=scott/tiger@box2 UID=�

Create a object 'LoadData' with a new thread for each database connection string.

Read (line by line) from stdin (I want the app to be like a filter so it can read data from a pipe) the header section of the file containing the SQL instructions.

Pass the SQL instructions to each 'LoadData' object.

Read (line by line) from stdin a complete data chunk

Notify all the 'LoadData' objects of the available data

Wait for all the 'LoadData' to complete the SQL instructions

Confirm that all SQL instructions for all 'LoadData' objects were successful

Issue a COMMIT or ROLLBACK

Repeat steps 5-9 until all chunks are exhausted (i.e. reached EOF)

This is my first threaded program that I am about to write. Therefore, I am looking for ideas specific to my situation and for any tips on the other issues listed.
On to RMI, I want 'eventually' that the 'LoadData' object to be on the host box of the database. I would definitely want to this from start but I think it is too much at once for me. I have yet to write anything in Java let alone threads and adding RMI would prolong the project objective at the moment. However, I would like to know, what are the basics that I must consider when designing my 'LoadData' object.
Also, I am looking for a good book on RMI.
Thanks for all your help.