java - JPA Criteria API persistent field IN expression using list of entities -
i'm trying similar question posted before:
jpa criteria api in expression parameter list
however, i'm trying use list of entities instead of list of longs (pks), like:
public list<task> generatefilteredquery( list<user> filterusers ) { criteriabuilder cb = this.em.getcriteriabuilder(); criteriaquery<task> criteriaquery = cb.createquery( task.class ); root<task> root = criteriaquery.from( task.class ); criteriaquery.select( root ).where( root.get( "owner" ).in( filterusers ) ); return this.em.createquery( criteriaquery ).getresultlist(); } please assume this.em of type entitymanager. task entity has associated owner, mapped this:
@entity @table( name = "tasks" ) public class task implements serializable { ... @manytoone( fetch = fetchtype.eager ) @joincolumn( name = "owner_user_id", referencedcolumnname = "user_id" ) private user owner; ... } i know have used similar jpql before, don't see how done using criteria api.
using above code causes db exception ocurr:
caused by: exception [eclipselink-4002] (eclipse persistence services - 2.4.1.v20121003-ad44345): org.eclipse.persistence.exceptions.databaseexception internal exception: com.microsoft.sqlserver.jdbc.sqlserverexception: fehler beim konvertieren des nvarchar-datentyps in bigint. error code: 8114 call: select distinct t1.task_id, t1.client_id, t1.close_time, t1.close_user_id, t1.task_date, t1.task_date_id, t1.lastaction, t1.lasthost_id, t1.lasttime, t1.lastuser_id, t1.object_id, t1.owner_user_id, t1.status_text, t1.status, t1.table_id, t1.task_trigger_id, t1.task_type tasks t1 left outer join task_types t2 on (t2.task_type_id = t1.task_type) left outer join task_triggers t3 on (t3.task_trigger_id = t1.task_trigger_id) left outer join users t4 on (t4.user_id = t1.lastuser_id) left outer join users t5 on (t5.user_id = t1.close_user_id), users t0 ((((t1.client_id = ?) , ((((((lower(t2.name) ? or lower(t1.status) ?) or lower(t1.status_text) ?) or lower(t0.user_name) ?) or lower(t3.description) ?) or lower(t4.user_name) ?) or lower(t5.user_name) ?)) , (t1.owner_user_id in (?))) , (t0.user_id = t1.owner_user_id)) order t1.lasttime desc bind => [1, %%, %%, %%, %%, %%, %%, %%, user [id=437, getusername()=bla, clients=[], .................]] query: readallquery(referenceclass=task sql="select distinct t1.task_id, t1.client_id, t1.close_time, t1.close_user_id, t1.task_date, t1.task_date_id, t1.lastaction, t1.lasthost_id, t1.lasttime, t1.lastuser_id, t1.object_id, t1.owner_user_id, t1.status_text, t1.status, t1.table_id, t1.task_trigger_id, t1.task_type tasks t1 left outer join task_types t2 on (t2.task_type_id = t1.task_type) left outer join task_triggers t3 on (t3.task_trigger_id = t1.task_trigger_id) left outer join users t4 on (t4.user_id = t1.lastuser_id) left outer join users t5 on (t5.user_id = t1.close_user_id), users t0 ((((t1.client_id = ?) , ((((((lower(t2.name) ? or lower(t1.status) ?) or lower(t1.status_text) ?) or lower(t0.user_name) ?) or lower(t3.description) ?) or lower(t4.user_name) ?) or lower(t5.user_name) ?)) , (t1.owner_user_id in (?))) , (t0.user_id = t1.owner_user_id)) order t1.lasttime desc") @ org.eclipse.persistence.exceptions.databaseexception.sqlexception(databaseexception.java:333) @ org.eclipse.persistence.internal.databaseaccess.databaseaccessor.basicexecutecall(databaseaccessor.java:646) @ org.eclipse.persistence.internal.databaseaccess.databaseaccessor.executecall(databaseaccessor.java:537) @ org.eclipse.persistence.internal.sessions.abstractsession.basicexecutecall(abstractsession.java:1800) @ org.eclipse.persistence.sessions.server.serversession.executecall(serversession.java:566) @ org.eclipse.persistence.sessions.server.clientsession.executecall(clientsession.java:240) @ org.eclipse.persistence.internal.queries.datasourcecallquerymechanism.executecall(datasourcecallquerymechanism.java:207) @ org.eclipse.persistence.internal.queries.datasourcecallquerymechanism.executecall(datasourcecallquerymechanism.java:193) @ org.eclipse.persistence.internal. info: queries.datasourcecallquerymechanism.executeselectcall(datasourcecallquerymechanism.java:264) @ org.eclipse.persistence.internal.queries.datasourcecallquerymechanism.selectallrows(datasourcecallquerymechanism.java:648) @ org.eclipse.persistence.internal.queries.expressionquerymechanism.selectallrowsfromtable(expressionquerymechanism.java:2706) @ org.eclipse.persistence.internal.queries.expressionquerymechanism.selectallrows(expressionquerymechanism.java:2659) @ org.eclipse.persistence.queries.readallquery.executeobjectlevelreadquery(readallquery.java:421) @ org.eclipse.persistence.queries.objectlevelreadquery.executedatabasequery(objectlevelreadquery.java:1150) @ org.eclipse.persistence.queries.databasequery.execute(databasequery.java:852) @ org.eclipse.persistence.queries.objectlevelreadquery.execute(objectlevelreadquery.java:1109) @ org.eclipse.persistence.queries.readallquery.execute(readallquery.java:393) @ org.eclipse.persistence.queries.objectlevelreadquery.executeinunitofwork(objectlevelreadquery.java:1197) @ org.eclipse.persistence.internal.sessions.unitofworkimpl.internalexecutequery(unitofworkimpl.java:2875) @ org.eclipse.persistence.internal.sessions.abstractsession.executequery(abstractsession.java:1602) @ org.eclipse.persistence.internal.sessions.abstractsession.executequery(abstractsession.java:1584) @ org.eclipse.persistence.internal.sessions.abstractsession.executequery(abstractsession.java:1549) @ org.eclipse.persistence.internal.jpa.queryimpl.executereadquery(queryimpl.java:231) @ org.eclipse.persistence.internal.jpa.queryimpl.getresultlist(queryimpl.java:411) @ com.sun.enterprise.container.common.impl.querywrapper.getresultlist(querywrapper.java:195) @ com.sun.enterprise.container.common.impl.typedquerywrapper.getresultlist(typedquerywrapper.java:129) @ de.bnext.core.base.repository.baseentityrepositorybean.findby(baseentityrepositorybean.java:448) @ sun.reflect.nativemethodaccessorimpl.invoke0(native method) @ sun.reflect.nativemethodaccessorimpl.invoke(nativemethodaccessorimpl.java:57) @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43) @ java.lang.reflect.method.invoke(method.java:601) @ org.glassfish.ejb.security.application.ejbsecuritymanager.runmethod(ejbsecuritymanager.java:1052) @ org.glassfish.ejb.security.application.ejbsecuritymanager.invoke(ejbsecuritymanager.java:1124) @ com.sun.ejb.containers.basecontainer.invokebeanmethod(basecontainer.java:5388) @ com.sun.ejb.ejbinvocation.invokebeanmethod(ejbinvocation.java:619) @ com.sun.ejb.containers.interceptors.aroundinvokechainimpl.invokenext(interceptormanager.java:800) @ com.sun.ejb.ejbinvocation.proceed(ejbinvocation.java:571) @ de.bnext.core.interceptor.persistence.persistencepropertiesinterceptor.injectpersistenceproperties(persistencepropertiesinterceptor.java:81) @ sun.reflect.generatedmethodaccessor1084.invoke(unknown source) @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43) @ java.lang.reflect.method.invoke(method.java:601) @ com.sun.ejb.containers.interceptors.aroundinvokeinterceptor.intercept(interceptormanager.java:861) @ com.sun.ejb.containers.interceptors.aroundinvokechainimpl.invokenext(interceptormanager.java:800) @ com.sun.ejb.ejbinvocation.proceed(ejbinvocation.java:571) @ de.bnext.core.interceptor.common.nullcheckinterceptor.checknull(nullcheckinterceptor.java:85) @ sun.reflect.generatedmethodaccessor1083.invoke(unknown source) @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43) @ java.lang.reflect.method.invoke(method.java:601) @ com.sun.ejb.containers.interceptors.aroundinvokeinterceptor.intercept(interceptormanager.java:861) @ com.sun.ejb.containers.interceptors.aroundinvokechainimpl.invokenext(interceptormanager.java:800) @ com.sun.ejb.ejbinvocation.proceed(ejbinvocation.java:571) @ de.bnext.core.interceptor.logging.logginginterceptor.logmethod(logginginterceptor.java:90) @ sun.reflect.generatedmethodaccessor1078.invoke(unknown source) @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43) @ java.lang.reflect.method.invoke(method.java:601) @ com.sun.ejb.containers.interceptors.aroundinvokeinterceptor.intercept(interceptormanager.java:861) @ com.sun.ejb.containers.interceptors.aroundinvokechainimpl.invokenext(interceptormanager.java:800) @ com.sun.ejb.ejbinvocation.proceed(ejbinvocation.java:571) @ org.jboss.weld.ejb.sessionbeaninterceptor.aroundinvoke(sessionbeaninterceptor.java:42) @ sun.reflect.generatedmethodaccessor1077.invoke(unknown source) @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43) @ java.lang.reflect.method.invoke(method.java:601) @ com.sun.ejb.containers.interceptors.aroundinvokeinterceptor.intercept(interceptormanager.java:861) @ com.sun.ejb.containers.interceptors.aroundinvokechainimpl.invokenext(interceptormanager.java:800) @ com.sun.ejb.ejbinvocation.proceed(ejbinvocation.java:571) @ com.sun.ejb.containers.interceptors.systeminterceptorproxy.doaround(systeminterceptorproxy.java:162) @ com.sun.ejb.containers.interceptors.systeminterceptorproxy.aroundinvoke(systeminterceptorproxy.java:144) @ sun.reflect.generatedmethodaccessor1076.invoke(unknown source) @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43) @ java.lang.reflect.method.invoke(method.java:601) @ com.sun.ejb.containers.interceptors.aroundinvokeinterceptor.intercept(interceptormanager.java:861) @ com.sun.ejb.containers.interceptors.aroundinvokechainimpl.invokenext(interceptormanager.java:800) @ com.sun.ejb.containers.interceptors.interceptormanager.intercept(interceptormanager.java:370) @ com.sun.ejb.containers.basecontainer.__intercept(basecontainer.java:5360) @ com.sun.ejb.containers.basecontainer.intercept(basecontainer.java:5348) @ com.sun.ejb.containers.ejblocalobjectinvocationhandler.invoke(ejblocalobjectinvocationhandler.java:214) ... 120 more caused by: com.microsoft.sqlserver.jdbc.sqlserverexception: fehler beim konvertieren des nvarchar-datentyps in bigint. @ com.microsoft.sqlserver.jdbc.sqlserverexception.makefromdatabaseerror(sqlserverexception.java:216) @ com.microsoft.sqlserver.jdbc.sqlserverresultset$fetchbuffer.nextrow(sqlserverresultset.java:4853) @ com.microsoft.sqlserver.jdbc.sqlserverresultset.fetchbuffernext(sqlserverresultset.java:1781) @ com.microsoft.sqlserver.jdbc.sqlserverresultset.next(sqlserverresultset.java:1034) @ com.sun.gjc.spi.base.resultsetwrapper.next(resultsetwrapper.java:103) @ org.eclipse.persistence.internal.databaseaccess.databaseaccessor.processresultset(databaseaccessor.java:715) @ org.eclipse.persistence.internal.databaseaccess.databaseaccessor.basicexecutecall(databaseaccessor.java:623) ... 194 more the problem part ... , (t1.owner_user_id in (?))) ... assumes kind of list used. not desired.
i remember having done jpql , hibernate jpa provider before, here's question.
q:
is there criteria equivalent jpql ... task.owner in :filterusers, :filterusers standard named paramater using query.setparameter( "filterusers", filterusers ) , latter being declared list<user> filterusers = ...? if so, how's done?
ps: sorry german error messages, h*te localized error messages (and can't repeat many times!).
you can try this,
criteria criteria = getsession().createcriteria(task .class); criteria.createalias("owner.id", "filterusers"); criteria.add(restrictions.in("filterusers", <list contains user ids>));
Comments
Post a Comment