You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by CG <le...@gmail.com> on 2008/08/11 18:31:34 UTC
[JPQL] Problem statement SELECT ... IN () , with parameters
Hi all,
I am quite new in Java and JPQL , encountered a problem which I
think should be easy to solve if have experience.
My problem is happen in SELECT ... .IN
I have Role , RoleProgram and Program table, where Program and Role
are Master table which keep the Program Master and Role Master ,
RoleProgram is a linking table which define which role have which
program access authority.
When I have the following JPQL statement
Query q = _em.createQuery("select distinct p from Program p" +
" join p.rolePrograms rp" +
" join rp.role r" +
" where r.id in (1,2)"
);
The correct result shown
Program: [id=1, record status=A, session id=1, create login=manual,
create app=, create date=2008-08-10, create time=00:00:00, modify
login=manual, modify app=, modify date=2008-08-10, modify
time=00:00:00, version=1]
Program: [id=2, record status=A, session id=1, create login=manual,
create app=, create date=2008-08-10, create time=00:00:00, modify
login=manual, modify app=, modify date=2008-08-10, modify
time=00:00:00, version=1]
Program: [id=3, record status=A, session id=1, create login=manual,
create app=, create date=2008-08-10, create time=00:00:00, modify
login=manual, modify app=, modify date=2008-08-10, modify
time=00:00:00, version=1]
Program: [id=5, record status=A, session id=1, create login=manual,
create app=, create date=2008-08-10, create time=00:00:00, modify
login=manual, modify app=, modify date=2008-08-10, modify
time=00:00:00, version=1]
When I try to change the list in brackets to use parameters
Query q = _em.createQuery("select distinct p from Program p" +
" join p.rolePrograms rp" +
" join rp.role r" +
" where r.id in (:roleIds)"
);
q.setParameter("roleIds", sb.toString());
where sb.toString() will be output as "1,2" ..
To my surprise, only the last result is returned. I can confirm that
sb.toString() output as "1,2" , because I have output the value to
file to debug.
Anybody encounters such problem before, mind to share your solution ?
Any input is appreciated , thanks in advanced.
CG
Re: [JPQL] Problem statement SELECT ... IN () , with parameters
Posted by Fay Wang <fy...@yahoo.com>.
Hi,
Can you post the exception stack trace?
Fay
----- Original Message ----
From: me_satya <he...@yahoo.co.in>
To: users@openjpa.apache.org
Sent: Tue, October 27, 2009 1:40:13 AM
Subject: Re: [JPQL] Problem statement SELECT ... IN () , with parameters
String queryString = new String("SELECT ci FROM Cxyz ci WHERE ci.Idxyz IN
(:UIDs) AND ci.dxyz IS NULL");
Query query = em.createQuery(queryString);
ci.Idxyz is the column of byte type.
this :UIDs is the list of object.
Can any one let me know how can i set this list in the query
I tried the following but got some unsupported operation exception.
List<byte[]> IdList = Arrays.asList(new byte[]{});
for (UID<ContextContainerId> containerId : containerUIDs) {
UIdList.add(containerId.toBytes());
}
query.setParameter("UIDs", UIdList);
I will appriciate you people help me out here!!!
--
View this message in context: http://n2.nabble.com/JPQL-Problem-statement-SELECT-IN-with-parameters-tp686295p3897633.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: [JPQL] Problem statement SELECT ... IN () , with parameters
Posted by me_satya <he...@yahoo.co.in>.
String queryString = new String("SELECT ci FROM Cxyz ci WHERE ci.Idxyz IN
(:UIDs) AND ci.dxyz IS NULL");
Query query = em.createQuery(queryString);
ci.Idxyz is the column of byte type.
this :UIDs is the list of object.
Can any one let me know how can i set this list in the query
I tried the following but got some unsupported operation exception.
List<byte[]> IdList = Arrays.asList(new byte[]{});
for (UID<ContextContainerId> containerId : containerUIDs) {
UIdList.add(containerId.toBytes());
}
query.setParameter("UIDs", UIdList);
I will appriciate you people help me out here!!!
--
View this message in context: http://n2.nabble.com/JPQL-Problem-statement-SELECT-IN-with-parameters-tp686295p3897633.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: [JPQL] Problem statement SELECT ... IN () , with parameters
Posted by ajcnb <jo...@gmail.com>.
I have tried to apply this solution to my own code:
This is the namedQuery I have placed on the entity:
@NamedQuery(name="listDocumentTypesByCategories",
query="SELECT dt FROM DocumentType dt, DocumentTypeCategoryLink dtcl
" +
"WHERE dt.documentTypeID = dtcl.documentType.documentTypeID " +
"AND dtcl.documentCategory.documentCategoryID IN (:catIDs) " +
"AND dt.invalid = 'false' " +
"ORDER BY dt.name ASC")
This is how I am executing setting up/executing query following the
instructions posted in this forum:
Query q = em.createNamedQuery("listDocumentTypesByCategories");
q.setParameter("catIDs", Arrays.asList(new int[]{1,2,3}));
List<DocumentType> objList = (List<DocumentType>)q.getResultList();
This is the error I am receiving upon execution:
java.lang.IllegalArgumentException: You have attempted to set a value of
type class java.util.Arrays$ArrayList for parameter catIDs with expected
type of int from query string SELECT dt FROM DocumentType dt,
DocumentTypeCategoryLink dtcl WHERE dt.documentTypeID =
dtcl.documentType.documentTypeID AND
dtcl.documentCategory.documentCategoryID IN (:catIDs) AND dt.invalid =
'false' ORDER BY dt.name ASC
The query works fine if I just pass in a single int value, instead of trying
an array of int values?
Any help on this would be greatly appreciated.
On Tue, Aug 12, 2008 at 1:53 AM, Pinaki Poddar <pp...@apache.org> wrote:
>
> Hi,
>> q.setParameter("roleIds", sb.toString());
>
> Set the IN parameter not as a String but a List<T> where T is the type of
> Role.id.
>
> For example, if Role.id is int, then
> q.setParameter("roleIds", Arrays.asList(new int[]{1,2}));
>
>
> --
> View this message in context:
> http://n2.nabble.com/-JPQL--Problem-statement-SELECT-...-IN-%28%29-%2C-with-parameters-tp686295p686459.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
>
--
View this message in context: http://n2.nabble.com/-JPQL--Problem-statement-SELECT-...-IN-%28%29-%2C-with-parameters-tp686295p839238.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: [JPQL] Problem statement SELECT ... IN () , with parameters
Posted by CG <le...@gmail.com>.
Thanks! It works!
CG
On Tue, Aug 12, 2008 at 1:53 AM, Pinaki Poddar <pp...@apache.org> wrote:
>
> Hi,
>> q.setParameter("roleIds", sb.toString());
>
> Set the IN parameter not as a String but a List<T> where T is the type of
> Role.id.
>
> For example, if Role.id is int, then
> q.setParameter("roleIds", Arrays.asList(new int[]{1,2}));
>
>
> --
> View this message in context: http://n2.nabble.com/-JPQL--Problem-statement-SELECT-...-IN-%28%29-%2C-with-parameters-tp686295p686459.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
>
Re: [JPQL] Problem statement SELECT ... IN () , with parameters
Posted by Pinaki Poddar <pp...@apache.org>.
Hi,
> q.setParameter("roleIds", sb.toString());
Set the IN parameter not as a String but a List<T> where T is the type of
Role.id.
For example, if Role.id is int, then
q.setParameter("roleIds", Arrays.asList(new int[]{1,2}));
--
View this message in context: http://n2.nabble.com/-JPQL--Problem-statement-SELECT-...-IN-%28%29-%2C-with-parameters-tp686295p686459.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.