Discussions

EJB programming & troubleshooting: EJBQL ?* IS NULL / multiple filters in EJB (CMP)

  1. Hi!

    I searched the forum but couldn't find any solution to my problem. If anyone already has tried this and knows the solution please let me know. I am looking for it desparately.

    The requirement...
    I want to support multiple filters for an entity bean. In GUI I give user an option to select filters. He can select them in any combination. Taking example of a Employee entity... lets say filters are name, phone, city, gender. Now GUI shows all fields and user can enter for example... name and gender while phone and city is blank.

    After this I want to access the Employee Entity EJB (CMP) and get relevant records.

    Tried solution...
    Accoridng to EJB2.1 (11.2.6.10) specification I can use ?* IS [NOT] NULL. So I tried specifying query...


    SELECT OBJECT(e) FROM Employee e WHERE ((?1 IS NOT NULL AND e.name = ?1) OR (?1 IS NULL))


    This didn't work.

    The questions...
    1. Is this feature (11.2.6.10) available in EJB2.1?
    2. Is there any other way by which I can achieve this?

    I am out of choices and have to deliver this functionality... if anyone knows any work around or any clean alternative please reply. It will help me a lot.

    Thanx in advance.
  2. The "IS NULL" test is design to test whether a field value is null, not whether an input parameter is null.

    Here is what I suggest:

    1) Check to see if you EJB server supports dynamic EJB-QL calls. It is outside the spec, but will do what you want.

    2) Don't use Entities for this lookup. Use a combination of JDBC and Data Transfer Objects to look up your data.

    Frankly, option 2 is probably the best. These kinds of ad-hoc queries are going to return a lot of results, and Entity beans tend to handle large resultsets poorly.
  3. ?1 IS NULL is definitely there in EJB 2.1 spec (at least the mentioned sections says clearly 'input parameter' in the BNF).

    Anyway... the server I am using doesn't support EJB2.1 yet.

    I was thinking of this second alternative you are mentioning, but couldn't exactly think about a solid design. But in the absence of EJBQL support for checking entity in given subset of entities (may be array of ids)... it will be too costly to get all possible entities and then discard them after finding they don't fit the criteria.

    Do you have any solution/alternative which might work? I can try to implement it.
  4. ?1 IS NULL is definitely there in EJB 2.1 spec (at least the mentioned sections says clearly 'input parameter' in the BNF).
    My apologies. I missed the "EJB 2.1" part of question. As you say, since most servers don't support it, I tend not to think of it.
    Anyway... the server I am using doesn't support EJB2.1 yet.I was thinking of this second alternative you are mentioning, but couldn't exactly think about a solid design. But in the absence of EJBQL support for checking entity in given subset of entities (may be array of ids)... it will be too costly to get all possible entities and then discard them after finding they don't fit the criteria.Do you have any solution/alternative which might work? I can try to implement it.
    An optimized, pure JDBC approach is fairly easy, because you can create your queries dynamically. Then you are only querying with the exact criterea the user wants, which is about as fast as it gets. Something like this:



    Map map = // Map containing parameter name/value pairs
    // names match DB field, values appropriate object type (String, Integer, etc.)

    // Create query:
    String query = "SELECT FROM Employee WHERE ";
    Iterator params = map.iterator();
    while(param.hasNext()) {
      query += param.next() "=?";
      if (param.hasNext()) query += " AND";
    }

    // Assign parameter values to query:
    PreparedStatement stmt = con.prepareStatement(query);
    int count = 1;
    params = map.iterator();
    while(param.hasNext()) {
      stmt.setObject(map.get(param.next());
      count++;
    }
    ResultSet rs = stmt.executeQuery();



    If your criterea involve relationships (only EMPLOYEES that are referenced from the RETIREMENT_PLAN table), the logic involved in creating your query is trickier, but still manageable.
  5. Thanx!