Java / MySQL - How to access connection from another class?

Question

I'm just getting my head around java (and OOP for that matter), the only thing I am familiar with is MySQL. I need to keep the DB connection open throughout the duration of the application, as well as a server socket.

I'm not even sure if they both need separate classes, but here's what I have so far:

http://pastebin.com/qzMFFTrY (it wouldn't all go in a code tag)

The variable I need is con for line 86.

Answer 1

Why not instantiate DoComms with the connection you've got earlier ?

e.g. line 44 would be:

DoComms conn_c = new DoComms(server, con);

and DoComms would hold a reference to that connection and then use it at line 86.

Note that you get the connection, and then close it in the finally block before you instantiate your DoComms objects (line 28). So you should close your connection once you've finished processing everything. Briefly:

try {
  // get connection

  // do stuff in threads
}
catch {
  // handle
}
finally {
  con.close();
}

If your application is long-lived, then I would use connection-pooling (e.g. C3P0 or Apache DBCP) and open/close connections as required. However your approach may well be suitable for your requirements, and I wouldn't worry about that for the moment.

Answer 2

I need to keep the DB connection open throughout the duration of the application

You shouldn't do that. The connection has a limited lifetime whose length is out of control from your application. When the DB decides that the connection is been open for too long, it will close the connection and you'll get connection reset or connection timed out exceptions. This is usually around 30 minutes, but can also be less.

The normal JDBC practice is to acquire and close Connection, Statement and ResultSet in the shortest possible scope, i.e. in the very same method block as you execute the query.

If the reason for keeping the connection that long open is due to performance, then you should consider connection pooling, for example c3p0 (usage guide here).