3 Replies Latest reply on Feb 16, 2013 8:53 PM by Greybird-Oracle

    EntityJoin with range

      I found this on online reference: http://www.oracle.com/technetwork/products/berkeleydb/performing.pdf
       * SELECT * FROM employee  
       * WHERE employeeName = 'key1' AND departmentId = key2; 
      public <PK, SK1, SK2, E> ForwardCursor<E> 
          doTwoConditionsJoin(PrimaryIndex<PK, E> pk, 
                                  SecondaryIndex<SK1, PK, E> sk1, 
                                  SK1 key1, 
                                  SecondaryIndex<SK2, PK, E> sk2, 
                                  SK2 key2) 
          throws DatabaseException { 
          assert (pk != null); 
          assert (sk1 != null); 
          assert (sk2 != null); 
          EntityJoin<PK, E> join = new EntityJoin<PK, E>(pk); 
          join.addCondition(sk1, key1); 
          join.addCondition(sk2, key2); 
          return join.entities(); 
      Instead of
       SELECT * FROM employee  
       WHERE employeeName = 'key1' AND departmentId = key2; 
      I want do to this (by range),
       SELECT * FROM employee  
       WHERE employeeName >= 'key1' AND employeeName <= 'key2' AND departmentId = key3; 
      Just for example I used the employeeName, it's not a good one, but just to exemplify.

      I could use a secondary key for checking timeStamp, anything else...

      on the EntityJoin I can only put addCondition a unique condition, couldn't I put a range condition?

      I could create a compositite key, or even separate the search on different databases or structures, but I think I should have a way to do this just with the fields of an Entity.
        • 1. Re: EntityJoin with range
          You can only use EntityJoin for ANDing two or more equality conditions together, when there is a secondary index on each field. You cannot use BDB's Join for any other situations.

          To do:
          employeeName >= 'key1' AND employeeName <= 'key2' AND departmentId = key3
          You must chose either the range or equality condition to perform using a secondary key, and then filter (discard the non-matching results) for the other condition.

          You can either:

          1) Use a range query for this part: employeeName >= 'key1' AND employeeName <= 'key2'
          and filter on: departmentId = key3


          2) Use a range query (with the same key for both parameters) for this part: departmentId = key3
          and filter on: employeeName >= 'key1' AND employeeName <= 'key2'

          There are several other discussions on the forum discussing this question, so you may also want to search for "query" in this forum. Be sure to set the date range to All.

          • 2. Re: EntityJoin with range
            first I read the oracle manuals and after I read the topics about query but it was not clear to me the kind of query I want to do.

            What you mean and what I understood reading the topics related to query doing a range search uses SortedMap or etities(fromKey, from inclusive... with range from Berkeley DB ok?

            So I did a range query by employeeName, ex:
            //secondKey.entities("fromKey", fromInclusive, "toKey", toInclusive)
            // in my case:
            EntityCursor<EnEmployee> employeeEntitiesCursor = secondKeyEmployeeName.entities('key1', true, 'key2', true);
            But this returned employeeEntitiesCursor that don't allow me to perform a second search by anything.

            What I found is this:

            I can do a search by subIndex:
            and after to do a search by range:
            secondaryKey.subIndex(value).entities("fromKey", fromInclusive, "toKey", toInclusive)
            But there is a problem with this search, I can only filter by value a secondaryKey, and after by the *"fromKey" and "toKey", * the primary Key.

            In my case I filter by employeeName and departamentId, two secondary keys of Employee entity, so the result of search doing by subIndex and by entities can just filter by departamentId and by employeeId (not by departamentId and employeeName)

            Check the entities taken from page 4 of http://www.oracle.com/technetwork/products/berkeleydb/performing.pdf
            class Employee { 
                int employeeId; 
                /* Many Employees may have the same name. */ 
                String employeeName; 
                /* Many Employees may have the same salary. */ 
                float salary; 
                Integer managerId; // Use "Integer" to allow null 
                                       // values. 
                Integer departmentId; 
                String address; 
                public Employee(int employeeId, 
                                   String employeeName, 
                                   float salary, 
                                   Integer managerId, 
                                   int departmentId, 
                                   String address) {         
                    this.employeeId = employeeId; 
                    this.employeeName = employeeName; 
                    this.salary = salary; 
                    this.managerId = managerId; 
                    this.departmentId = departmentId; 
                    this.address = address; 
                private Employee() {} // Needed for deserialization 
            class Department { 
                int departmentId;
                String departmentName; 
                String location;  
                public Department(int departmentId, 
                                      String departmentName, 
                                      String location) { 
                    this.departmentId = departmentId; 
                    this.departmentName = departmentName; 
                    this.location = location; 
                private Department() {} // Needed for deserialization 
            So as far as I know it's not possible to do a range search by employeeName and by departamentId (two secondary key) on the Employee Entity even with query passing range.

            At the moment what I'm doing is separating the databases by departamentId which is not a good solution.
            • 3. Re: EntityJoin with range
              So as far as I know it's not possible to do a range search by employeeName and by departamentId (two secondary key) on the Employee Entity even with query passing range.
              Yes, it is not possible to use more than one secondary key to do a range search. So you must pick one key and use it to do the range search. Then do filtering to implement the rest of the query.

              Filtering can always be done on any attribute of the entity, because you have the entity object in hand. Filtering is not implemented using a secondary key index, it is implemented by just checking the attributes and discarding any entities that don't match, i.e., with an "if" statement.