JdbcTemplate Connection Management in MyIsam tables

[minmay]

Please correct me if I am wrong, but when the
JdbcTemplate is used in Spring, in a non-transactional environment (without the Spring Transaction Manager),
then "each time" an operation is invoked, such as
queryForInt, it opens a new connection, executes the statement,
and then closes the statement and connection. Is this correct?

Hence, if some service calls queryForInt multiple times, that's three
database connections opened and closed.

If the Spring Transaction Manager is used, it recognizes that
the operation is part of a transaction, and re-uses the same connection,
by checking if the call is within the same thread, or something along
that line. Kind of magical, but nice if that is that what it does.

But what I am really wondering, is how does this type of resource
management occur in a non-transational situation, such as when
using MyIsam non-transactional databases?

I don't want a new connection each time. Does the Spring Transaction
Manager at least manage the JdbcConnections properly in that situation?

Maybe I am getting this all wrong. Oddly enough, transaction management
is generally considered an advanced topic, but I am finding that
developing without transactions is even MORE DIFFICULT!

Any sense of direction would be greatly appreciated

[karldmoore]

If you are using a connection pool, then you aren't going to be creating a new connection you are simply looking one up. As for the non-transactional database, then you should still be able to use the transactionManager but this will simply execute non-transactionally.

[minmay]

We are using a connection pool. I need to rephrase
my concerns.

Here is a use case explaining how Spring JdbcTemplate Works with
connection pooling (nevermind transactions at this point).

JdbcTemplate Use Case

1) retrieve connection from pool
2) execute statement
3) close statement
4) return pool back to pool

But, the Use Case that I am handling is more like this:

JdbcTemplate Delagating Use Case
1) retrieve connection from pool
2) execute statement
3) close statement
4) Include the JdbcTemplate Use Case with this requirement:
enforce that the exact
same connection is used. This requirement is necessary because
MyIsam is not transactional, hence in certain situations we need
to lookup the primary key that was just inserted, and that is only
supported WHEN the same connection is used.
5) return pool back to pool

That's actually what I am trying to figure out.

[karldmoore]

Either bind a connection to the thread yourself, or IMHO use a transaction.

[wpoitras]

You can use a transaction to reuse the same database connection without actually performing actual database transactions. You just need to use TransactionProxyFactoryBean or TransactionTemplate and a propagation which doesn't start a transaction: PROPAGATION_NEVER, PROPAGATION_NOT_SUPPORTED, PROPAGATION_SUPPORTS. This will cause Spring to bind the current connection for all of the database operations in the transaction, but not actually do a commit/rollback for the whole thing, but what separate connections would do: commit after each statement.