Returning a connection to the connection pool

[Nivetha Shri]

Hi All

Regarding JBoss Connection pooling using Datasource.

http://www.javaranch.com/journal/200601/JDBCConnectionPooling.html

The above specified link states that connection.close() will return the connection to the pool and not actually close the connection.

Can someone explain me what is actually happening internally when we call the close() method.

If connection.close() returns a connection to the pool, then how do we actually close a connection?



Thanks and Regards
Nivi

[Moojid Hamid]

The whole point of pool is to reuse the connections. Is there any good reason why you want to close the connection in the first place?

[Nivetha Shri]

Yea i understand that the whole point of a pool is to reuse and so we needn't close it.

Am just curious enough to know if close() returns the connection to the pool then how to close it actually?

Also, it would be great if you can let me know whether Datasource gives a logical connection or a physical connection?
Can we achieve connection pooling by using a datasource as follows?

i.e.,

A XX-ds.xml in the deploy folder of jboss

and in the java code the follwowing snippet

InitialContext context = new InitialContext();
DataSource dataSource = (DataSource) context.lookup("java:DataSourceName");
connection = dataSource.getConnection();

Is the connection returned by the datasource actually from a pool?
and if say connection.close will it actually return it to the pool?
i.e., is it a logical connection or physical connection.

Thanks and Regards
Nivi

[Peter Johnson]

The data source pool maintains physical connections to the database. At first you get min-pool-size connections, and as you need more, you can have up to max-pool-size connections. And those are all physical connection. You can use the ManagedConnectionPool mbean to see how many physical connections are made and how many are in use.

When you ask the DataSource for a connection, it gives you an available connection (which is physical). When you close that connection, the DataSource keeps the physical connection with the database open and simply returns the connection object to the pool for the next requestor to use.

[Nivetha Shri]

Hi Peter Johnson

Thanks for your response.
I understand the concept now.
Have a question in getting it implemented.

http://java.sun.com/j2se/1.4.2/docs/api/javax/sql/DataSource.html
This site says that there are 3 different types of datasource implementation

1. Basic implementation - produces a standard Connection object
2. Connection pooling implementation
3. Distributed transaction implementation

What i understood is every vendor will have 3 types of implementations as said above and provides connections appropriately.

For example,
1) in the sybase-ds.xml if i say <driver-class>com.sybase.jdbc2.jdbc.SybDataSource</driver-class> and use

InitialContext context = new InitialContext();
DataSource dataSource = (DataSource) context.lookup("java:jdbc/SybaseDB");
Connection connection = dataSource.getConnection();

then i will get a normal connection object (the same as we get by invoking a DriverManager)

2) if i say <driver-class>com.sybase.jdbc2.jdbc.SybConnectionPoolDataSource</driver-class> and use

InitialContext context = new InitialContext();
ConnectionPoolDataSource poolDataSource= (ConnectionPoolDataSource)context .lookup(""java:jdbc/SybaseDB");
PooledConnection pconn = poolDataSource.getPooledConnection();

then i will get a connection object from a pool.

3) com.sybase.jdbc2.jdbc.SybXADataSource for Distributed transaction implementation.

This means that the second option specified above should be used for setting up a connection pool
Is my understanding correct?

Any help on this will be of great use.

Thanks and Regards
Nivi

[Peter Johnson]

for 1) and 2), those classes are provided by the Sybase driver as data source implementations for stand-alone Java apps; they are not for use within JBoss AS which provides its own data source. For the driver-class, you must specify a driver implementation, not a data source implementation.

3) This class would be used in a distributed transaction environment. Look at the docs/examples/jca/*-xa-ds.xml file for examples on how to configure them.

[Nivetha Shri]

Hi Peter Johnson

The example file in docs/examples/jca/sybase-ds.xml had the following
<driver-class>com.sybase.jdbc2.jdbc.SybDataSource</driver-class>

which misguided me to specify a datasource in the driver class. Also, when i saw 3 different types of datasources implemented in sybase i got even more confused.

Anyway i got my connection pooling implemented :-) by specifying com.sybase.jdbc2.jdbc.SybDriver in the driver class. Got it verified by checking the parameters in jmx-console

Actually com.sybase.jdbc2.jdbc.SybDataSource class is extending com.sybase.jdbc2.jdbc.SybDriver class and that is why am not getting any errors when i specify the datasource class instead of a driver class.
Correct me if am wrong again..

Thanks & Regards
Nivi

[Peter Johnson]

I went purely by the name (especially since I don't have the Sybase driver JAR), so I thought SybDataSource was a data source, but since you found out that it is a driver, then you can use that class.

[Nivetha Shri]

Thanks for all your responses :-)