It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Transaction or Table Locking? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Transaction or Table Locking?" Watch "Transaction or Table Locking?" New topic

Transaction or Table Locking?

Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 567
Hi List People,
I'm starting an app which I would like to make database-independent, e.g. so that it can work on Oracle or mySQL. I would like to have one schema that works on both, no stored procedures and, well that's challenging enough.
My first issue: primary keys
I'll have an extra helper table (last_key_used) to store the last primary key used in each table. When I insert a new row in a normal table, I'll do this:
get last value from last_key_used table
increment it
update last_key_used table with incremented value
use the incremented value for the primary key in my new row
I want to try transactions - so that I can place the whole operation in a transaction - and alternatively I want to try just table locking. But I don't know how to do either in Java.
Can anybody give me a pointer for transactions?
Also any ideas how I could use table-locking, i.e. how do I get 4 SQL statements to execute in one block and return a value? (LOCK TABLE; SELECT; increment the value; UPDATE; UNLOCK;)

I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
I agree. Here's the link:
subject: Transaction or Table Locking?
It's not a secret anymore!