java - Spring JdbcTemplate butchUpdate更新org.springframework.jdbc.BadSqlGrammarException :

Question

我几天前开始学习Spring MVC，现在我正在尝试做一个butchUpdate。我找到了article如何执行此操作，但出现以下异常。我在内存数据库中使用 HSQLDB。这是我的代码:

public void updateStockBatch(List<Product> 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() < noOfUnits) {
                    ps.setLong(1, product.getUnitsInStock() + addQuantity);
                    ps.setString(2, product.getProductId());
                    batchSize++;
                }
            }
        }
        @Override
        public int getBatchSize() {
            return batchSize;
        }
    });
}

<小时/>

    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)

Answer 1

您误解了它的工作原理。 batchUpdate()的原理是执行相同的参数化SQL查询N次。

BatchPreparedStatementSetter 首先由 JdbcTemplate 调用来了解批量大小。假设它返回 10。然后 JdbcTemplate 将调用 setValues() 10 次(先是 0，然后是 1，然后是 2，等等，直到 9)。

您的代码返回 0 作为批量大小，这没有意义，并且其 setValues 方法在同一条语句上多次设置相同的参数，这也没有意义。

正确的实现是:

public void updateStockBatch(List<Product> allProducts, int addQuantity, int noOfUnits) {
    String sql = "UPDATE products SET UNITS_IN_STOCK = ? WHERE ID = ?";
    List<Product> productsToUpdate = 
        allProducts.stream()
                   .filter(p -> p.getUnitsInStock() < noOfUnits)
                   .collect(Collectors.toList());
    if (!productsToUpdate.isEmpty()) {

        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

            @Override
            public int getBatchSize() {
                return productsToUpdate.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {

                Product product : productsToUpdate.get(i);
                ps.setLong(1, product.getUnitsInStock() + addQuantity);
                ps.setString(2, product.getProductId());
            }
        });
    }
}