0

I have started five threads from main() function.

I have written three functions 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() function.

How can I do that?

I can provide additional information if you need any.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Amit
  • 33,847
  • 91
  • 226
  • 299

3 Answers3

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.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • Connections actually _are_ thread-safe as mandated by JDBC spec. In fact, you said so yourself in answer to http://stackoverflow.com/questions/1209693/is-mysql-connector-jdbc-thread-safe That said, using the same connection for multiple threads _is_ a bad idea unless it's absolutely necessary to perform all work in a single transaction. In all other cases connection pool is definitely the way to go. +1 – ChssPly76 Sep 26 '09 at 20:40
  • ChssPly76, thanks for the correction. A terrible mistake, indeed. – duffymo Sep 26 '09 at 20:50
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.

Waleed Amjad
  • 6,716
  • 4
  • 35
  • 35
0

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.

ZZ Coder
  • 74,484
  • 29
  • 137
  • 169