How to store data in MySql through multiple threads?

Question

I have started five threads from main() function.

I have written three funtions as follows:

getConnectionToDatabase();
saveToDataBase();
closeConnection();

Now I want that main function should establish a connection to the database before starting those five threads by calling getConnectionToDataBase() so that each thread doesn't have to start a new connection.

Now I want that each thread should store the data by calling saveToDataBase() by first getting the connection established by the main() funtion.

How can I do that.

I can provide additional information if you need any.

Answer 1

Can you not make getConnectionToDatabase return a database link and then pass on that link to saveToDataBase and closeConnection.

That way you only connect once and use that link for all your queries in that session:

For example, your connection function could be something like:

Class.forName("org.gjt.mm.mysql.Driver"); // Load the driver
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/data", "root", ""); // Connect

return conn;

You then pass on the returned link to the other functions which use it accordingly.

Have a look at http://www.dreamincode.net/forums/showtopic32360.htm it's quite helpful.

Answer 2

It's a bad idea to do this. (Thanks to ChssPly76 for pointing out my error and poor memory.)

But if you must, have the main class create the connection and pass it to the constructor of each class that implements Runnable. The class that does the database work will have a private data member that references the Connection. Then the main class closes the Connection when they're done.

A better design is one Connection per thread with a pool of Connections, but it's your choice.

Answer 3

With MySQL, multiple threads can't share connections. You should try it to see what happens. You will get lots of State Errors from server.

MySQL has a synchronized protocol, each connection (physical TCP connection) can only do one thing at a time. You have to wait one transaction finish before sending next query. With MySQL's JDBC driver, one JDBC Connection is mapped into one physical connection.

If you control the thread, you can have per-thread connection. Open the connection when thread is started and close when thread is stopped.

If you have no control of thread creation, you need to use some kind of pooling mechanism. You can check out DBCP or C3P0.