Inserting or updating multiple records in database in a multi-threaded way in java

Question

I am updating multiple records in database. Now whenever UI sends the list of records to be updated, I have to just update those records in database. I am using JDBC template for that.

Earlier Case

Earlier what I was whenever I got records from UI, I just do

jdbcTemplate.batchUpdate(Query, List<object[]> params)

Whenever there was an exception, I used to rollback whole transaction.

(Updated : Is batchUpdate multi-threaded or faster than batch update in some way?)

Later Case

But later as requirement changed whenever there was exception. So, whenever there is some exception, I should know which records failed to update. So I had to sent the records back to UI in case of exception with a reason, why did they failed.

so I had to do something similar to this:

for(Record record : RecordList)
{
   try{
       jdbcTemplate.update(sql, Object[] param)
   }catch(Exception ex){
       record.setReason("Exception : "+ex.getMessage());
       continue;
   }
}

So am I doing this in right fashion, by using the loop?

If yes, can someone suggest me how to make it multi-threaded. Or is there anything wrong in this case. To be true, I was hesitating to use try catch block inside the loop :( .

Please correct me, really need to learn a better way because I myself feel, there must be a better way , thanks.

instead of doing batch updates in separate threads that touch on the same rows, why don't you try to split up the work in a non-conflicting way across your threads? — stevevls
– stevevls, Commented Sep 22, 2011 at 8:09

swanliu · Accepted Answer · 2011-09-22 08:31:34Z

1

make all update-operation to a Collection Callable<>, send it to java.util.concurrent.ThreadPoolExecutor. the pool is multithreaded.

make Callable:

class UpdateTask implements Callable<Exception> {
  //constructor with jdbctemplate,sql,param goes here.
  @Override
    public Exception call() throws Exception {
        try{
              jdbcTemplate.update(sql, Object[] param)
            }catch(Exception ex){
                   return ex;
           }

        return null;
    }

invoke call:

<T> List<Future<T>> java.util.concurrent.ExecutorService.invokeAll(Collection<? extends Callable<T>> tasks) throws InterruptedException

answered Sep 22, 2011 at 8:31

swanliu

7913 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Naveen Babu · Accepted Answer · 2011-09-22 08:17:27Z

1

Your case looks like you need to use validation in java and filter out the valid data alone and send to the data base for updating.

BO layer -> filter out the Valid Record. -> Invalid Record should be send back with some validation text.

In DAO layer -> batch update your RecordList

This will give you the best performance.

Never use database insert exception as a validation mechanism.

Exceptions are costly as the stack trace has to be created
Connection to database is another costly process and will take time to get a connection
Java If-Else will run much faster for same data-base validation

answered Sep 22, 2011 at 8:17

Naveen Babu

1,5741 gold badge14 silver badges38 bronze badges

2 Comments

kinshuk4 Over a year ago

Yes I agree to all your points, but my friend I don't have validation logic in place. Exceptions are the conditions which we couldn't think off where the code may fail. Thanks though, not applicable to my case.

Naveen Babu Over a year ago

@kinshuk4 1st I would try to catch maximum known issues in validation. Then I will expect exception for unknown causes which we cant predict. Still for your case, you can do one thing u can have both implementations <batch insert logic> on exception <call individual insert logic>. Not sure if code review team will be pleased with the logic though

Collectives™ on Stack Overflow

Inserting or updating multiple records in database in a multi-threaded way in java

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related