CODEWITHSUNDEEP
javajdbc
user3239126
user3239126

Reputation:

Records are not getting updated with ResultSet

I have some JDBC stuff defined over here.

I am basically, grabbing records from one table(test.selectiontable) located at 11.11.1.111 and inserting into 22.22.2.222(test.insertiontable). It's a thread based process, so the thread will keep on looking & transferring the records until all records are transferred.

and

Since I don't want to insert duplicate records into another table, I have a field in test.selectiontable, called DTSStatusType_ti which has initial value of 1 in the table. Hence,I am updating it after transferring the records to test.insertiontable as follows:

Line # 112  SelectQueueRS.updateInt( "DTSStatusType_ti", 3 );
Line # 113  SelectQueueRS.updateString( "Queued_DialerIP_vch", 22.22.2.222 );

The initial value of Queued_DialerIP_vch in test.selectiontable is 11.11.1.111.

Although the records are getting updated using above two lines of code in my code but I don't think it's an efficient way to update. Could anyone please suggest some efficient way to update the records and 100% make sure that these records are not duplicated ever. Please feel free to suggest any changes in my code. Thanks

Upvotes: 0

Views: 104

Answers (1)

mihu86
mihu86

Reputation: 1005

Instead of updating the ResultSet of the original query, you can:

  1. Start a transaction with connMain.setAutoCommit(false)
  2. If others can modify the data, you should use SELECT FOR UPDATE which locks the selected rows
  3. Collect the primary key ids from the query result
  4. Do the insert to the remote datasource
  5. Update the original table with one update query, by the collected ids (WHERE id IN (...))
  6. Commit the transaction with connMain.commit()

3 and 4 can be done in the same cycle. By the way you can also start a transaction on the remote datasource and do a batch insert. You should also be aware of doing transactions in try-catch blocks and closing your resources. This may help: http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

Upvotes: 1

Related Questions