Win a copy of Micro Frontends in Action this week in the Server-Side JavaScript and NodeJS forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Getting connection from pool?

 
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Assume that we get database connection from datasource pool in jdbc. If we dont close the connection that connection
will be lying there as it is and consider as connection leak and will not return to pool. But if we close the connection that connection will
move back to pool , Is this correct?
Secone question is we always say connection is expensive and scarce reasource. Can't we get connection for every request
even if we dont close some of the connections in between(i mean some connection leaks exists)?Is there a maximum limit of connection production
on database server ?
 
Bartender
Posts: 2658
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

1st question: Yes, that is right.

2nd question: there is a maximum number of connections.
 
scott miles
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Jan Cumps wrote:Hi,

1st question: Yes, that is right.



In case we dont get the connection from datasource, and we do connection.close then connection will be physically closed but in case of datasource it will go back to pool. right?

2nd question: there is a maximum number of connections.




cant we increase to some large value near to infinite so that connection is never run out even in case of resource leak.
 
Rancher
Posts: 43016
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

scott miles wrote:But if we close the connection that connection will move back to pool , Is this correct?


No. You should never close a connection that you obtain from a connection pool - you should return it back into the pool when you're done with it. Closing it a) does not return it to the pool, and may b) play havoc with the pool's connection management (at least of you're talking about java.sql.Connection; if you're using some other connection abstraction, then it may well return the connection to the pool if you call some "close()" method it may happen to have).

As to increasing the pool size to a large number - don't do that. It runs counter to the point of having connections managed by the pool. Instead, look at the pool options - most have a setting where you can specify a timeout where a connection is reclaimed by the pool if it isn't returned to the pool within a specified timeframe. See http://www.mchange.com/projects/c3p0/index.html#unreturnedConnectionTimeout for c3p0 and http://commons.apache.org/dbcp/configuration.html for Apache DBCP (search for "abandoned").
 
scott miles
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

No. You should never close a connection that you obtain from a connection pool - you should return it back into the pool when you're done with it.



How can we return the connection to pool in programme?

 
Ulf Dittmer
Rancher
Posts: 43016
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That depends on the connection pool API.
 
author & internet detective
Posts: 40213
816
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ulf Dittmer wrote:

scott miles wrote:But if we close the connection that connection will move back to pool , Is this correct?


No. You should never close a connection that you obtain from a connection pool - you should return it back into the pool when you're done with it. Closing it a) does not return it to the pool, and may b) play havoc with the pool's connection management (at least of you're talking about java.sql.Connection; if you're using some other connection abstraction, then it may well return the connection to the pool if you call some "close()" method it may happen to have).


All the ones I've used have wrapped close() to mean return to pool. This matters because the code shouldn't care whether the datasource is giving a pooled connection or a standalone one. This being the code that calls close on resultset/statement/connection.

Do you have an example of some library that you don't call close() to return the connection to the pool? Or are you referring to Scott's scenario where he gets a pooled connection in some way other than the pool?
 
Ulf Dittmer
Rancher
Posts: 43016
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, I've seen pool implementations that would hand you a raw java.sql.Connection that you had better not close. There'd be an explicit call to the pool API to return it to the pool. It's possible that these types of pool implementations are now extinct, given that most JDBC drivers now have their own pooled DataSource implementations.
 
I didn't do it. You can't prove it. Nobody saw me. The sheep are lying! This tiny ad is my witness!
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic