Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Criteria API joinList inheritance Problem
Criteria API joinList inheritance Problem [message #1096340] Wed, 28 August 2013 04:57 Go to next message
Philip Mair is currently offline Philip Mair
Messages: 5
Registered: July 2013
Junior Member
I have following example:

private static void runDemo( EntityManager em )
    {
        em.getTransaction().begin();
        
        TestEntity e;
        TestKeyValueEntity kve;
        
        //lets create 10 dummy data, every one will have a TestEntityMultiExtensionA instance and a TestKeyValueEntity with key "A"
        //5 of them will have a TestEntityMultiExtensionB instance and 2 TestKeyValueEntities with keys "B" and "C"
        for( int i=0; i<10; i++ )
        {
            e = new TestEntity();

            e.extensions = new ArrayList<TestEntityMultiExtension>();
            e.customValues = new ArrayList<TestKeyValueEntity>();
            
            TestEntityMultiExtensionA meA = new TestEntityMultiExtensionA();
            meA.customValueA = "multi A"+i;
            e.extensions.add( meA );
            
            if( i % 2 == 0 )
            {
                TestEntityMultiExtensionB meB = new TestEntityMultiExtensionB();
                meB.customValueB = "multi B"+i;
                e.extensions.add( meB );
                
                kve = new TestKeyValueEntity();
                kve.setKey( "B" );
                kve.setValue( "bValue" + i );
                e.customValues.add( kve ); 
                
                kve = new TestKeyValueEntity();
                kve.setKey( "C" );
                kve.setValue( "cValue" + i );
                e.customValues.add( kve ); 
            }

            
            
            kve = new TestKeyValueEntity();
            kve.setKey( "A" );
            kve.setValue( "aValue" + i );
            e.customValues.add( kve ); 
            
            em.persist( e );
        }
        em.getTransaction().commit();
        
        
        final CriteriaBuilder cb = em.getCriteriaBuilder();
        final CriteriaQuery<Tuple> cq = cb.createTupleQuery();
        
        final Root<TestEntity> root = cq.from( TestEntity.class );

        final List<Selection<?>> selections = new LinkedList<Selection<?>>();

        selections.add( root.get( "id" ) );
        
        final ListJoin<?,?> joinA = root.joinList( "customValues", JoinType.LEFT );
        joinA.on( cb.equal( joinA.get( "key"), "A" ) );
        selections.add( joinA.get( "value" ) );
        
        final ListJoin<?,?> joinB = root.joinList( "customValues", JoinType.LEFT );
        joinB.on( cb.equal( joinB.get( "key"), "B" ) );
        selections.add( joinB.get( "value" ) );
        
        final ListJoin<?,?> joinC = root.joinList( "customValues", JoinType.LEFT );
        joinC.on( cb.equal( joinC.get( "key"), "C" ) );
        selections.add( joinC.get( "value" ) );
        
        final ListJoin<?,?> joinD = root.joinList( "customValues", JoinType.LEFT );
        joinD.on( cb.equal( joinD.get( "key"), "D" ) );
        selections.add( joinD.get( "value" ) );
        
        cq.multiselect( selections );
        
        List<Tuple> result = em.createQuery( cq ).getResultList();
        
        //without joining to extensions i get 10 rows:
        for( Tuple r : result )
            System.out.println( Arrays.toString( r.toArray() ) );
        
        
        //now let us join to TestEntityMultiExtensionA and TestEntityMultiExtensionB:
        final ListJoin<?,?> testMultiJoinA = (ListJoin<?,?>) root.joinList( "extensions", JoinType.LEFT ).as( TestEntityMultiExtensionA.class );
        selections.add( testMultiJoinA.get( "customValueA" ) );
        
        final ListJoin<?,?> testMultiJoinB = (ListJoin<?,?>) root.joinList( "extensions", JoinType.LEFT ).as( TestEntityMultiExtensionB.class );
        selections.add( testMultiJoinB.get( "customValueB" ) );
        
        cq.multiselect( selections );
        
        result = em.createQuery( cq ).getResultList();
        
        //now we get 25 rows, because of the left-joins missing type='tests_multi_extensions_[a,b]' in on clause
        for( Tuple r : result )
            System.out.println( Arrays.toString( r.toArray() ) );
        
        em.close();
    }


Output of Demo:
[EL Fine]: sql: 2013-08-28 10:48:54.642--ServerSession(978758920)--Connection(506212352)--SELECT t0.ID, t1.val, t2.val, t3.val, t4.val FROM TESTS t0 LEFT OUTER JOIN TEST_KEY_VALUES t1 ON ((t1.test_id = t0.ID) AND (t1.key = ?)) LEFT OUTER JOIN TEST_KEY_VALUES t2 ON ((t2.test_id = t0.ID) AND (t2.key = ?)) LEFT OUTER JOIN TEST_KEY_VALUES t3 ON ((t3.test_id = t0.ID) AND (t3.key = ?)) LEFT OUTER JOIN TEST_KEY_VALUES t4 ON ((t4.test_id = t0.ID) AND (t4.key = ?))
	bind => [A, B, C, D]
[1, aValue0, bValue0, cValue0, null]
[2, aValue1, null, null, null]
[3, aValue2, bValue2, cValue2, null]
[4, aValue3, null, null, null]
[5, aValue4, bValue4, cValue4, null]
[6, aValue5, null, null, null]
[7, aValue6, bValue6, cValue6, null]
[8, aValue7, null, null, null]
[9, aValue8, bValue8, cValue8, null]
[10, aValue9, null, null, null]
[EL Fine]: sql: 2013-08-28 10:48:54.648--ServerSession(978758920)--Connection(506212352)--SELECT t0.ID, t1.val, t2.val, t3.val, t4.val, t6.custom_value_a, t8.custom_value_b FROM TESTS t0 LEFT OUTER JOIN TEST_KEY_VALUES t1 ON ((t1.test_id = t0.ID) AND (t1.key = ?)) LEFT OUTER JOIN TEST_KEY_VALUES t2 ON ((t2.test_id = t0.ID) AND (t2.key = ?)) LEFT OUTER JOIN TEST_KEY_VALUES t3 ON ((t3.test_id = t0.ID) AND (t3.key = ?)) LEFT OUTER JOIN TEST_KEY_VALUES t4 ON ((t4.test_id = t0.ID) AND (t4.key = ?)) LEFT OUTER JOIN TESTS_MULTI_EXTENSIONS t5 ON (t5.test_id = t0.ID) LEFT OUTER JOIN TESTS_MULTI_EXTENSIONS_A t6 ON (t6.ID = t5.ID) LEFT OUTER JOIN TESTS_MULTI_EXTENSIONS t7 ON (t7.test_id = t0.ID) LEFT OUTER JOIN TESTS_MULTI_EXTENSIONS_B t8 ON (t8.ID = t7.ID)
	bind => [A, B, C, D]
[1, aValue0, bValue0, cValue0, null, multi A0, null]
[1, aValue0, bValue0, cValue0, null, multi A0, multi B0]
[1, aValue0, bValue0, cValue0, null, null, null]
[1, aValue0, bValue0, cValue0, null, null, multi B0]
[2, aValue1, null, null, null, multi A1, null]
[3, aValue2, bValue2, cValue2, null, multi A2, null]
[3, aValue2, bValue2, cValue2, null, multi A2, multi B2]
[3, aValue2, bValue2, cValue2, null, null, null]
[3, aValue2, bValue2, cValue2, null, null, multi B2]
[4, aValue3, null, null, null, multi A3, null]
[5, aValue4, bValue4, cValue4, null, multi A4, null]
[5, aValue4, bValue4, cValue4, null, multi A4, multi B4]
[5, aValue4, bValue4, cValue4, null, null, null]
[5, aValue4, bValue4, cValue4, null, null, multi B4]
[6, aValue5, null, null, null, multi A5, null]
[7, aValue6, bValue6, cValue6, null, multi A6, null]
[7, aValue6, bValue6, cValue6, null, multi A6, multi B6]
[7, aValue6, bValue6, cValue6, null, null, null]
[7, aValue6, bValue6, cValue6, null, null, multi B6]
[8, aValue7, null, null, null, multi A7, null]
[9, aValue8, bValue8, cValue8, null, multi A8, null]
[9, aValue8, bValue8, cValue8, null, multi A8, multi B8]
[9, aValue8, bValue8, cValue8, null, null, null]
[9, aValue8, bValue8, cValue8, null, null, multi B8]
[10, aValue9, null, null, null, multi A9, null]



If i use JoinType.INNER with the extensions listJoins the DiscriminatorColumn Restriction will be added to where clause, but this means that i just can read those entities which have both instances in extensions list.

The next problem is, that a on-clause like shown on 'customValues' will be ignored.

Any suggestions?

Entities:
@Entity(name="tests")
public class TestEntity {

    @Id
    @TableGenerator(
            name="TestEntityIdGenerator", 
            table="jpa_hilo",
            pkColumnName="name",
            valueColumnName="hilo",
            pkColumnValue="TestEntity",
            allocationSize=127
    )
    @GeneratedValue( strategy=GenerationType.TABLE, generator="TestEntityIdGenerator" )
    public long id;
    
    @OneToMany(cascade={CascadeType.ALL})
    @JoinColumn(name="test_id")
    public List<TestEntityMultiExtension> extensions;
    
    @OneToMany(cascade={CascadeType.ALL})
    @JoinColumn(name="test_id")
    public List<TestKeyValueEntity> customValues;
    
    public TestEntity() 
    {
        
    }
}

@Entity(name="tests_multi_extensions")
@Inheritance( strategy=InheritanceType.JOINED )
@DiscriminatorColumn( name="type", discriminatorType=DiscriminatorType.STRING )
public abstract class TestEntityMultiExtension 
{
    @Id
    @TableGenerator(
            name="TestEntityMultiExtensionIdGenerator", 
            table="jpa_hilo",
            pkColumnName="name",
            valueColumnName="hilo",
            pkColumnValue="TestEntityMultiExtension"
    )
    @GeneratedValue( strategy=GenerationType.TABLE, generator="TestEntityMultiExtensionIdGenerator" )
    public long id;
}

@Entity(name="tests_multi_extensions_a")
public class TestEntityMultiExtensionA extends TestEntityMultiExtension {

    
    @Column(name = "custom_value_a")
    public String customValueA;
    
    public TestEntityMultiExtensionA() {
        
    }

}

@Entity(name="tests_multi_extensions_b")
public class TestEntityMultiExtensionB extends TestEntityMultiExtension {

    @Column(name = "custom_value_b")
    public String customValueB;
    
    public TestEntityMultiExtensionB() {
    }

}

@javax.persistence.Entity(name="test_key_values")
public class TestKeyValueEntity {

    @Id
    @TableGenerator(
            name="TestKeyValueEntityIdGenerator", 
            table="jpa_hilo",
            pkColumnName="name",
            valueColumnName="hilo",
            pkColumnValue="TestKeyValueEntity"
    )
    @GeneratedValue( strategy=GenerationType.TABLE, generator="TestKeyValueEntityIdGenerator" )
    public long id;

    @Column(name="key")
    private String key;
    @Column(name="val")
    private String value;
    
    public String getKey() {
        return key;
    }
    
    public void setKey(String key) {
        this.key = key;
    }
    
    public String getValue() {
        return value;
    }
    public void setValue(String value) {
        this.value = value;
    }

}

[Updated on: Wed, 28 August 2013 06:40]

Report message to a moderator

Re: Criteria API joinList inheritance Problem [message #1096465 is a reply to message #1096340] Wed, 28 August 2013 08:32 Go to previous message
Philip Mair is currently offline Philip Mair
Messages: 5
Registered: July 2013
Junior Member
I found one Workaround for my Problem:

@Entity(name="tests_multi_extensions")
@Inheritance( strategy=InheritanceType.JOINED )
@DiscriminatorColumn( name="type", discriminatorType=DiscriminatorType.STRING )
//Customizer for this Entity
@Customizer( ExtensionCustomizer.class )
public abstract class TestEntityMultiExtension 
{
    @Id
    @TableGenerator(
            name="TestEntityMultiExtensionIdGenerator", 
            table="jpa_hilo",
            pkColumnName="name",
            valueColumnName="hilo",
            pkColumnValue="TestEntityMultiExtension"
    )
    @GeneratedValue( strategy=GenerationType.TABLE, generator="TestEntityMultiExtensionIdGenerator" )
    public long id;
    
    //non public field named like DiscriminatorColumn
    @Column(name="type")
    private String type;
}

public class ExtensionCustomizer implements DescriptorCustomizer {
    @Override
    public void customize(ClassDescriptor descriptor) throws Exception {
        //use full qualified class name instead of table name
        descriptor.getInheritancePolicy().useClassNameAsIndicator();
    }
}

....

        final ListJoin<?,?> testMultiJoinA = root.joinList( "extensions", JoinType.LEFT );
        //calling "on" before calling "as" works for restrictions based on TestEntityMultiExtension
        testMultiJoinA
                .on( cb.equal( testMultiJoinA.get("type"), TestEntityMultiExtensionA.class.getName() ) ) //manual restriction to TestEntityMultiExtensionA
                .as( TestEntityMultiExtensionA.class );
        
        selections.add( testMultiJoinA.get( "customValueA" ) );
        
        final ListJoin<?,?> testMultiJoinB = root.joinList( "extensions", JoinType.LEFT );
        testMultiJoinB
                .on( cb.equal( testMultiJoinB.get("type"), TestEntityMultiExtensionB.class.getName() ) ) //manual restriction to TestEntityMultiExtensionB
                .as( TestEntityMultiExtensionB.class );
        
        selections.add( testMultiJoinB.get( "customValueB" ) );
....


Output:
[EL Fine]: sql: 2013-08-28 14:30:53.087--ServerSession(1538209352)--Connection(1625718196)--SELECT t0.ID, t1.val, t2.val, t3.val, t4.val FROM TESTS t0 LEFT OUTER JOIN TEST_KEY_VALUES t1 ON ((t1.test_id = t0.ID) AND (t1.key = ?)) LEFT OUTER JOIN TEST_KEY_VALUES t2 ON ((t2.test_id = t0.ID) AND (t2.key = ?)) LEFT OUTER JOIN TEST_KEY_VALUES t3 ON ((t3.test_id = t0.ID) AND (t3.key = ?)) LEFT OUTER JOIN TEST_KEY_VALUES t4 ON ((t4.test_id = t0.ID) AND (t4.key = ?))
	bind => [A, B, C, D]
[1, aValue0, bValue0, cValue0, null]
[2, aValue1, null, null, null]
[3, aValue2, bValue2, cValue2, null]
[4, aValue3, null, null, null]
[5, aValue4, bValue4, cValue4, null]
[6, aValue5, null, null, null]
[7, aValue6, bValue6, cValue6, null]
[8, aValue7, null, null, null]
[9, aValue8, bValue8, cValue8, null]
[10, aValue9, null, null, null]
[EL Fine]: sql: 2013-08-28 14:30:53.093--ServerSession(1538209352)--Connection(1625718196)--SELECT t0.ID, t1.val, t2.val, t3.val, t4.val, t6.custom_value_a, t8.custom_value_b FROM TESTS t0 LEFT OUTER JOIN TEST_KEY_VALUES t1 ON ((t1.test_id = t0.ID) AND (t1.key = ?)) LEFT OUTER JOIN TEST_KEY_VALUES t2 ON ((t2.test_id = t0.ID) AND (t2.key = ?)) LEFT OUTER JOIN TEST_KEY_VALUES t3 ON ((t3.test_id = t0.ID) AND (t3.key = ?)) LEFT OUTER JOIN TEST_KEY_VALUES t4 ON ((t4.test_id = t0.ID) AND (t4.key = ?)) LEFT OUTER JOIN TESTS_MULTI_EXTENSIONS t5 ON ((t5.test_id = t0.ID) AND (t5.type = ?)) LEFT OUTER JOIN TESTS_MULTI_EXTENSIONS_A t6 ON (t6.ID = t5.ID) LEFT OUTER JOIN TESTS_MULTI_EXTENSIONS t7 ON ((t7.test_id = t0.ID) AND (t7.type = ?)) LEFT OUTER JOIN TESTS_MULTI_EXTENSIONS_B t8 ON (t8.ID = t7.ID)
	bind => [A, B, C, D, tests.TestEntityMultiExtensionA, tests.TestEntityMultiExtensionB]
[1, aValue0, bValue0, cValue0, null, multi A0, multi B0]
[2, aValue1, null, null, null, multi A1, null]
[3, aValue2, bValue2, cValue2, null, multi A2, multi B2]
[4, aValue3, null, null, null, multi A3, null]
[5, aValue4, bValue4, cValue4, null, multi A4, multi B4]
[6, aValue5, null, null, null, multi A5, null]
[7, aValue6, bValue6, cValue6, null, multi A6, multi B6]
[8, aValue7, null, null, null, multi A7, null]
[9, aValue8, bValue8, cValue8, null, multi A8, multi B8]
[10, aValue9, null, null, null, multi A9, null]


For me its a bug, cause the restriction will be added automatically if INNER JOIN is used.
Previous Topic:Jpa UNION and MySql
Next Topic:How to obtain sequence value?
Goto Forum:
  


Current Time: Thu Jul 24 02:32:04 EDT 2014

Powered by FUDForum. Page generated in 0.01593 seconds