Using Java EE Connection Pools
walt.sto...@gmail.com
I've got a session bean in JBoss that's currently updating a Postgres
database with JDBC. Note, this is not object persistence -- the bean
sits in a long loop processing all kinds of input streams, emitting
numerous rows and modifying complex relationships continuously.
Meanwhile, elsewhere, other applications are watching the database and
acting accordingly.
Problem is, as this bean does work and calls out, opening and closing
connections via straight JDBC is slow.
The obvious solution is to use connection pooling; what's the
*correct* way to use Java EE's existing connection pools for just
arbitrary SQL execution allowing intermittent commits? Can I even use
EE's connection pools without them being managed?
( I ask because my existing attempt to use EE's connection pools are
preventing me from calling commit() directly, and they seem to be
enforcing a transactions at the method level, which is most certainly
what I do not want; because code is perpetually writing to the
database, the transaction never ends, and all the external
applications see no data. )
Thanks,
-Walt Stoneburner
w...@wwco.com
SIMPLIFIED BROKEN EXAMPLE CODE FOLLOWS
Context ic = new InitialContext();
DataSource ds = ( DataSource ) ic.lookup( datasource_name );
Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement( some_huge_sql );
while ( looping_for_a_really_long_time ) {
// ...Get data from different places and do stuff with it ...
...
ps.setString( col, value ); // Assign values and such
...
ps.executeUpdate(); // Do the statement
// Problem is we're using a Managed Connection Pool - commits aren't
allowed!
conn.commit(); // Throws exception with Java EE! -HELP-
ps.clearWarnings(); // Clear warnings for next time through
}
conn.close();
Arne Vajhøj
> I've got a session bean in JBoss that's currently updating a Postgres
> database with JDBC. Note, this is not object persistence -- the bean
> sits in a long loop processing all kinds of input streams, emitting
> numerous rows and modifying complex relationships continuously.
> Meanwhile, elsewhere, other applications are watching the database and
> acting accordingly.
>
> Problem is, as this bean does work and calls out, opening and closing
> connections via straight JDBC is slow.
>
> The obvious solution is to use connection pooling; what's the
> *correct* way to use Java EE's existing connection pools for just
> arbitrary SQL execution allowing intermittent commits? Can I even use
> EE's connection pools without them being managed?
Just use the servers connection pool by looking up
ressources via JNDI.
If you need to control the transaction handling you need
to specify the session bean as being BMT not CMT.
I dot think there are a problem using BMT and server connection
pool.
It is not possible with CMT.
Arne
walt.sto...@gmail.com
> ressources via JNDI.
>
> If you need to control the transaction handling you need
> to specify the session bean as being BMT not CMT.
Thanks Arne, ...I've learned a few things in the past week that make
me wonder if I've approached the problem incorrectly. Allow me to
elaborate for anyone who's following in my footsteps.
With JDBC you get a connection to a database, and with it, one can
engage in transactions, doing database commits, etc.
Your message about BMT (Bean Managed Transactions) and CMT (Container
Managed Transactions) led me to understand that a JBoss Transaction is
_not_ a database transaction, but rather a unit-of-work transaction.
Such things make perfect sense in the context of viewing a EJB as a
piece of business logic, and that a transaction starts when you call a
method, and a transaction ends when the method returns. The reason
for the kinds of transactions is for the case when one bean calls
another: is it the same transaction, are the transactions wholly
independent, is there a dependency between transactions,...
My little problem is that my bean is stateless and, here's the
sticking point, is not trying to persist itself. It's just a piece of
code that gets triggered, to which it sits in a large loop reading
from a stream of near endless data and writes what it's finding
relevant to a database, stopping when it's reached enough.
Knowing that JBoss managed its own persistence by use of connection
pools for efficiency, I'd hoped to tap into the same resource, doing
whatever I wanted with the raw database connection, returning it to
the pool when done.
What's seemingly complicating the matter is that because this is
happening inside of a bean, it is automatically wrapped in a JBoss
Transaction. And, since I'm using a resource obtained from JBoss (the
database connection from the JNDI), it is complying with the implied
transaction. Meaning, no matter what I write, nor how much I close,
the data does not make it into the database until the first entered
method finally exits -- which, as I said being in a loop, can take a
while.
Is there a way, perhaps with class or method annotations to indicate
to JBoss that I don't want my bean persisted, that I don't want it
wrapped in a transaction, and that I'd like a raw database connection
from the pool? (I see by the JMX console, it's got about 20
connections sitting in the pool just waiting to be used.)
Thanks again; replies from anyone are helpful and encouraged.
-Walt Stoneburner, w...@wwco.com
joe.we...@gmail.com
There should be a way in the bean's descriptor to define it as
not supporting transactions. You should also be able to define
pools/Datasources to be non-transactional so whatever JDBC you
do with a connection is not known to the JBoss transaction
coordinator, and will come as an autoCommit(true) connection.
Joe Weinstein at BEA Systems
Arne Vajhøj
> Your message about BMT (Bean Managed Transactions) and CMT (Container
> Managed Transactions) led me to understand that a JBoss Transaction is
> _not_ a database transaction, but rather a unit-of-work transaction.
Database transactions can participate in a app server
transaction.
But the app server needs to be able to work with multiple
databases.
> Such things make perfect sense in the context of viewing a EJB as a
> piece of business logic, and that a transaction starts when you call a
> method, and a transaction ends when the method returns. The reason
> for the kinds of transactions is for the case when one bean calls
> another: is it the same transaction, are the transactions wholly
> independent, is there a dependency between transactions,...
You specify transaction behavior in the deployment descriptor.
> My little problem is that my bean is stateless and, here's the
> sticking point, is not trying to persist itself. It's just a piece of
> code that gets triggered, to which it sits in a large loop reading
> from a stream of near endless data and writes what it's finding
> relevant to a database, stopping when it's reached enough.
Session beans are not supposed to sit in a loop reading.
Have you considered a message queue and a message driven bean ?
> Knowing that JBoss managed its own persistence by use of connection
> pools for efficiency, I'd hoped to tap into the same resource, doing
> whatever I wanted with the raw database connection, returning it to
> the pool when done.
>
> What's seemingly complicating the matter is that because this is
> happening inside of a bean, it is automatically wrapped in a JBoss
> Transaction. And, since I'm using a resource obtained from JBoss (the
> database connection from the JNDI), it is complying with the implied
> transaction. Meaning, no matter what I write, nor how much I close,
> the data does not make it into the database until the first entered
> method finally exits -- which, as I said being in a loop, can take a
> while.
>
> Is there a way, perhaps with class or method annotations to indicate
> to JBoss that I don't want my bean persisted, that I don't want it
> wrapped in a transaction, and that I'd like a raw database connection
> from the pool? (I see by the JMX console, it's got about 20
> connections sitting in the pool just waiting to be used.)
BMT.
Arne