aspose file tools
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

Win a copy of Head First Android this week in the Android forum!
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.
Don't get me started about those stupid light bulbs.
subject: Transaction or Table Locking?
It's not a secret anymore!