Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Best way to connect?

 
Ed Carrington
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20511
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ed Carrington
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Pie
Posts: 20511
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
why not use connection pool?
[ November 16, 2007: Message edited by: wang jiajian ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic