mybatis传入list参数查询

69 阅读 0 评论 46 点赞

我是靠谱客的博主害怕星月，最近开发中收集的这篇文章主要介绍mybatis传入list参数查询，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

list里面是一个实体类，根据实体类中的某几个字段进行查询

<select id="selectAllCeshi" parameterType="java.util.List" resultMap="BaseResultMapVO">
  SELECT * FROM operator_record oe
  LEFT JOIN device_record dr ON dr.record_id = oe.record_id and dr.function_type = 1
  WHERE  oe.operator_type = 1 and
  dr.record_id in
  <foreach collection="list" index="index" item="cs" separator="," >
    SELECT record_id FROM device_record WHERE fingerprint_id =#{cs.fingerprintId}
    AND operator_id = #{cs.operatorId} and device_id = #{cs.deviceId} and function_type = 1
  </foreach>
</select>

org.springframework.jdbc.BadSqlGrammarException:
### Error querying 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 'SELECT record_id FROM device_record WHERE fingerprint_id ='www'
AND operat' at line 6
### The error may exist in file [E:javagitmachineMgrtargetclassesmappingOperatorRecordDOMapper.xml]
### The error may involve com.asd.mapper.OperatorRecordDOMapper.selectAllCeshi-Inline
### The error occurred while setting parameters
### SQL: SELECT * FROM operator_record oe LEFT JOIN device_record dr ON dr.record_id = oe.record_id and dr.function_type = 1 WHERE oe.operator_type = 1 and dr.record_id in SELECT record_id FROM device_record WHERE fingerprint_id =? AND operator_id = ? and device_id = ? and function_type = 1 , SELECT record_id FROM device_record WHERE fingerprint_id =? AND operator_id = ? and device_id = ? and function_type = 1
### 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 'SELECT record_id FROM device_record WHERE fingerprint_id ='www'
AND operat' at line 6
; 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 'SELECT record_id FROM device_record WHERE fingerprint_id ='www'
AND operat' at line 6

   at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
   at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
   at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
   at com.sun.proxy.$Proxy32.selectList(Unknown Source)
   at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:231)
   at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
   at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
   at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
   at com.sun.proxy.$Proxy73.selectAllCeshi(Unknown Source)
   at com.asd.service.impl.OperationServiceImpl.selectecordCeshi(OperationServiceImpl.java:78)
   at com.asd.machineMgr.RecordTest.selectRecord(RecordTest.java:32)
   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(RunBeforeTestMethodCallbacks.java:75)
   at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.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:252)
   at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
   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(RunBeforeTestClassCallbacks.java:61)
   at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
   at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
   at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
   at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
   at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
   at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
   at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
   at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
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 'SELECT record_id FROM device_record WHERE fingerprint_id ='www'
AND operat' at line 6
   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:425)
   at com.mysql.jdbc.Util.getInstance(Util.java:408)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
   at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:1392)
   at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
   at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
   at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
   at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
   at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
   at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
   at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
   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:434)
   ... 36 more

仔细观察的童鞋已经看出来了，导致这个sql错误的原因是执行foreach里的查询语句时的前后缀与分隔符是错误的导致foreach里的sql无法正常执行

正确的应该是

<select id="selectAllCeshi" parameterType="java.util.List" resultMap="BaseResultMapVO">
  SELECT * FROM operator_record oe
  LEFT JOIN device_record dr ON dr.record_id = oe.record_id and dr.function_type = 1
  WHERE  oe.operator_type = 1 and
  dr.record_id in
  <foreach collection="list" index="index" item="cs" separator="),(" open="((" close="))">
    SELECT record_id FROM device_record WHERE fingerprint_id =#{cs.fingerprintId}
    AND operator_id = #{cs.operatorId} and device_id = #{cs.deviceId} and function_type = 1
  </foreach>
</select>