代码之家  ›  专栏  ›  技术社区  ›  traczovsky

MyBatis foreach更新无法查看集合中的所有项参数

ibatis mybatis h2 prepared-statement foreach
  •  0
  • traczovsky  · 技术社区  · 6 年前

    我试图在我的映射器中使用@Update中的foreach循环,但出乎意料的是,mybatis在解析集合中的所有参数时遇到了问题。以下详细信息: 

      @Update("<script>"
              + "<foreach collection='objects' item='item' separator=';'>"
              + "UPDATE some_table "
              + "SET field_x=#{item.x}, "
              + "field_y=#{item.y} "
              + "WHERE field_z=#{item.z} AND field_a=#{item.a}"
              + "</foreach>"
              + "</script>")
  void update(@Param("objects") List<SomeObject> objects);

    以及: 

    public class SomeObject {

  private String z;
  private SomeEnum a;
  private BigDecimal x;
  private Integer y;
}

    返回的方法调用: 

    org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='__frch_item_1.dailyAmount', mode=IN, javaType=class java.math.BigDecimal, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #5 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.h2.jdbc.JdbcSQLException: Invalid value "5" for parameter "parameterIndex" [90008-196]

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy33.update(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy43.updateLimits(Unknown Source)
at pl.raiffeisen.ekd.blik.core.persistence.DataProvider.updateLimits(DataProvider.java:76)
at pl.raiffeisen.ekd.blik.api.rest.settings.SettingsController.updateLimits(SettingsController.java:136)
at pl.raiffeisen.ekd.blik.test.rest.SettingsControllerTest.testUpdateLimitsSuccessPath(SettingsControllerTest.java:532)
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.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
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: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='__frch_item_1.dailyAmount', mode=IN, javaType=class java.math.BigDecimal, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #5 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.h2.jdbc.JdbcSQLException: Invalid value "5" for parameter "parameterIndex" [90008-196]
    at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:89)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:93)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:64)
    at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:86)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    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:433)
    ... 38 more
Caused by: org.apache.ibatis.type.TypeException: Error setting non null for parameter #5 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.h2.jdbc.JdbcSQLException: Invalid value "5" for parameter "parameterIndex" [90008-196]
    at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:55)
    at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:87)
    ... 50 more
Caused by: org.h2.jdbc.JdbcSQLException: Invalid value "5" for parameter "parameterIndex" [90008-196]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.getInvalidValueException(DbException.java:228)
    at org.h2.jdbc.JdbcPreparedStatement.setParameter(JdbcPreparedStatement.java:1444)
    at org.h2.jdbc.JdbcPreparedStatement.setBigDecimal(JdbcPreparedStatement.java:384)
    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.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:67)
    at com.sun.proxy.$Proxy58.setBigDecimal(Unknown Source)
    at org.apache.ibatis.type.BigDecimalTypeHandler.setNonNullParameter(BigDecimalTypeHandler.java:32)
    at org.apache.ibatis.type.BigDecimalTypeHandler.setNonNullParameter(BigDecimalTypeHandler.java:27)
    at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:53)
    ... 51 more

    调试后,我看到了:(at:org.h2.jdbc.JdbcPreparedStatement:1002) 

    this = prep19: UPDATE some_table SET field_x=?, field_y=? WHERE field_z=? AND field_a=? ;
UPDATE transaction_limit SET field_x=?, field_y=? WHERE field_z=? AND field_a=? ;
UPDATE transaction_limit SET field_x=?, field_y=? WHERE field_z=? AND type=? ;
{1: 250.00, 2: 1, 3: 'qwerty123', 4: 'VALUE1'}

    应该是这样的:(示例值) 

    {1: 250.00, 2: 1, 3: 'qwerty123', 4: 'VALUE1', 
 5: 750.00, 6: 4, 7: 'qwerty123', 8: 'VALUE2', 
 9: 500.00, 10: 3, 11: 'qwerty123', 12: 'VALUE3'}

    有人能告诉我为什么只有一个对象从列表映射而不是所有三个对象(将3个元素列表传递给方法)?

    1 回复  |  直到 6 年前
        1
  •  0
  •   traczovsky    6 年前

    我已经解决了这个问题。数据库H2显然不支持foreach脚本。使用PostgreSQL,一切正常。

    推荐文章
    RonnieT  ·  MySQL插入双引号PHP
    6 年前
    janusz  ·  Php Mysql“WHERE INSTR…”准备好的语句[已关闭]
    7 年前
    3D-kreativ  ·  如何从$stmt->get\u result()[重复]中检索单个值
    7 年前
    MBJH  ·  准备好的报表。executeUpdate()不影响数据库
    7 年前
    Raptiku  ·  从多个数据库执行多个语句
    7 年前
    Heratom  ·  准备好的报表。addBatch()添加空参数
    7 年前
    Steve Mack  ·  Mysqli绑定参数不工作
    7 年前
    deR_Ed  ·  Perl DBI(MySQL)将单引号而不是实际参数放在准备好的语句中
    7 年前
    bem22  ·  准备好的报表是这样工作的吗?
    7 年前
    AashikP  ·  自定义mysqli类中bind\u param的参数计数错误
    7 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号