首页 > 代码库 > MySQL+MyBatis下批量修改数据的问题

MySQL+MyBatis下批量修改数据的问题

今天处理数据批量的更新,场景是这样子的,web站管理的字典功能,需要添加一个记录的整体描述,以及详细内容的描述。一个字典整体概述只有一组信息,但是其详细内容,会有很多项,不确定。

这个场景,在关系型数据库操作中,就是典型的1vN的问题,即一对多的问题。

 

做内容修改时,涉及到批量的更新过程。这里,只针对具体的问题描述细节,不过多介绍字典的设计。

 

字典的查询没有问题,mybatis的mapper函数如下:

  <resultMap id="FullConfResultMap" type="com.tg.ecs.system.model.SysConfElement" >
    <!--   
              注意,这种基于has-one,以及has-many的模式,会造成结果映射出错。个人理解,这种复合查询中,顶层元素中
              必须有基本数据类型的成员,否则会出现结果个数映射转换失败。
    <association property="scc" javaType="com.tg.ecs.system.model.SysConfCategory">
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="code_name" property="codeName" jdbcType="VARCHAR" />
        <result column="info_name" property="infoName" jdbcType="VARCHAR" />
        <result column="remark" property="remark" jdbcType="VARCHAR" />
        <result column="who_cm" property="whoCm" jdbcType="INTEGER" />
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
        <result column="in_use" property="inUse" jdbcType="BIT" />
    </association>
    -->
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="code_name" property="codeName" jdbcType="VARCHAR" />
    <result column="info_name" property="infoName" jdbcType="VARCHAR" />
    <result column="c_remark" property="remark" jdbcType="VARCHAR" />
    <result column="who_cm" property="whoCm" jdbcType="INTEGER" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="in_use" property="inUse" jdbcType="BIT" />
    <collection property="scds" ofType="com.tg.ecs.system.model.SysConfDetail">
        <result column="did" property="id" jdbcType="INTEGER" />
        <result column="code" property="code" jdbcType="VARCHAR" />
        <result column="info" property="info" jdbcType="VARCHAR" />
        <result column="d_remark" property="remark" jdbcType="VARCHAR" />
        <result column="category_id" property="categoryId" jdbcType="INTEGER" />
    </collection>
  </resultMap>
  <select id="selectOneFullConf" resultMap="FullConfResultMap">
      select tscc.id, tscc.code_name, tscc.info_name, 
             tscc.remark as c_remark,
             tscc.who_cm, tscc.create_time, tscc.update_time, tscc.in_use,
             tscd.id as did, 
             tscd.code, tscd.info, 
           tscd.remark as d_remark, 
           tscd.category_id 
         from sys_conf_category tscc, sys_conf_detail tscd
      where tscd.category_id = tscc.id 
         and tscc.id = #{cid, jdbcType=INTEGER}
  </select> 

 

这里,我的SysConfDetail的POJO代码如下:

public class SysConfDetail {
    
    private Integer id;
    
    private String code;

    private String info;

    private String remark;

    private Integer categoryId;
   
    public String getInfo() {
        return info;
    }

    public void setInfo(String info) {
        this.info = info == null ? null : info.trim();
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark == null ? null : remark.trim();
    }

    public Integer getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }

    /**
     * @return the code
     */
    public String getCode() {
        return code;
    }

    /**
     * @param code the code to set
     */
    public void setCode(String code) {
        this.code = code;
    }

    /**
     * @return the id
     */
    public Integer getId() {
        return id;
    }

    /**
     * @param id the id to set
     */
    public void setId(Integer id) {
        this.id = id;
    }
}

就是查询中collection的数据类型。

 

我的设计中,字典的操作是下面这个样子的,请看下图:

技术分享

当我更新上图中的内容后,点击保存,我的WEB应用后台总是爆出一个很奇怪的问题:

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near UPDATE sys_conf_detail
         SET code = 2,  
           info = ?o§?“?èˉ| at line 7
### The error may involve com.tg.ecs.system.dao.SysConfDetailMapper.updateOneConfDetail-Inline
### The error occurred while setting parameters
### SQL: UPDATE sys_conf_detail          SET code = ?,              info = ?,              remark = ?             WHERE id = ? and category_id = ?        ;            UPDATE sys_conf_detail          SET code = ?,              info = ?,              remark = ?             WHERE id = ? and category_id = ?        ;            UPDATE sys_conf_detail          SET code = ?,              info = ?,              remark = ?             WHERE id = ? and category_id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near UPDATE sys_conf_detail
         SET code = 2,  
           info = ?o§?“?èˉ| at line 7
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near UPDATE sys_conf_detail
         SET code = 2,  
           info = ?o§?“?èˉ| at line 7
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:421)
    at com.sun.proxy.$Proxy11.update(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:270)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:55)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
    at com.sun.proxy.$Proxy42.updateOneConfDetail(Unknown Source)
    at com.tg.ecs.system.service.impl.SysConfDetailService.updateOneConfDetail(SysConfDetailService.java:55)
    at com.tg.ecs.system.service.impl.SysConfDetailService$$FastClassBySpringCGLIB$$373671b0.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:700)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:633)
    at com.tg.ecs.system.service.impl.SysConfDetailService$$EnhancerBySpringCGLIB$$f6432ee7.updateOneConfDetail(<generated>)
    at com.tg.ecs.system.service.impl.SysConfDetailService$$FastClassBySpringCGLIB$$373671b0.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:700)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:633)
    at com.tg.ecs.system.service.impl.SysConfDetailService$$EnhancerBySpringCGLIB$$b9909d6d.updateOneConfDetail(<generated>)
    at com.tg.ecs.system.controller.ConfController.saveCategory(ConfController.java:223)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161)
    at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:205)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)
    at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
    at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)
    at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)
    at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154)
    at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473)
    at org.glassfish.jersey.servlet.ServletContainer.serviceImpl(ServletContainer.java:408)
    at org.glassfish.jersey.servlet.ServletContainer.doFilter(ServletContainer.java:583)
    at org.glassfish.jersey.servlet.ServletContainer.doFilter(ServletContainer.java:524)
    at org.glassfish.jersey.servlet.ServletContainer.doFilter(ServletContainer.java:461)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at com.tg.ecs.core.xss.XssFilter.doFilter(XssFilter.java:43)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61)
    at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
    at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
    at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
    at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
    at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
    at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
    at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
    at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
    at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
    at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
    at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
    at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
    at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
    at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
    at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
    at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
    at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)
    at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)
    at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1502)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1458)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:744)

 

分析上面的错误日志,开始总是不知道什么原因,以为是其中的info字段乱码。分析自己的update的SQL语句:

<update id="updateOneConfDetail" parameterType="java.util.List">
    <!-- 注意这里,index以及open,close没有用,所以就不做配置了,默认的 -->
      <foreach collection="scds" item="ocd" separator=";">  
        UPDATE sys_conf_detail
        <set>  
           code = #{ocd.code},  
           info = #{ocd.info},  
           remark = #{ocd.remark}
        </set>  
        <where>  
           id = #{ocd.id, jdbcType=INTEGER} and category_id = #{ocd.categoryId, jdbcType=INTEGER}  
        </where>  
      </foreach>                
  </update>

 

上面的SQL语句,红色部分separator=“;”,是重点。 回想到官方的描述,关于一行SQL语句中执行多个子SQL的时候(比如这里,MySQL默认是用分号最为一个语句的结束),会出问题。请参考MySQL官方文档描述

allowMultiQueries

Allow the use of ‘;‘ to delimit multiple queries during one statement (true/false), defaults to ‘false‘, and does not affect the addBatch() and executeBatch() methods, which instead rely on rewriteBatchStatements.

Default: false

Since version: 3.1.1

这个是问题的关键,我们可以在jdbc的地址url中添加一下这个allowMultiQueries=true的参数。

jdbc.url=jdbc\:mysql\://10.90.7.2\:3306/rdcenter?useUnicode\=true&characterEncoding\=UTF-8&zeroDateTimeBehavior\=convertToNull&allowMultiQueries\=true

 

修改后,再次启动我的应用,就不再报上述的exception问题了。

 

总结:

mysql数据库,要在一个statement中执行多条sql子句的时候,需要打开这个功能,默认该功能是关闭的,也就是在url地址栏开启allowMultiQueries=true.

 

MySQL+MyBatis下批量修改数据的问题