Need help with selectManyListbox

[daxxy]

I have an entity Devices.  One property of Devices is peRouter as follows.

@Entity
@Table(name = "devices")
@Name("devices")
public class Devices implements java.io.Serializable {

     private Integer devId;
     private String devName;
     private String devSerialNum;
     private String devPlatform;
     private String devOsVer;
     private String devIp;
     private String peRouter;
     
     public Devices() {
     }

     @Id
     @GeneratedValue(strategy = IDENTITY)
     @Column(name = "dev_id", unique = true, nullable = false)
     public Integer getDevId() {
          return this.devId;
     }

     public void setDevId(Integer devId) {
          this.devId = devId;
     }

     @Column(name = "dev_name", length = 30)
     @Length(max = 30)
     public String getDevName() {
          return this.devName;
     }

     public void setDevName(String devName) {
          this.devName = devName;
     }

     @Column(name = "dev_serial_num", nullable = false, length = 64)
     @NotNull
     @Length(max = 64)
     public String getDevSerialNum() {
          return this.devSerialNum;
     }

     public void setDevSerialNum(String devSerialNum) {
          this.devSerialNum = devSerialNum;
     }

     @Column(name = "dev_platform", nullable = false, length = 64)
     @NotNull
     @Length(max = 64)
     public String getDevPlatform() {
          return this.devPlatform;
     }

     public void setDevPlatform(String devPlatform) {
          this.devPlatform = devPlatform;
     }

     @Column(name = "dev_os_ver", nullable = false, length = 64)
     @NotNull
     @Length(max = 64)
     public String getDevOsVer() {
          return this.devOsVer;
     }

     public void setDevOsVer(String devOsVer) {
          this.devOsVer = devOsVer;
     }

     @Column(name = "dev_ip", length = 15)
     @Length(max = 15)
     public String getDevIp() {
          return this.devIp;
     }
     
     public void setDevIp(String devIp) {
          this.devIp = devIp;
     }
     
     
     @Column(name = "pe_router")
     public String getPeRouter() {
          return peRouter;
     }

     public void setPeRouter(String peRouter) {
          this.peRouter = peRouter;
     }

     
}

I have a search page that displays in a selectManyListbox component and list of PE Routers retrieved as follows from PeRouterList.java:

@SuppressWarnings("serial")
@Name("peRouterList")
public class PeRouterList extends EntityQuery<Devices> {

     private static final String EJBQL = 
          "select distinct devices.peRouter from Devices devices where " +
          "peRouter is not null order by peRouter";
     
     private Devices devices = new Devices();
     
     public PeRouterList() {
          setEjbql(EJBQL);
     }
     
     public Devices getDevices() {
          return devices;
     }

}

The goal is to select several PE Routers and display all the devices connected to that PE Router. The SQL query equivalent is something like this

select dev_name from devices where pe_router in (<list of pe routers here>)

I have read and read and read documentation and examples and I cannot figure out how the heck to do this.  It seems none of the examples are close enough to what I am doing.  In addition, I am a relative new-comer to java so some of the explanations are hard to understand.  I would appreciate greatly if someone can shed some light on what I need to do here.

The results page is backed by this action (generated originaly by seam-gen):

@Name("devicesList")
public class DevicesList extends EntityQuery<Devices> {

     private static final String EJBQL = "select devices from Devices devices";

          
     private static final String[] RESTRICTIONS = {
               "lower(devices.peRouter) in(#{devices.peRouters})",
               "lower(devices.devIp) like lower(concat('%'#{devices.devIp},'%'))",
               "lower(devices.devName) like lower(concat('%',#{devicesList.devices.devName},'%'))",
               "lower(devices.devOsVer) like lower(concat('%'#{devices.devOsVer},'%'))",
               "lower(devices.devPlatform) like lower(concat('%'#{devices.devPlatform},'%'))",
               "lower(devices.devSerialNum) like lower(concat('%'#{devices.devSerialNum},'%'))",
               "lower(devices.isRouter) = lower(concat(#{devices.isRouter},'%'))",};

     private Devices devices = new Devices();
     

     public DevicesList() {
          setEjbql(EJBQL);
          setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
          setOrderColumn("Devices.devName");
          setMaxResults(25);
     }

     public Devices getDevices() {
          return devices;
     }
}

I can't figure out how to create the list that is the argument to the IN operator in the query.  There is something here I can't quite get my head around.  Can someone explain?

Hope this question is clear and complete

TDR

[cash1981]

I am not 100% sure if I have understood your problem, but what I think is that you want to select all dev.name where the peRouter something is in Device?

You can do that by creating this query:

entityManager.createQuery("select dev.name from Device dev where dev.peRouter = :someName").setParameter("someName","peRouterName").getResultList();

[zhangxiubo]

I don't think you can use IN operator in where clauses when using EJBQL. See Example Queries for the usages of the IN operator.

[wilczarz.wilczarz.gmail.com]

Of course you can use IN operator inside WHERE caluses. The problem is that you can't pass a collection as a named parameter to an EJBQL query. Unfortunately for you, this is exactly the case since EntityQuery maps the EL expression from each restriction to a named parameter. My advice is to drop EntityQuery, or at least restrictions - maybe you can find a way to modify ejbQl on the fly.

[daxxy]

So even though it is syntactically correct to use an IN clause within an EJBQL query, since you can't pass a collection to an EJBQL query via RESTRICTIONS it doesn't really do you (ie me) any good in this case.

OK, that's a start I guess.

TDR

[zhangxiubo]

Sorry that I made it wrong. You are right. One can definitely use IN in WHERE clause.