title: Spring JdbcTemplate butchUpdate UPDATE org.springframework.jdbc.BadSqlGrammarException:
body: <p>I have started learning Spring MVC a few days ago and now I am trying to do a butchUpdate. I have found an <a href="https://www.mkyong.com/spring/spring-jdbctemplate-batchupdate-example/" rel="nofollow noreferrer">article</a> how to do it but I get an exception that is below.I am using HSQLDB in memory database.
  Here is my code:</p>
  
  <pre><code>public void updateStockBatch(List&lt;Product&gt; allProducts, int addQuantity, int noOfUnits) {
      String SQL = "UPDATE products SET UNITS_IN_STOCK = ? WHERE ID = ?";
      jdbcTemplate.batchUpdate(SQL, new BatchPreparedStatementSetter() {
          int batchSize = 0;
          @Override
          public void setValues(PreparedStatement ps, int i) throws SQLException {
              for (Product product : allProducts) {
                  if (product.getUnitsInStock() &lt; noOfUnits) {
                      ps.setLong(1, product.getUnitsInStock() + addQuantity);
                      ps.setString(2, product.getProductId());
                      batchSize++;
                  }
              }
          }
          @Override
          public int getBatchSize() {
              return batchSize;
          }
      });
  }
  </code></pre>
  
  <hr>
  
  <pre><code>    Type Exception Report
  
  Message Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE products SET UNITS_IN_STOCK = ? WHERE ID = ?]; nested exception is java.sql.SQLException: statement is not in batch mode
  
  Description The server encountered an unexpected condition that prevented it from fulfilling the request.
  
  Exception
  
  org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE products SET UNITS_IN_STOCK = ? WHERE ID = ?]; nested exception is java.sql.SQLException: statement is not in batch mode
      org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982)
      org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
      org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
      org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
  Root Cause
  
  org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE products SET UNITS_IN_STOCK =? WHERE ID = ?]; nested exception is java.sql.SQLException: statement is not in batch mode
      org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
      org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
      org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
      org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
      org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
      org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:662)
      org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:950)
      com.packt.webstore.domain.repository.impl.InMemoryProductRepository.updateStockBatch(InMemoryProductRepository.java:53)
      com.packt.webstore.domain.repository.impl.ProductProductServiceImpl.updateAllStock(ProductProductServiceImpl.java:30)
      com.packt.webstore.controller.ProductController.updateStock(ProductController.java:33)
      sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      java.lang.reflect.Method.invoke(Method.java:498)
      org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
      org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
      org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
      org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
      org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
      org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
      org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
      org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
      org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
      org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
      org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
      org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
  Root Cause
  
  java.sql.SQLException: statement is not in batch mode
      org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
      org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
      org.hsqldb.jdbc.JDBCUtil.sqlExceptionSQL(Unknown Source)
      org.hsqldb.jdbc.JDBCPreparedStatement.executeBatch(Unknown Source)
      org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:966)
      org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:950)
      org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
      org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:662)
      org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:950)
      com.packt.webstore.domain.repository.impl.InMemoryProductRepository.updateStockBatch(InMemoryProductRepository.java:53)
      com.packt.webstore.domain.repository.impl.ProductProductServiceImpl.updateAllStock(ProductProductServiceImpl.java:30)
      com.packt.webstore.controller.ProductController.updateStock(ProductController.java:33)
      sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      java.lang.reflect.Method.invoke(Method.java:498)
      org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
      org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
      org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
      org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
      org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
      org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
      org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
      org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
      org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
      org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
      org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
      org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
  Root Cause
  
  org.hsqldb.HsqlException: statement is not in batch mode
      org.hsqldb.error.Error.error(Unknown Source)
      org.hsqldb.error.Error.error(Unknown Source)
      org.hsqldb.jdbc.JDBCUtil.sqlExceptionSQL(Unknown Source)
      org.hsqldb.jdbc.JDBCPreparedStatement.executeBatch(Unknown Source)
      org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:966)
      org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:950)
      org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
      org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:662)
      org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:950)
      com.packt.webstore.domain.repository.impl.InMemoryProductRepository.updateStockBatch(InMemoryProductRepository.java:53)
      com.packt.webstore.domain.repository.impl.ProductProductServiceImpl.updateAllStock(ProductProductServiceImpl.java:30)
      com.packt.webstore.controller.ProductController.updateStock(ProductController.java:33)
      sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      java.lang.reflect.Method.invoke(Method.java:498)
      org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
      org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
      org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
      org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
      org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
      org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
      org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
      org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
      org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
      org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
      org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
      org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
  </code></pre>