Hi,
I have a very quick proposal to handle connection pooling,
and I'm dumping it to a mail before starting work on something
else, so please excuse if it is in brief form.
To sum up the requirements we gathered so far, we need the jdbc
data store to use a DataSource some kind, but we
want to allow users to choose which one to use,
and how to configure it (for those who lost the discussion
before this, a DataSource is a jdbc Connetion provider, and usually,
but not necessarily, it keeps a pool of connections).
The old pooling class is so bad that we can just throw it away,
so this leaves us with JNDI, DBCP and C3P0 (there are others, but 3 candidates are enough to make some considerations).
Moreover, we need a way to "unpack" a connection provided
by the pool so that we get the native database connection,
this is needed for Oracle.
I propose we set up a new Factory, the DataSourceFactory,
that looks a lot like a DataStoreFactorySpi, but generates
a DataSource instead of a DataStore.
I don't have details sorted out, but the main ideas are that
you can look up these factories, instrospect their params
(min/max connections, timeouts, prep. statement caches and
whatnot), configure and get back a DataSource.
Probably the following 2 extra methods are needed:
* boolean isDataStoreCompatible(DataStoreFactorySpi)
* Connection unpack(Connection c)
The first one is used to allow datastore specific connection
pools (think an Oracle specific connection pool, that can
be used only with the Oracle data store).
The second one is there to unpack a connection, you give in
a pooled connection, you get out "null" if the factory
is not able to unpack the connection, a native connection
otherwise.
We can think about splitting the latter method into its
own interface, and have a method in the DataSourceFactory that
provides an unpacker:
ConnectionUnpacker getConnectionUnpacker(DataSource)
and then have the unpacking method in the returned class.
Ok, once we have this available, we can simplify the
jdbc data store factories to accept a DataSource and
a set of parameters that don't have to do with connection.
For example, the PostgisDataStoreFactory would end up
with the following parameters:
DATASOURCE (the datasource that someone will have to provide).
WKBENABLED (use wkb for transfer)
LOOSEBBOX (use loose bbox matching)
ESTIMATEDEXTENT (compute estimated bboxes)
From the on the fly generate UI point of view (think Geoserver),
every time a DataSource parameter is seen, we know we have to
look up the DataSourceFactorySpi compatible with the current
DataStore, ask the user which one he wants to use, and then
ask the parameters for that specific data source.
So, how does it look like? Feedback welcome.
Let's try to have this sorted out quickly 
Cheers
Andrea