This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Is transaction possible when one query is dependent on previous query result? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Is transaction possible when one query is dependent on previous query result?" Watch "Is transaction possible when one query is dependent on previous query result?" New topic
Author

Is transaction possible when one query is dependent on previous query result?

Sijohn Thomas
Greenhorn

Joined: Mar 10, 2011
Posts: 3
I have three queries.

Query 1 - Inserts a row in table1.
Query 2 - Fetches the primary key id for the inserted record (by Query 1) in table1.
Query 3 - Uses the id fetched by the second query as foreign key to insert values in table 2.

Can I put all 3 queries in one transaction?

If I write like

conn.setAutoCommit(false);

execute all three queries

conn.commit();

The problem I would be facing is if the third query will fail as the commit happens at last. (As it requires the id by query 2). Please help.



Saikat Mukherjee
Greenhorn

Joined: Mar 06, 2008
Posts: 13
This can be achieved using a stored procedure if i am not wrong
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2271
    
  28

Yes you can put all SQL statements in one transaction, and each SQL will see the changes done by other sqls before it.

Actually, isolation level changes this behavior a bit. A transaction will always see its own changes. If you use read uncommitted, transactions can see other transactions changes. If you use read committed or repeatable read, transaction will not be able to see other transactions changes unless the transaction is committed.
Sijohn Thomas
Greenhorn

Joined: Mar 10, 2011
Posts: 3
Thanks Jayesh. Would appreciate if you could elaborate on transactions with read uncommited with an example. Thanks a lot.
 
Don't get me started about those stupid light bulbs.
 
subject: Is transaction possible when one query is dependent on previous query result?
 
Similar Threads
Query Dillema
Mapping of three tables
Hibernate: Different Join in Save() and Get()
Single SQL query for retrieval of data with different where clause possible?
select distinct