Problem using DBCP with Simple JNDI.

[vikas.khengare]

Hi,

We are configuring Simple JNDI to use connection pool by 'pool=true'
in properties file. We are firing 20 select/insert queries, each time
a new connection is opened. But I want to use the same connection for
all 20 queries so I can handle transaction states like rollback,
commit on particular connection.

Each time it is opening a new connection when I call
dataSource.getConnection().

Environment:
Java: 1.5
Simple jndi: 0.11.3
Dbcp: commons-collections-3.2.1.jar, commons-dbcp-1.2.2.jar, commons-
pool-1.4.jar
Database: mysql

I am not able to set the parameters values like initialSize, maxActive
etc.
What are the default values for above parameters?

Do I need to specify something in code while creating context Or
something in properties file?

Thanks

[Henri Yandell]

If you check:

http://osjava.googlecode.com/svn/trunk/simple-jndi/src/java/org/osjava/sj/loader/PoolSetup.java

you'll see the list of supported properties. They should be documented
at http://www.osjava.org/simple-jndi/manual/DataSources.html - but
only the first batch are currently there.

Let me know if any properties are missing - I tried to get all the
ones I could see. As you can see, the default values are based on the
defaults from DBCP.

Hen

[Henri Yandell]

I've gone ahead and added this to the webpage below.

[Lars Kampen]

Hi Hen,

Vikas is right claiming that a new connection is established each
time. There seems to be missing a small part of the implementation of
your datasource class.
All the properties mentioned above are working well, but checking the
source code I found an unfinished part in SJDataSource (around line 90
in version 0.11.3).
There you placed a todo comment:
// TODO: Fix this, it pools each time, which is probably bad
Thanks for providing this hint, it helped a lot making it work.

I changed the method as follows:
------------
/** if a connection pool has been built, its url is stored in here
*/
private String poolUrl = null;

/** returns a connection to the database specified in the
properties and
* creates a connection pool, if neccessary */
public Connection getConnection(String username, String password)
throws SQLException {
String tmpUrl = this.url;
String pool = properties.getProperty("pool");
if (pool != null) { // we want a connection name named like
the pool property
synchronized (poolUrl) {
if (poolUrl == null) { // we didn't create a
connection pool already, so do i now..
PoolSetup.setupConnection(pool, url, username,
password, properties);
poolUrl = PoolSetup.getUrl(pool);
}
}
tmpUrl = poolUrl; // url is now a pooling link
}
if(username == null || password == null) {
return DriverManager.getConnection(tmpUrl);
}
return DriverManager.getConnection(tmpUrl, username,
password);
}
------------
This seems to works fine, but you have to specify a different name for
each pool that you use, if you have different databases. Since I
didn't want to introduce any new properties, I just used the pool
property, so when using two different database connections, you should
set the property not only to true, but to a unique value (see example
files below).

userdb.properties
type=javax.sql.DataSource
driver=com.sybase.jdbc2.jdbc.SybDriver
url=jdbc:sybase:Tds:10.63.84.215:5000
pool=userdb

appdatadb.properties
type=javax.sql.DataSource
driver=com.sybase.jdbc2.jdbc.SybDriver
url=jdbc:sybase:Tds:10.63.85.72:5000
pool=some_other_db

It would be nice if you could integrate this (or another solution)
within the next release, so I can exchange the locally patched library
with an official version.

Lars

[Henri Yandell]

Thanks Lars - I'll get that integrated into the next release (and look
to do that asap).

Hen

[Henri Yandell]

Patch applied. Very cool fix, thanks Lars.

Hen