aspose file tools*
The moose likes JDBC and the fly likes Getting connection from pool? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting connection from pool?" Watch "Getting connection from pool?" New topic
Author

Getting connection from pool?

scott miles
Ranch Hand

Joined: Jun 16, 2011
Posts: 70

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 ?
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2495
    
    8

Hi,

1st question: Yes, that is right.

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


OCUP UML fundamental and ITIL foundation
youtube channel
scott miles
Ranch Hand

Joined: Jun 16, 2011
Posts: 70
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.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41525
    
  53
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").


Ping & DNS - my free Android networking tools app
scott miles
Ranch Hand

Joined: Jun 16, 2011
Posts: 70

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
Marshal

Joined: Mar 22, 2005
Posts: 41525
    
  53
That depends on the connection pool API.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30309
    
150

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?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41525
    
  53
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.
 
 
subject: Getting connection from pool?