DB Connection Question for MySQL

[Michael Salvini]

Good Evening All,

I am fairly new with Java and just playing around creating various little tools and applications while studying for my SCJA. I have been reading through the Oracle tutorials regarding the use of JDBC and I am curious about some things.

1) Is it better practice to make a DB Utility class to make a connection to the database each time a query needs to be executed on the dbms or is it better practice to create a db class that allows the connection to stay open and then I can just pull a reference to the connection each time.

2) I have also seen the connection established two ways, the first:

try {
	Class.forName(driver);
} catch (ClassNotFoundException e) {
	JOptionPane.showMessageDialog(null,"Unable to Load the Database Driver " + driver,"Driver Load Failed",1);
}

try {
	conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
	JOptionPane.showMessageDialog(null,"Unable to Establish a Connection to the DB" + e,"DB Conenction Failed",1);
}

And the Second:

try {
	conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
	JOptionPane.showMessageDialog(null,"Unable to Establish a Connection to the DB" + e,"DB Conenction Failed",1);
}

I am curious if the first call to load the driver is a necessary check. I know my error handling needs to be better.


Just curious.

Thanks,
Mike

[Tom Reilly]

Is it better practice to make a DB Utility class to make a connection to the database each time a query needs to be executed on the dbms or is it better practice to create a db class that allows the connection to stay open and then I can just pull a reference to the connection each time.

The unfortunate answer is that it depends on your needs. But I would start with opening and closing a connection for each query until you identify a problem. Then change the code. You can anticipate this need by providing a utility class that obtains and closes a connection on each request but can be changed to provide a connection cache without having to change the rest of your code.

[Michael Salvini]

Makes sense, thanks for the reply. What would typically dictate the need for such a change, strictly performance or something else?

Thanks,
Mike

[Tom Reilly]

Performance is what I was thinking. Perhaps someone else can add other reasons.

[Paul Clapham]

If you're writing a web application, then you might have two (or a hundred) simultaneous requests which need access to the database. It's better if each request has its own connection, so you don't get data from the two requests all mixed up. So the usual plan there is to have a connection pool which is managed by the web application container, rather than writing your own utility class.

[Michael Salvini]

Thank you Tom and Paul for your advice and input. Right now I am just building things for the sake of practice, so I will stick with opening and closing a connection everytime I need it, but I will definitely keep the connection pool idea bouncing around my head. I started to google that a little bit and I think the use of that is a little out of my league right now, I am really just trying to get the basics of the language down and then I will try and move onto some of these seemingly more advanced topics.

My eyes have been opened to a lot as a result of this question and that is what I am looking for.

I really appreciate you guys taking the time to help, thanks!

Mike