Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting connection from pool?

 
scott miles
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 ?
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • 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.
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • 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: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That depends on the connection pool API.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34680
367
Eclipse IDE Java VI Editor
  • 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: 42968
73
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic