Understanding Connection Pooling - a few problems

[Shitij Bhargava]

Hi !
I am reading about connection pooling, and have seemingly understood why it is used. But I am not sure how it is used. This is a code I wrote after what I understood (manipulated some code i found on the internet): (I get the correct output for this program)

import java.sql.*;
import javax.sql.*;
public class ConnectionPooling2
{
public static void main(String[] args) throws Exception
{
Connection con;
try
{
com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource ds=new com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource();
ds.setServerName("localhost");
//ds.setPortNumber(3306);
ds.setDatabaseName("employee_record");
ds.setUser("root");
ds.setPassword("root");
PooledConnection pc = ds.getPooledConnection();
con=pc.getConnection();
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("SELECT * from employee");
rs.next();
System.out.println(rs.getString(1)+" "+rs.getString(2));
}
catch(Exception e){System.out.println("Exception !!"+e);}
}
}

Am I getting a pooled connection here ? Is there any way by which I can verify that I have got a pooled connection (and not a simple connection)?
Also, why do I need to do:- "PooledConnection pc = ds.getPooledConnection();", i mean could you explain what this statement exactly does ? Why dont we do simply do:-"Connection con=ds.getConnection()". Program works even if I do this, but do I get a pooled connection in that case ?

Also, the code I found on the internet (and most others) used JNDI in this program in some weird way. I admit that I understand JNDI very little, but could you tell me the need of using JNDI here ? What is the advantage ?

Thank you for your help in advance !!

[Prabhakar Reddy Bokka]

The code using JNDI lookup for creating DataSource, is getting the DataSource from an Application Server.
Application Servers allows you to create Connection Pool and DataSource and make it available to the applications using it. The allications now make a lookup on the Application Server to get the DataSource and get the connections from the Connection Pool. Infact the DataSource will be created on top of Connection Pool. So, everytime you get a connection from DataSource, that is actually retrieved from the Connection Pool.

DataSource is an interface to get connections from Connection Pool.

Now.. looking at your code,

MysqlConnectionPoolDataSource will create a PooledConnection when you call
PooledConnection pc = ds.getPooledConnection();
When you close the connecction the actual physical connection to the database is not closed. the connection instance is returned to the pool and returned when next request came for connection.

In the second case Connection con=ds.getConnection() will create a physical connection with the database, and when you close it the actual connection with the database is closed. When you request for a connection next time again a new connection with the database will be created.

In real time applications creating a database connection and closing are very costly, and hence all the applications will use Connection Pool concept to interact with the databases.

[Jan Cumps]

Welcome to JavaRanch, Shitij.

[Shitij Bhargava]

Thanks a lot for your answer Prabhakar ! I understand things a lot more now !

But here are a little more doubts:

Just to confirm, so JNDI actually allows you to make more portable code ? (Hardcoding the data source like I did doesnt
make portable code I guess ?) Its like different applications may be using different dbms's or different versions of
dbms's and thus may have different names for their respective classes implementing DataSource interface ? Isnt it?
JNDI can itself somehow map names to the correct DataSource name? I have understood it right, haven't I ?

Also, you said

MysqlConnectionPoolDataSource will create a PooledConnection when you call
PooledConnection pc = ds.getPooledConnection();

then what does con=pc.getConnection(); do ? I cant understand why we need two statements to get a connection to the pool instead of just one. (what is the difference between the two ?)

Thanks again !!
(and thanks Jan Cumps, feels good to be here)

[Prabhakar Reddy Bokka]

Externalize your JNDI names of DataSources to a properties file.
Then isn't your code portable? JNDI names are now configurable depending on the user's need.

con=pc.getConnection(); will give you a ready made connection available with the pool.