[JPQL] Problem statement SELECT ... IN () , with parameters

[CG-6]

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

[Pinaki Poddar]

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}));

[CG-6]

Thanks! It works!

CG

On Tue, Aug 12, 2008 at 1:53 AM, Pinaki Poddar <[hidden email]> 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.
>
>

[ajcnb]

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 <[e-mail]> 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.
>
>

[me_satya]

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!!!

[Fay Wang]

Hi,

Can you post the exception stack trace?

Fay



----- Original Message ----
From: me_satya <[hidden email]>
To: [hidden email]
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.