Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Select query not working with Jdbc for transactional calls.

 
kartik krishnan
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are the insert and the select done with the same database connection?
 
Jelle Klap
Bartender
Posts: 1951
7
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
kartik krishnan
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@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
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
kartik krishnan
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@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
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have you tried using getGeneratedKeys()?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic