Calling JdbcTemplate batchUpdate() with java ForkJoinPool

Question

I was looking for a way to update a large number of rows optimally , since orm operations turned out to be slow , Eventually the solution I have used currently is to wrap the db update via jdbc batch update inside a forkjoinpool task .(With ORM it was 20 sec to update in db , with this approach it came to 5 sec)

ForkJoinPool customThreadPool = new ForkJoinPool(8);

        try {
            customThreadPool.submit(
                    () ->{
                        String query = "update tableX set name = ? ";

                        jdbcTemplate.batchUpdate(query, new BatchPreparedStatementSetter() {
                            @Override
                            public void setValues(PreparedStatement ps, int i) throws SQLException {
                             
                              //compList is a list of ABC with 50,000+ elements
                              ABC abc = compList.get(i);

                                ps.setString(1, abc.getName());
                            
                            }
                            @Override
                            public int getBatchSize() {
                                return compList.size();
                            }
                        });

                    }

                    ).get();
            
            
        } catch (InterruptedException | ExecutionException e) {
            e.printStackTrace();
        }

Is it okay to do it like this ? I had seen a forkjoinpool example , however I am not sure how it is going to divide this particular db operation task.

I have tested the database changes , it is positive , but I have a couple of doubts on this :

1 - Are the different batches prepared operated in different threads ?

2 - I am also unsure how the db connection is managed in the multi-threaded environment

Any help would be appreciated.

Deepak Khillare Deepak Khillare · Accepted Answer · 2019-04-22T12:25:50

There are two things in your code I'd like to point out.

It seems jdbcTemplate object is shared by all thread here. This essentially means sharing the same connection. We should not use the same connection in multiple threads.
ForkJoinPool is meant for recursive tasks where you need to fork out the sub-tasks and join for them to complete. I don't think is the case here. If you need to use multi-threading, use a fixed thread pool and use connection pooling to grab connection in each thread.

Calling JdbcTemplate batchUpdate() with java ForkJoinPool

1 Answers