aspose file tools*
The moose likes JDBC and the fly likes Select query not working with Jdbc for transactional calls. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Select query not working with Jdbc for transactional calls." Watch "Select query not working with Jdbc for transactional calls." New topic
Author

Select query not working with Jdbc for transactional calls.

kartik krishnan
Ranch Hand

Joined: Nov 19, 2006
Posts: 63
Hi,

I am working on a web app which requires the user to save some web form information i(say customer) into the database using insert sql query (with PrepraredStatement api). and then load it immediately with some predefined db column values using a select query (again with PreparedStatement api). This must be made transactional. If the insert succeeds but select fails, then the entire operation must be rolled back.

The service layer method is calling both insert and select methods in DAO class. For my connection, I have set autocommit to false and nothing else. After both operations are executed without errors, I call connection.commit() explicitly. If an exception is thrown during insert or select, I call rollback.

My problem is that after I call my select query after running an insert query, I don't see any results. Is there anything I am doing wrong? Do I have to change anything for inserts to work?

Any advice would be appreciated.

Thanks,

Kartik
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

Are the insert and the select done with the same database connection?


OCUP UML fundamental and ITIL foundation
youtube channel
Jelle Klap
Bartender

Joined: Mar 10, 2008
Posts: 1823
    
    7

This is probably down to the transaction isolation level of the Connection, which defaults to TRANSACTION_READ_COMMITTED.
Try specifying TRANSACTION_READ_UNCOMMITTED using Connection#setTransactionIsolation(), which should allow you to read the uncommitted data you previously inserted within the same transaction.


Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.
kartik krishnan
Ranch Hand

Joined: Nov 19, 2006
Posts: 63
@Jelle Thanks for replying. We are using a same connection instance. The connection is stored as a thread local object and is acquired by the data access layer.

@Jan Thanks for the advice. To clarify if I set the isolation level to READ_UNCOMMITTED, my select query will return the output before commit is called. So if my insert operation is rolled back, then the select query will not return anything. On the other hand, if once commit is executed, the insert operation changes are persisted into the database. Have I understood this correctly?
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

This is one of those situations where you shouldn't fix the issue with database settings (reading uncommitted data for example) but with your code. Why are you trying to read data you just inserted? Why not use the data in memory you inserted into the table instead?


My Blog: Down Home Country Coding with Scott Selikoff
kartik krishnan
Ranch Hand

Joined: Nov 19, 2006
Posts: 63
@Scott, Thanks for replying. We have to insert into the database because the database generates some default values for columns not populated with JDBC calls. We need that information to be displayed. One of them is the customer id. The customer id is uniquely associated with a customer and the customer should have that id when logging into our application.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

Have you tried using getGeneratedKeys()?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Select query not working with Jdbc for transactional calls.