This discussion is archived
3 Replies Latest reply: Feb 16, 2013 12:53 PM by greybird RSS

EntityJoin with range

user13358531 Newbie
Currently Being Moderated
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
    greybird Expert
    Currently Being Moderated
    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

    OR

    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.

    --mark                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 2. Re: EntityJoin with range
    user13358531 Newbie
    Currently Being Moderated
    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:
    secondaryKey.subIndex(value) 
    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
    @Entity 
    class Employee { 
        @PrimaryKey 
        int employeeId; 
        /* Many Employees may have the same name. */ 
        @SecondaryKey(relate=MANY_TO_ONE) 
        String employeeName; 
        /* Many Employees may have the same salary. */ 
        @SecondaryKey(relate=MANY_TO_ONE) 
        float salary; 
        @SecondaryKey(relate=MANY_TO_ONE, 
                         relatedEntity=Employee.class,   
                         onRelatedEntityDelete=NULLIFY)
        Integer managerId; // Use "Integer" to allow null 
                               // values. 
        @SecondaryKey(relate=MANY_TO_ONE, 
                         relatedEntity=Department.class, 
                         onRelatedEntityDelete=NULLIFY)
        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 
    } 
    
    @Entity 
    class Department { 
        @PrimaryKey 
        int departmentId;
        @SecondaryKey(relate=ONE_TO_ONE) 
        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
    greybird Expert
    Currently Being Moderated
    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.

    --mark                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points