• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Devaka Cooray
  • Ron McLeod
  • Jeanne Boyarsky
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Martijn Verburg
  • Frits Walraven
  • Himai Minh

Best way to connect?

 
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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:

 
Sheriff
Posts: 22701
129
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 22701
129
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
why not use connection pool?
[ November 16, 2007: Message edited by: wang jiajian ]
 
Something about .... going for a swim. With this tiny ad ...
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic