<p dir="auto"><strong><a href="https://jira.spring.io/secure/ViewProfile.jspa?name=nico.fabre" rel="nofollow">Nicolas FABRE</a></strong> opened <strong><a href="https://jira.spring.io/browse/SPR-6334?redirect=false" rel="nofollow">SPR-6334</a></strong> and commented</p>
<p dir="auto">Using org.springframework.jdbc.core.JdbcTemplate.batchUpdate(String sql, BatchPreparedStatementSetter pss), if the number of rows I want to insert (update or delete) is greater than the batch size I define in the BatchPreparedStatementSetter, the rows after the batch size limit are not inserted. However if I have a huge amount of rows to insert (for example 100000), I think it is not good to set the batch size to 100000. The database could be oppressed if it receives 100000 rows to process on a single call. Am I wrong ?</p>
<p dir="auto">So would it be possible to use batchUpdate with a number of updates to be performed not equals to the batch size ? May I request the creation of the following method to manage this problem ?</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="        // 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(&quot;Executing SQL batch update [&quot; + sql + &quot;]&quot;);
		}

		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&lt;Integer&gt; rowsAffected = new ArrayList&lt;Integer&gt;();
						for (int i = 1; i &lt;= nbUpdates; i++) {
							pss.setValues(ps, i - 1);
							if (ipss != null &amp;&amp; ipss.isBatchExhausted(i - 1)) {
								if (logger.isDebugEnabled()) {
									int batchIdx = (i % batchSize == 0) ? i / batchSize : (i / batchSize) + 1;
									logger.debug(&quot;Batch exhausted - Sending last SQL batch update #&quot; + batchIdx);
								}
								int[] res = ps.executeBatch();
								for (int j = 0; j &lt; 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(&quot;Sending SQL batch update #&quot; + batchIdx);
								}
								int[] res = ps.executeBatch();
								for (int j = 0; j &lt; res.length; j++) {
									rowsAffected.add(res[j]);
								}
							}
						}
						int[] result = new int[rowsAffected.size()];
						for (int i = 0; i &lt; result.length; i++) {
							result[i] = rowsAffected.get(i).intValue();
						}
						return result;
					} else {
						List&lt;Integer&gt; rowsAffected = new ArrayList&lt;Integer&gt;();
						for (int i = 0; i &lt; nbUpdates; i++) {
							pss.setValues(ps, i);
							if (ipss != null &amp;&amp; ipss.isBatchExhausted(i)) {
								break;
							}
							rowsAffected.add(ps.executeUpdate());
						}
						int[] rowsAffectedArray = new int[rowsAffected.size()];
						for (int i = 0; i &lt; rowsAffectedArray.length; i++) {
							rowsAffectedArray[i] = rowsAffected.get(i);
						}
						return rowsAffectedArray;
					}
				} finally {
					if (pss instanceof ParameterDisposer) {
						((ParameterDisposer) pss).cleanupParameters();
					}
				}
			}
		});
	}"><pre class="notranslate"><code class="notranslate">        // 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&lt;Integer&gt; rowsAffected = new ArrayList&lt;Integer&gt;();
						for (int i = 1; i &lt;= nbUpdates; i++) {
							pss.setValues(ps, i - 1);
							if (ipss != null &amp;&amp; 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 &lt; 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 &lt; res.length; j++) {
									rowsAffected.add(res[j]);
								}
							}
						}
						int[] result = new int[rowsAffected.size()];
						for (int i = 0; i &lt; result.length; i++) {
							result[i] = rowsAffected.get(i).intValue();
						}
						return result;
					} else {
						List&lt;Integer&gt; rowsAffected = new ArrayList&lt;Integer&gt;();
						for (int i = 0; i &lt; nbUpdates; i++) {
							pss.setValues(ps, i);
							if (ipss != null &amp;&amp; ipss.isBatchExhausted(i)) {
								break;
							}
							rowsAffected.add(ps.executeUpdate());
						}
						int[] rowsAffectedArray = new int[rowsAffected.size()];
						for (int i = 0; i &lt; rowsAffectedArray.length; i++) {
							rowsAffectedArray[i] = rowsAffected.get(i);
						}
						return rowsAffectedArray;
					}
				} finally {
					if (pss instanceof ParameterDisposer) {
						((ParameterDisposer) pss).cleanupParameters();
					}
				}
			}
		});
	}
</code></pre></div>
<p dir="auto">Thanks to this method I can process my 100000 rows with a batchSize = 5000 for example. It avoid to manage sub-collections in the DAO method to invoke the batchUpdate method with just 5000 rows.</p>
<p dir="auto">Thanks in advance for the attention which will be given to this issue.</p>
<p dir="auto">Nicolas</p>
<hr>
<p dir="auto"><strong>Affects:</strong> 3.0 RC1</p>
<p dir="auto"><strong>Attachments:</strong></p>
<ul dir="auto">
<li><a href="https://jira.spring.io/secure/attachment/17624/spring-contribution-JIRA-SPR-6334.zip" rel="nofollow">spring-contribution-JIRA-SPR-6334.zip</a> (<em>13.07 kB</em>)</li>
</ul>
<p dir="auto"><strong>Issue Links:</strong></p>
<ul dir="auto">
<li><a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="398105733" data-permission-text="Title is private" data-url="https://github.com/spring-projects/spring-framework/issues/11949" data-hovercard-type="issue" data-hovercard-url="/spring-projects/spring-framework/issues/11949/hovercard" href="https://github.com/spring-projects/spring-framework/issues/11949">#11949</a> Extend JdbcTemplate to allow larger batch updates (<em><strong>"is duplicated by"</strong></em>)</li>
</ul>
<p dir="auto"><strong>Referenced from:</strong> commits <a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/spring-projects/spring-framework/commit/0adcb2ad2ea5bdf85d27e9c279cf165db1eeb33c/hovercard" href="https://github.com/spring-projects/spring-framework/commit/0adcb2ad2ea5bdf85d27e9c279cf165db1eeb33c"><tt>0adcb2a</tt></a></p>
<p dir="auto">2 votes, 3 watchers</p>
<p dir="auto"><strong><a href="https://jira.spring.io/secure/ViewProfile.jspa?name=quaff" rel="nofollow">Yanming Zhou</a></strong> opened <strong><a href="https://jira.spring.io/browse/SPR-8213?redirect=false" rel="nofollow">SPR-8213</a></strong> and commented</p>
<p dir="auto">I have the same problem with <a href="http://forum.springsource.org/showthread.php?t=105476" rel="nofollow">http://forum.springsource.org/showthread.php?t=105476</a></p>
<hr>
<p dir="auto"><strong>Affects:</strong> 3.0.5</p>
<p dir="auto"><strong>Reference URL:</strong> <a href="http://forum.springsource.org/showthread.php?t=105476" rel="nofollow">http://forum.springsource.org/showthread.php?t=105476</a></p>
<p dir="auto"><strong>Issue Links:</strong></p>
<ul dir="auto">
<li><a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="398089277" data-permission-text="Title is private" data-url="https://github.com/spring-projects/spring-framework/issues/9610" data-hovercard-type="issue" data-hovercard-url="/spring-projects/spring-framework/issues/9610/hovercard" href="https://github.com/spring-projects/spring-framework/issues/9610">#9610</a> Doc: <code class="notranslate">@Autowired</code> not working in BeanFactoryPostProcessor (<em><strong>"duplicates"</strong></em>)</li>
</ul>