mybatis mysql 批量_mysql使用mybatis批量更新问题

一、好言

在光芒万丈之前，我们都要欣然接受眼下的难堪和不易，接受一个人的孤独和偶尔的无助。

二、背景

其实这是在处理一个问题的时候，需要批量更新数据，结果这个批量更新数据是有问题的，所以改了顺便记载下。

问题

SQL: update t_user set user_name = ?, age = ?, login_token = ? where login_token = ? ; update t_user set

user_name = ?, age = ?, login_token = ? where login_token = ? ; update t_user set user_name = ?, age =

?, login_token = ? where login_token = ?

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

'updatet_user set user_name = 'hahahha' at line 1

; 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 t_user set device_id = hahahha' at line 1

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLE

xceptionTranslator.java:231)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLE

xceptionTranslator.java:73)

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.ja

va:74)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:421)

at com.sun.proxy.$Proxy40.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.$Proxy45.bathUpdateUserInfos(Unknown Source)

at com.mouse.moon.user.service.UserService.bathUpdateUserInfos(UserService.java:74)

at at com.mouse.moon.user.service.UserService$$FastClassBySpringCGLIB$$6d6c351

c.invoke()

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy

.java:720)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)

at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInt

erceptor.java:99)

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(Transactio

nAspectSupport.java:281)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:

179)

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.ja

va:655)

at at com.mouse.moon.user.service.UserService$$EnhancerBySpringCGLIB$$e3e29c1

c.bathUpdateUserInfos()

at test.userServicetest.testUpdaterUserInfo(UserServiceTest.java:71)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTes

tMethodCallbacks.java:75)

at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMe

thodCallbacks.java:86)

at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)

at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:2

54)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:8

9)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)

at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestC

lassCallbacks.java:61)

at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClas

sCallbacks.java:70)

at org.junit.runners.ParentRunner.run(ParentRunner.java:363)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:193)

at org.junit.runner.JUnitCore.run(JUnitCore.java:137)

at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)

at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)

at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:253)

at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)

Caused by: 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 t_user set user_name = 'hahahha' at line 1

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

at java.lang.reflect.Constructor.newInstance(Constructor.java:423)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)

at com.mysql.jdbc.Util.getInstance(Util.java:387)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)

at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:4

5)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:73)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)

at com.sun.proxy.$Proxy73.update(Unknown Source)

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115)

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:408)

... 50 more

解决

jdbc:mysql://localhost:3306/users?characterEncoding=utf-8&autoReconnect=true&allowMultiQueries=true

四:批量处理代码

如果传入两个参数，mapper层如下：

int batchInsertData(@Param("versionList") List userList, @Param("id")Long id);

insert into t_User (id, version,

ostype, createTime,

updateTime)

values

( #{id,jdbcType=BIGINT}, #{item.version,jdbcType=VARCHAR},

#{item.ostype,jdbcType=VARCHAR}, 0, now(),now())

五、set的批量处理

List selectUserInfoByIdSet(@Param("set") Set userIdSet,@Param("sex")String sex);

select * from t_user

where sex= #{sex} and user_id IN

#{item}