How to store data in MySql through multiple threads?

Question

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.

duffymo · Accepted Answer · 2009-09-26 20:51:44Z

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.

edited Sep 26, 2009 at 20:51

answered Sep 26, 2009 at 19:49

duffymo

310k46 gold badges376 silver badges570 bronze badges

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

2 Comments

ChssPly76 Over a year ago

Connections actually are thread-safe as mandated by JDBC spec. In fact, you said so yourself in answer to stackoverflow.com/questions/1209693/… 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

duffymo Over a year ago

ChssPly76, thanks for the correction. A terrible mistake, indeed.

Waleed Amjad · Accepted Answer · 2009-09-26 19:48:37Z

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.

answered Sep 26, 2009 at 19:48

Waleed Amjad

6,8284 gold badges37 silver badges35 bronze badges

Comments

ZZ Coder · Accepted Answer · 2009-09-26 21:10:34Z

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.

answered Sep 26, 2009 at 21:10

ZZ Coder

75.7k30 gold badges139 silver badges169 bronze badges

Collectives™ on Stack Overflow

How to store data in MySql through multiple threads?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related