Searching lists within objects

#1

Hi everyone, 

I've a problem with searching on lists. I'm uncertain as to what the best form is, so I've created a maven project with all the classes and a unit test showing my issue. Pretty much I want to search using cb.isMember passing an object. This however does NOT work. I'm uncertain how the database is doing its matching, but it looks to me like it matches using objectids, which means you can only match isMember if you have a database object. This does not help as I'm never going to have the real object at search time.

Trying to use join is problematic as for every extra attribute I add to the search, I need to add 2 more joins. Obviously this is not satisfactory. 

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<CDI> q = cb.createQuery(CDI.class);
        Root<CDI> cdi = q.from(CDI.class);
       
        Join<CDI, Attribute> attributeJoin = cdi.join("attributeList");
        Join<Attribute, AttributeValue> attributeValuesJoin = attributeJoin.join("attributeValues");
        Join<CDI, Attribute> attributeJoin2 = cdi.join("attributeList");
        Join<Attribute, AttributeValue> attributeValuesJoin2 = attributeJoin2.join("attributeValues");
       
        Predicate pred =
            cb.and(
                cb.and(
                    cb.equal(attributeValuesJoin.get("value"), "Alex"),
                    cb.equal(attributeJoin.get("name"), "firstname")
                ),
                cb.and(
                    cb.equal(attributeValuesJoin2.get("value"), "Baldwin"),
                    cb.equal(attributeJoin2.get("name"), "lastname")
                )
            );
       
        q.select(cdi).where(pred);
        TypedQuery<CDI> typedQuery = em.createQuery(q);
        List<CDI> results = typedQuery.getResultList();

 

If I try to use isMember I have to use:

Attribute attributeLastName = em.getReference(Attribute.class, williamLastNameAttribute.getId());

which is fine in a test where I have the attribute ID, but in the real example I'll never know the id, only what the attribute contains (ie Williams last name).

 

I'm at my wits end over how to solve this. I don't see any way of refactoring my objects to make this search easy without compromising what I'm trying to achieve. Hopefully some will have an answer.

 

Thanks

P.

#2

You are right about comparison of entity objects - it is based on ID comparison.

It is unclear why you manage two levels of nested lists. For the simplicity of this discussion let consider a simpler structure:

@Entity
class CDI {
    private List<Attribute> attributeList = new ArrayList<Attribute>();
}

@Entity
@Unique(members={"type","value"})
class Attribute {
    private AttributeType type;
    private String value;
}

A possible solution:

  • Avoid multiple Attribute instances with identical type / value by defining a composite unique index on type and value (as shown above). Always look for existing attribute before persisting a new one (this could also save space in the database).
  • Split the query - first find the relevant attributes and then find the CDI that contains these attributes.
  • AttributeType can be defined as enum (more efficient).

An alternative solution is to use embedded objects:

@Entity
@Index(members={"attributeList.type","attributeList.value"})
class CDI {
    private List<Attribute> attributeList = new ArrayList<Attribute>();
}

@Embeddable
class Attribute {
    private AttributeType type;
    private String value;
}

Embedded objects are compared by content (and not by ID), so you can build new embeddable objects for the search. However, I am not sure if indexes are currently used in such comparison, so it might be still more efficient to use multiple JOINs in this case.

ObjectDB Support
#3

Okay, I've follow suggestion one. Now I'm running some example queries.

 

        CDI createdCdi1 = createCDI("baffleck", "Ben", "Geza", "Affleck", true);
        CDI createdCdi2 = createCDI("jalba", "Jessica", "Marie", "Alba", true);
        CDI createdCdi3 = createCDI("kbeckinsale", "Kate", null, "Beckinsale", true);
        CDI createdCdi4 = createCDI("abaldwin", "Alex", "Rae", "Baldwin", true);
        CDI createdCdi5 = createCDI("sbaldwin", "Stephen", "Andrew", "Baldwin", true);
        CDI createdCdi6 = createCDI("wbaldwin", "William", null, "Baldwin", true);
       
        String query = "SELECT DISTINCT t FROM com.contextspace.datamodel.party.CDI t JOIN t.attributeList attr WHERE attr.value='Alex' and attr.name='firstname'";
        List<CDI> searchResult = cdiDao.SearchByCustomQuery(query);
        assertEquals(1, searchResult.size());
       
        query = "SELECT DISTINCT t FROM com.contextspace.datamodel.party.CDI t JOIN t.attributeList attr WHERE attr.value='Baldwin' and attr.name='lastname' ";
        searchResult = cdiDao.SearchByCustomQuery(query);
        assertEquals(3, searchResult.size());
       
        query = "SELECT DISTINCT t FROM com.contextspace.datamodel.party.CDI t JOIN t.attributeList attr JOIN t.attributeList attr2 WHERE " +
                "attr.value='Alex' and attr.name='firstname' and attr2.value='Baldwin' and attr2.name='lastname'";
        searchResult = cdiDao.SearchByCustomQuery(query);
        assertEquals(1, searchResult.size());
       
        query = "SELECT DISTINCT t FROM com.contextspace.datamodel.party.CDI t JOIN t.attributeList attr WHERE " +
                "(attr.value='Alex' and attr.name='firstname') or (attr.value='William' and attr.name='firstname')";
        searchResult = cdiDao.SearchByCustomQuery(query);
        assertEquals(2, searchResult.size());

The last query is failing. The exception given is:

[ObjectDB 2.2.7] Unexpected exception (Error 990)
  Generated by Java HotSpot(TM) 64-Bit Server VM 1.6.0_20 (on Linux 2.6.38-8-generic).
Please report this error on http://www.objectdb.com/database/issue/new
com.objectdb.o.InternalException: java.lang.NullPointerException: null
java.lang.NullPointerException
at com.objectdb.o.BQI.Ve(BQI.java:134)
at com.objectdb.o.RQI$h._h(RQI.java:286)
at com.objectdb.o.RQI.Ve(RQI.java:151)
at com.objectdb.o.TQI.Ve(TQI.java:67)
at com.objectdb.o.MQI.Ve(MQI.java:143)
at com.objectdb.o.PRG.ac(PRG.java:691)
at com.objectdb.o.PRG.aa(PRG.java:621)
at com.objectdb.o.QRM.UR(QRM.java:256)
at com.objectdb.o.MST.UR(MST.java:887)
at com.objectdb.o.WRA.UR(WRA.java:286)
at com.objectdb.o.WSM.UR(WSM.java:113)
at com.objectdb.o.QRR.g(QRR.java:220)
at com.objectdb.o.QRR.b(QRR.java:143)
at com.objectdb.jpa.JpaQuery.getResultList(JpaQuery.java:618)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:310)
at $Proxy74.getResultList(Unknown Source)
at com.contextspace.dao.impl.BaseDAOImpl.SearchByCustomQuery(BaseDAOImpl.java:456)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:196)
at $Proxy22.SearchByCustomQuery(Unknown Source)
at com.contextspace.handlers.filter.FilterTest.doCustomQuery(FilterTest.java:165)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
#4

I forgot to attach the example. Please run the junit test. Its the test call queryAttribute5. Thanks.

#5

That was a bug - please try build 2.2.8_02.

If you post a test with many CDI objects and attributes - I can help you with setting indexes to improve the performance of these queries.

by the way, you can use unqualified class names for entities in queries:

SELECT DISTINCT t FROM CDI

instead of:

SELECT DISTINCT t FROM com.contextspace.objectdb_example.model.CDI
ObjectDB Support
#6

Ok, so that fixed the last problem. Now it has issues searching on numbers, even though the number is stored in the database as a string. Please look at unit tests, queryAttribute6 and queryAttribute7.

#7

Test 6 fails because in comparison of Object (Attribute.getValue()) with char literal ('5') ObjectDB considers the literal as character rather than as a string. Only in comparison of a string and a char the char is converted into string. A possible workaround is to use a string parameter instead of a literal.

Test 7 fails because the query is executed using an index and the transient value (Attribute.getValue()) is unavailable. Using transient fields in queries is inefficient and very risky and probably should not be allowed by ObjectDB.

ObjectDB Support
#8

Right, my bad. I assumed that queries referred only to fields within the object. Not realising that the fields within objectdb are populated via the getters. So yes, objectdb shouldn't allow queries on transient fields. Would be much better if a null pointer or some such was thrown for accessing a transient field.

Reply