How can I request a specific connection from com.mchange.v2.c3p0.ComboPooledDataSource?

Question

Problem:

• The program uses com.mchange.v2.c3p0.ComboPooledDataSource to connect to the Sybase server
• The program executes 2 methods, runSQL1() and runSQL2() , sequentially

• runSQL1() executes SQL which creates #temptable

   SELECT * INTO #myTemp FROM TABLE1 WHERE X=2 

• runSQL2() executes SQL that reads from this #temptable

   SELECT * FROM #myTemp WHERE Y=3 

• PROBLEM : runSQL2() receives a different database connection from the pool from the pool than the one passed to runSQL1() .

  However, Sybase #temptables are connection specific, so runSQL2() fails if the table cannot be found .

The most obvious solution that I can think of (in addition to degenerating one pool size 1, in which we do not even need a pool), you need to somehow remember which specific connection from the pool used runSQL1() , and runSQL2() request the same connection .

Is there a way to do this in com.mchange.v2.c3p0.ComboPooledDataSource ?

If possible, I need a concurrency -safe response (in other words, if the connection used in runSQL1 () is being used by another thread, calling runSQL2 () on the connection will wait for that connection to be issued by the other thread).

However, if this is not possible, I am fine with the answer, which assumes that the database connections (the ones that I like) occur on the same thread, and therefore any connection requested by runSQL2 () will be 100% available if it was available to run SQL1 ().

I also welcome any solutions that solve the problem differently if they do not include "stop using #temptables" as part of the solution.

Answer 1

The easiest and most obvious way to do this is to simply request a connection from the pool, and then run runSQL1() and runSQL2() with that connection. The usage model proposed in the question runs counter to the general principles of designing connection pool managers, as it effectively promotes them to a specific transaction manager.

There are Java frameworks that can help with this. For example, in Spring @Transaction or TransactionTemplate can be used to delimit transaction boundaries, and this ensures that a single connection is used by a single thread (more precisely, in accordance with transaction propagation annotations). Spring can use many transaction managers, but probably the easiest would be to use the DataSourceTransactionManager , and it can also be configured to use c3p0 as a DataSource .