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