wood burning stoves*
The moose likes JDBC and the fly likes Best way to connect? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Best way to connect?" Watch "Best way to connect?" New topic
Author

Best way to connect?

Ed Carrington
Ranch Hand

Joined: Oct 11, 2007
Posts: 77
What is the best way to connect to a database in Java Class?
I have used 3 different ways and would like to know what is the most efficient one.

1- Put the connection as a data member:



2 - Put connection in Constructor:



3- Put connection in every method that needs a connection:

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19649
    
  18

Originally posted by Ed Carrington:
What is the best way to connect to a database in Java Class?
I have used 3 different ways and would like to know what is the most efficient one.

Define efficient. Speed? Resources?

1- Put the connection as a data member:

...

2 - Put connection in Constructor:

...

The second has a bug, because you need to declare the connection outside of the constructor to be able to use it in other methods. If you do this, these two are actually the same. The timing is a little different, but in both cases the connection is created as the object is created.


Basically the question is, what is more important.
Do you want speed? Then create one connection when the object is created (1 or 2). The drawback is that the connection is open even if unused.
Do you want to close unused connections? Then use option 3. The drawback is that you have to create the connection every time, which will be a speed killer.


Alternatively, you could try using a connection pool. This does have a number of unused open connections, but the number is limited, and connections are shared among multiple objects. It's also (almost) as fast as option 3 because you retrieve an open connection when you need one.
[ November 15, 2007: Message edited by: Rob Prime ]

SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
I haven't done a lot of JDBC over the years, but recently put something together kinda like this:

So far I'm pleased with this because it does the open and "finally close" for me so I can never forget it and handles some exceptions I don't really care about. (The close exception handler is actually pluggable; I could use one that doesn't swallow the exception.) The handlers isolate all knowledge of the SQL and table and column structures into one place. Other classes never touch connections, statements, resultsets.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Ed Carrington
Ranch Hand

Joined: Oct 11, 2007
Posts: 77

Basically the question is, what is more important.
Do you want speed? Then create one connection when the object is created (1 or 2). The drawback is that the connection is open even if unused.
Do you want to close unused connections? Then use option 3. The drawback is that you have to create the connection every time, which will be a speed killer.


Thanks, that is great info that I didnt know about speed, resources and closing. I always thought the issue was connection closing and didnt know I had options.

If I close stmt in the PreparedStatment method and close the connection in the method that calls it then I will be okay with my options 1 or 2?


Method that calls insertDb:

[ November 15, 2007: Message edited by: Ed Carrington ]
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19649
    
  18

Well, if you close the connection after using it, then you will have to reopen it when using it again, or you won't be able to call the dbMethod twice on the same object.

If you go for option 1 or 2, don't close the connection after using it. Instead, create a method to explicitly close it.
wang jiajian
Greenhorn

Joined: Sep 20, 2007
Posts: 9
why not use connection pool?
[ November 16, 2007: Message edited by: wang jiajian ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Best way to connect?
 
Similar Threads
Connection attempt
Could not locate DB driver
MS Access, Jboss, BMP
Database helper class and closing question
MS Access, Jboss, BMP