Java – how does spring JDBC temaplate send the full batch size to the DB2 server at one time?

Although JDBC template Batchupdate (...) is running. I can see that the DB row count increases gradually (by running count (*) in the table), initially 2K, then 3K and up to 10K 2k and 3K are not exact numbers. Sometimes I get 2357 and then 4567

I expect 10 K lines (batch size) to be submitted at one time In my understanding, if I get a row count of 0 at first, the next row count should be 10K I don't want to insert performance one by one, which is why using the batch update function doesn't seem to submit a shot

I want to send data (10K rows) to DB server only once for my batch size Why should I specify what in the configuration?

Here is how I write JDBC template batch updates The batch size is 10K

public void insertRows(...) { 
    ... 
    jdbcTemplate.batchUpdate(query,new BatchPreparedStatementSetter(){
    @Override public void

    setValues(PreparedStatement ps,int i) throws sqlException {
        ... 
    }

    @Override public int getBatchSize() { 
        if(data == null){ 
            return 0; 
        }
        return data.size(); 
    }
   }); 
}

Edit: add @ transactional to the isertrows method stiil, and I can see the same behavior It is submitted after 10K lines, but when I see that the count using ur (select count (*) from the URL) indicates that the data is gradually updated (2k, 4K, 10K) This means that the data is sent to the server in large blocks (probably one by one) How can I send everything at once This question suggests using rewritebackedstatements in mysql, and something similar to DB2

I'm using datasource to implement com ibm. db2. jcc. DB2BaseDataSource

Solution

How about the following methods? In your case, specify nupdates = 10000 I didn't try to test this If not, I ignore my answer

// the batch size is set in the BatchPreparedStatementSetter,the number of rows we want to process is equal to the nbUpdates parameter
    public int[] batchUpdate(String sql,final long nbUpdates,final BatchPreparedStatementSetter pss) throws DataAccessException {
        if (logger.isDebugEnabled()) {
            logger.debug("Executing sql batch update [" + sql + "]");
        }

        return (int[]) execute(sql,new PreparedStatementCallback() {
            public Object doInPreparedStatement(PreparedStatement ps) throws sqlException {
                try {
                    int batchSize = pss.getBatchSize();
                    InterruptibleBatchPreparedStatementSetter ipss = (pss instanceof InterruptibleBatchPreparedStatementSetter ? (InterruptibleBatchPreparedStatementSetter) pss
                            : null);
                    if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
                        List<Integer> rowsAffected = new ArrayList<Integer>();
                        for (int i = 1; i <= nbUpdates; i++) {
                            pss.setValues(ps,i - 1);
                            if (ipss != null && ipss.isBatchExhausted(i - 1)) {
                                if (logger.isDebugEnabled()) {
                                    int batchIdx = (i % batchSize == 0) ? i / batchSize : (i / batchSize) + 1;
                                    logger.debug("Batch exhausted - Sending last sql batch update #" + batchIdx);
                                }
                                int[] res = ps.executeBatch();
                                for (int j = 0; j < res.length; j++) {
                                    rowsAffected.add(res[j]);
                                }
                                break;
                            }
                            ps.addBatch();
                            if (i % batchSize == 0 || i == nbUpdates) {
                                if (logger.isDebugEnabled()) {
                                    int batchIdx = (i % batchSize == 0) ? i / batchSize : (i / batchSize) + 1;
                                    logger.debug("Sending sql batch update #" + batchIdx);
                                }
                                int[] res = ps.executeBatch();
                                for (int j = 0; j < res.length; j++) {
                                    rowsAffected.add(res[j]);
                                }
                            }
                        }
                        int[] result = new int[rowsAffected.size()];
                        for (int i = 0; i < result.length; i++) {
                            result[i] = rowsAffected.get(i).intValue();
                        }
                        return result;
                    } else {
                        List<Integer> rowsAffected = new ArrayList<Integer>();
                        for (int i = 0; i < nbUpdates; i++) {
                            pss.setValues(ps,i);
                            if (ipss != null && ipss.isBatchExhausted(i)) {
                                break;
                            }
                            rowsAffected.add(ps.executeUpdate());
                        }
                        int[] rowsAffectedArray = new int[rowsAffected.size()];
                        for (int i = 0; i < rowsAffectedArray.length; i++) {
                            rowsAffectedArray[i] = rowsAffected.get(i);
                        }
                        return rowsAffectedArray;
                    }
                } finally {
                    if (pss instanceof ParameterDisposer) {
                        ((ParameterDisposer) pss).cleanupParameters();
                    }
                }
            }
        });
    }