5 Replies Latest reply on Jul 20, 2009 5:51 PM by Xiubo Zhang

    Need help with selectManyListbox

    Tanya Ruttenberg Expert

      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