NativeTaskQuery and list-parameters?

[Markus Schulz]

hello,

how can i add a list as parameter to a native query?

for example:

return taskService.createNativeTaskQuery().sql(

"select * from act_ru_task task "

+ "left join act_ru_identitylink idlink on task.id_ = idlink.task_id_ AND idlink.type_ = 'candidate' "

+ "WHERE task.proc_inst_id_ IN (#{parentProcessInstanceIDs}) "

+ "AND (task.assignee_ IS NULL OR task.assignee_ = #{actorLogin} OR idlink.user_id_ = #{actorLogin} OR idlink.group_id_ IN (#{actorRoles}))"

)

.parameter("actorLogin", actor.getLogin())

.parameter("actorRoles", actor.getRoles().toArray(new String[actor.getRoles().size()]))

.parameter("parentProcessInstanceIDs", procInstanceIDs.toArray(new String[procInstanceIDs.size()]))

.list();

i've tried many things (List<String>, String[] and Object[]) without luck. Every time i got an exception:

13:28:12,215 SEVERE [org.camunda.bpm.engine.impl.interceptor.CommandContext] (http-/0.0.0.0:8080-2) Error while closing command context: org.apache.ibatis.exceptions.PersistenceException: 

### Error querying database.  Cause: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of [Ljava.lang.String;. Use setObject() with an explicit Types value to specify the type to use.

### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Task.xml

### The error may involve org.camunda.bpm.engine.impl.persistence.entity.TaskEntity.selectTaskByNativeQuery-Inline

### The error occurred while setting parameters

### SQL: select * from act_ru_task task left join act_ru_identitylink idlink on task.id_ = idlink.task_id_ AND idlink.type_ = 'candidate' WHERE task.proc_inst_id_ IN (?) AND (task.assignee_ IS NULL OR task.assignee_ = ? OR idlink.user_id_ = ? OR idlink.group_id_ IN (?))

### Cause: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of [Ljava.lang.String;. Use setObject() with an explicit Types value to specify the type to use.

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)

at org.camunda.bpm.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:317) [camunda-engine-7.1.0-Final.jar:7.1.0-Final]

at org.camunda.bpm.engine.impl.persistence.entity.TaskManager.findTasksByNativeQuery(TaskManager.java:126) [camunda-engine-7.1.0-Final.jar:7.1.0-Final]

at org.camunda.bpm.engine.impl.NativeTaskQueryImpl.executeList(NativeTaskQueryImpl.java:28) [camunda-engine-7.1.0-Final.jar:7.1.0-Final]

at org.camunda.bpm.engine.impl.AbstractNativeQuery.execute(AbstractNativeQuery.java:115) [camunda-engine-7.1.0-Final.jar:7.1.0-Final]

at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24) [camunda-engine-7.1.0-Final.jar:7.1.0-Final]

at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:90) [camunda-engine-7.1.0-Final.jar:7.1.0-Final]

at org.camunda.bpm.engine.impl.interceptor.JtaTransactionInterceptor.execute(JtaTransactionInterceptor.java:59) [camunda-engine-7.1.0-Final.jar:7.1.0-Final]

at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32) [camunda-engine-7.1.0-Final.jar:7.1.0-Final]

at org.camunda.bpm.engine.impl.AbstractNativeQuery.list(AbstractNativeQuery.java:89) [camunda-engine-7.1.0-Final.jar:7.1.0-Final]

at org.sis.apo2.camunda.services.tasks.MyPersonalTasklist.fetchAll(MyPersonalTasklist.java:55) [foundation-2206055524065067930.jar:]

at org.sis.apo2.camunda.services.tasks.MyPersonalTasklist$Proxy$_$$_WeldClientProxy.fetchAll(MyPersonalTasklist$Proxy$_$$_WeldClientProxy.java) [foundation-2206055524065067930.jar:]

at org.sis.apo2.camunda.services.tasks.MyPersonalTasklistTestIT.testQuery(MyPersonalTasklistTestIT.java:46) [foundation-5360262004314638204.jar:]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_21]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_21]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_21]

at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_21]

at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:80) [testng-6.5.2.jar:]

at org.testng.internal.MethodInvocationHelper$1.runTestMethod(MethodInvocationHelper.java:182) [testng-6.5.2.jar:]

at org.jboss.arquillian.testng.Arquillian$2.invoke(Arquillian.java:170) [arquillian-testng.jar:]

at org.jboss.arquillian.container.test.impl.execution.LocalTestExecuter.execute(LocalTestExecuter.java:60) [arquillian-core.jar:]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_21]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_21]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_21]

at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_21]

at org.jboss.arquillian.core.impl.ObserverImpl.invoke(ObserverImpl.java:94) [arquillian-core.jar:]

at org.jboss.arquillian.core.impl.EventContextImpl.invokeObservers(EventContextImpl.java:99) [arquillian-core.jar:]

at org.jboss.arquillian.core.impl.EventContextImpl.proceed(EventContextImpl.java:81) [arquillian-core.jar:]

at org.jboss.arquillian.core.impl.ManagerImpl.fire(ManagerImpl.java:145) [arquillian-core.jar:]

at org.jboss.arquillian.core.impl.ManagerImpl.fire(ManagerImpl.java:116) [arquillian-core.jar:]

at org.jboss.arquillian.core.impl.EventImpl.fire(EventImpl.java:67) [arquillian-core.jar:]

at org.jboss.arquillian.container.test.impl.execution.ContainerTestExecuter.execute(ContainerTestExecuter.java:38) [arquillian-core.jar:]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_21]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_21]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_21]

at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_21]

at org.jboss.arquillian.core.impl.ObserverImpl.invoke(ObserverImpl.java:94) [arquillian-core.jar:]

at org.jboss.arquillian.core.impl.EventContextImpl.invokeObservers(EventContextImpl.java:99) [arquillian-core.jar:]

at org.jboss.arquillian.core.impl.EventContextImpl.proceed(EventContextImpl.java:81) [arquillian-core.jar:]

at org.jboss.arquillian.test.impl.TestContextHandler.createTestContext(TestContextHandler.java:102) [arquillian-core.jar:]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_21]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_21]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_21]

at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_21]

at org.jboss.arquillian.core.impl.ObserverImpl.invoke(ObserverImpl.java:94) [arquillian-core.jar:]

at org.jboss.arquillian.core.impl.EventContextImpl.proceed(EventContextImpl.java:88) [arquillian-core.jar:]

at org.jboss.arquillian.test.impl.TestContextHandler.createClassContext(TestContextHandler.java:84) [arquillian-core.jar:]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_21]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_21]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_21]

at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_21]

at org.jboss.arquillian.core.impl.ObserverImpl.invoke(ObserverImpl.java:94) [arquillian-core.jar:]

at org.jboss.arquillian.core.impl.EventContextImpl.proceed(EventContextImpl.java:88) [arquillian-core.jar:]

at org.jboss.arquillian.test.impl.TestContextHandler.createSuiteContext(TestContextHandler.java:65) [arquillian-core.jar:]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_21]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_21]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_21]

at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_21]

at org.jboss.arquillian.core.impl.ObserverImpl.invoke(ObserverImpl.java:94) [arquillian-core.jar:]

at org.jboss.arquillian.core.impl.EventContextImpl.proceed(EventContextImpl.java:88) [arquillian-core.jar:]

at org.jboss.arquillian.core.impl.ManagerImpl.fire(ManagerImpl.java:145) [arquillian-core.jar:]

at org.jboss.arquillian.test.impl.EventTestRunnerAdaptor.test(EventTestRunnerAdaptor.java:111) [arquillian-core.jar:]

at org.jboss.arquillian.testng.Arquillian.run(Arquillian.java:161) [arquillian-testng.jar:]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_21]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_21]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_21]

at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_21]

at org.testng.internal.MethodInvocationHelper.invokeHookable(MethodInvocationHelper.java:194) [testng-6.5.2.jar:]

at org.testng.internal.Invoker.invokeMethod(Invoker.java:695) [testng-6.5.2.jar:]

at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:894) [testng-6.5.2.jar:]

at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1219) [testng-6.5.2.jar:]

at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:127) [testng-6.5.2.jar:]

at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111) [testng-6.5.2.jar:]

at org.testng.TestRunner.privateRun(TestRunner.java:768) [testng-6.5.2.jar:]

at org.testng.TestRunner.run(TestRunner.java:617) [testng-6.5.2.jar:]

at org.testng.SuiteRunner.runTest(SuiteRunner.java:334) [testng-6.5.2.jar:]

at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329) [testng-6.5.2.jar:]

at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291) [testng-6.5.2.jar:]

at org.testng.SuiteRunner.run(SuiteRunner.java:240) [testng-6.5.2.jar:]

at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:53) [testng-6.5.2.jar:]

at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:87) [testng-6.5.2.jar:]

at org.testng.TestNG.runSuitesSequentially(TestNG.java:1188) [testng-6.5.2.jar:]

at org.testng.TestNG.runSuitesLocally(TestNG.java:1113) [testng-6.5.2.jar:]

at org.testng.TestNG.run(TestNG.java:1025) [testng-6.5.2.jar:]

at org.jboss.arquillian.testng.container.TestNGTestRunner.execute(TestNGTestRunner.java:53) [arquillian-testng.jar:]

at org.jboss.arquillian.protocol.servlet.runner.ServletTestRunner.executeTest(ServletTestRunner.java:159) [arquillian-protocol.jar:]

at org.jboss.arquillian.protocol.servlet.runner.ServletTestRunner.execute(ServletTestRunner.java:125) [arquillian-protocol.jar:]

at org.jboss.arquillian.protocol.servlet.runner.ServletTestRunner.doGet(ServletTestRunner.java:89) [arquillian-protocol.jar:]

at javax.servlet.http.HttpServlet.service(HttpServlet.java:734) [jboss-servlet-api_3.0_spec-1.0.2.Final-redhat-1.jar:1.0.2.Final-redhat-1]

at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [jboss-servlet-api_3.0_spec-1.0.2.Final-redhat-1.jar:1.0.2.Final-redhat-1]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295) [jbossweb-7.2.2.Final-redhat-1.jar:7.2.2.Final-redhat-1]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214) [jbossweb-7.2.2.Final-redhat-1.jar:7.2.2.Final-redhat-1]

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230) [jbossweb-7.2.2.Final-redhat-1.jar:7.2.2.Final-redhat-1]

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149) [jbossweb-7.2.2.Final-redhat-1.jar:7.2.2.Final-redhat-1]

at org.jboss.as.jpa.interceptor.WebNonTxEmCloserValve.invoke(WebNonTxEmCloserValve.java:50) [jboss-as-jpa-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]

at org.jboss.as.jpa.interceptor.WebNonTxEmCloserValve.invoke(WebNonTxEmCloserValve.java:50) [jboss-as-jpa-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]

at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169) [jboss-as-web-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:145) [jbossweb-7.2.2.Final-redhat-1.jar:7.2.2.Final-redhat-1]

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97) [jbossweb-7.2.2.Final-redhat-1.jar:7.2.2.Final-redhat-1]

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102) [jbossweb-7.2.2.Final-redhat-1.jar:7.2.2.Final-redhat-1]

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:336) [jbossweb-7.2.2.Final-redhat-1.jar:7.2.2.Final-redhat-1]

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856) [jbossweb-7.2.2.Final-redhat-1.jar:7.2.2.Final-redhat-1]

at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:653) [jbossweb-7.2.2.Final-redhat-1.jar:7.2.2.Final-redhat-1]

at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:920) [jbossweb-7.2.2.Final-redhat-1.jar:7.2.2.Final-redhat-1]

at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_21]

Caused by: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of [Ljava.lang.String;. Use setObject() with an explicit Types value to specify the type to use.

at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1920)

at org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:36)

at org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:47)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_21]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_21]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_21]

at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_21]

at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:453)

at com.sun.proxy.$Proxy14.setObject(Unknown Source)

at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.setObject(CachedPreparedStatement.java:289)

at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:986)

at org.apache.ibatis.type.ObjectTypeHandler.setNonNullParameter(ObjectTypeHandler.java:28)

at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:46)

at org.apache.ibatis.type.UnknownTypeHandler.setNonNullParameter(UnknownTypeHandler.java:37)

at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:46)

at org.apache.ibatis.executor.parameter.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:91)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:77)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:58)

at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:71)

at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:56)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:141)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:101)

... 105 more

[Sebastian Menski]

Hi Markus,

I think you should use the mybatis foreach construct [1] to create the correct SQL syntax. Or you could create the same string with Java

code.

Cheers,
Sebastian

[1] http://mybatis.github.io/mybatis-3/dynamic-sql.html

[Markus Schulz]

much overhead for such a simple thing. but okay i will take a look at the mybatis integration/customization.