2

I am using @Multitenant(SINGLE_TABLE) to support multi-tenancy. To fix the n+1 problem, I use batch fetch feature of eclipselink.

@Entity
public class TestEntity implements Serializable {
    @Id
    @Column
    private Long id;

    @OneToMany(mappedBy = "testEntity", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @BatchFetch(BatchFetchType.IN)
    private List<TestEntityLine> lines = new ArrayList<>();
}

@Entity
public class TestEntityLine implements Serializable {
    @Id
    @Column
    private Long id;

    @JoinColumn(name = "PID", referencedColumnName = "ID", nullable = true)
    private TestEntity testEntity;
}

When I query using:

   String boql = "SELECT e FROM TestEntity e order by e.id";
   query.setFirstResult(1); // for pagination
   query.setMaxResults(3); // for pagination

The sql and results log:

2016-06-16 10:05:14.558 [main] DEBUG o.e.p.s./.sql - SELECT ID AS a1 FROM TESTENTITY ORDER BY ID LIMIT ? OFFSET ?
    bind => [3, 1]
entity-2
2016-06-16 10:05:14.594 [main] DEBUG o.e.p.s./.sql - SELECT ID, NAME, VALUE, PID FROM TESTENTITYLINE WHERE (PID IN (?,?,?))
    bind => [2, 3, 4]
entityLine-2-3
entityLine-2-1
entityLine-2-2
entity-3
entityLine-3-2
entityLine-3-1
entityLine-3-3
entity-4
entityLine-4-3
entityLine-4-2
entityLine-4-1

All of these works perfectly. But when I enable multi-tenant:

@Entity
public class TestMultiTenantEntity implements Serializable {
    @Id
    @Column
    private Long id;

    @OneToMany(mappedBy = "testEntity", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @BatchFetch(BatchFetchType.IN)
    private List<TestMultiTenantEntityLine> lines = new ArrayList<>();
}

@Entity
public class TestMultiTenantEntityLine implements Serializable {
    @Id
    @Column
    private Long id;

    @JoinColumn(name = "PID", referencedColumnName = "ID", nullable = true)
    private TestMultiTenantEntity testEntity;
}

@MappedSuperclass
@Multitenant(SINGLE_TABLE)
@TenantDiscriminatorColumn(discriminatorType = DiscriminatorType.INTEGER, primaryKey = true, name = MultiTenantSupport.TENANT_COLUMN_NAME, length = 12, contextProperty = MultiTenantSupport.MULTITENANT_CONTEXT_PROPERTY)
public abstract class MultiTenantSupport {

    public static final String MULTITENANT_CONTEXT_PROPERTY = "tenant_id";

    public static final String TENANT_COLUMN_NAME = "TENANT_ID";

}

When I query using:

String boql = "SELECT e FROM TestEntity e order by e.id"; query.setFirstResult(1); // for pagination query.setMaxResults(3); // for pagination

The sql and results log:

2016-06-16 10:17:17.123 [main] DEBUG o.e.p.s./.sql - SELECT TENANT_ID AS a1, ID AS a2 FROM TESTMULTITENANTENTITY WHERE (TENANT_ID = ?) ORDER BY ID LIMIT ? OFFSET ?
    bind => [1, 3, 1]
entity-2
2016-06-16 10:17:17.159 [main] DEBUG o.e.p.s./.sql - SELECT t0.TENANT_ID, t0.ID, t0.NAME, t0.VALUE, t0.PID FROM TESTMULTITENANTENTITYLINE t0, TESTMULTITENANTENTITYLINE t1 WHERE ((((t1.TENANT_ID, t1.PID) IN ((?,?),(?,?),(?,?))) AND (t0.TENANT_ID = ?)) AND (t1.TENANT_ID = ?))
    bind => [1, 2, 1, 3, 1, 4, 1, 1]  **?????? Why TESTMULTITENANTENTITYLINE joins to itself t0/t1? This produce many duplicated records**
2016-06-16 10:17:17.227 [main] DEBUG o.e.p.s./.sql - SELECT TENANT_ID, ID FROM TESTMULTITENANTENTITY WHERE (((TENANT_ID = ?) AND (ID = ?)) AND (TENANT_ID = ?))
    bind => [1, 1, 1] **?????? I try to fetch record 2,3,4, why it fetch out 1, 5, 6?**
2016-06-16 10:17:17.267 [main] DEBUG o.e.p.s./.sql - SELECT TENANT_ID, ID FROM TESTMULTITENANTENTITY WHERE (((TENANT_ID = ?) AND (ID = ?)) AND (TENANT_ID = ?))
    bind => [1, 5, 1]
2016-06-16 10:17:17.295 [main] DEBUG o.e.p.s./.sql - SELECT TENANT_ID, ID FROM TESTMULTITENANTENTITY WHERE (((TENANT_ID = ?) AND (ID = ?)) AND (TENANT_ID = ?))
    bind => [1, 6, 1]
entityLine-2-1
entityLine-2-1
entityLine-2-1
entityLine-2-1
entityLine-2-1
entityLine-2-1
entityLine-2-1
entityLine-2-1
entityLine-2-1
entityLine-2-2
entityLine-2-2
entityLine-2-2
entityLine-2-2
entityLine-2-2
entityLine-2-2
entityLine-2-2
entityLine-2-2
entityLine-2-2
entityLine-2-3
entityLine-2-3
entityLine-2-3
entityLine-2-3
entityLine-2-3
entityLine-2-3
entityLine-2-3
entityLine-2-3
entityLine-2-3
entity-3
entityLine-3-1
entityLine-3-1
entityLine-3-1
entityLine-3-1
entityLine-3-1
entityLine-3-1
entityLine-3-1
entityLine-3-1
entityLine-3-1
entityLine-3-2
entityLine-3-2
entityLine-3-2
entityLine-3-2
entityLine-3-2
entityLine-3-2
entityLine-3-2
entityLine-3-2
entityLine-3-2
entityLine-3-3
entityLine-3-3
entityLine-3-3
entityLine-3-3
entityLine-3-3
entityLine-3-3
entityLine-3-3
entityLine-3-3
entityLine-3-3
entity-4
entityLine-4-1
entityLine-4-1
entityLine-4-1
entityLine-4-1
entityLine-4-1
entityLine-4-1
entityLine-4-1
entityLine-4-1
entityLine-4-1
entityLine-4-2
entityLine-4-2
entityLine-4-2
entityLine-4-2
entityLine-4-2
entityLine-4-2
entityLine-4-2
entityLine-4-2
entityLine-4-2
entityLine-4-3
entityLine-4-3
entityLine-4-3
entityLine-4-3
entityLine-4-3
entityLine-4-3
entityLine-4-3
entityLine-4-3
entityLine-4-3

Why TESTMULTITENANTENTITYLINE joins to itself t0/t1? This produce many duplicated records

I try to fetch record 2,3,4, why it fetch out 1, 5, 6?

Could you please help? is this a bug? Or I missed something? Thanks in advance.

Jacky
  • 8,619
  • 7
  • 36
  • 40
  • Looks like EclipseLink is treating the tenant ID as a part of the Entity, making it a composite PK behind the scenes as the entity's id field does not need to be unique across all tenants. This would be covered by the documentation stating BatchFetchType.IN "On some databases, this may only work for singleton IDs". You might try the latest version and file a bug regardless, as EclipseLink could handle it differently to account for this, and use one of the other batchFetchType options instead. – Chris Jun 16 '16 at 17:32
  • @Chris thanks for your comments. I tried the latest version 2.6.3 and it does not work either. I tried other options, JOIN will not produce duplicated records, but it also tries to fetch unneeded records. Also, it seems only IN can support good for pagination. – Jacky Jun 17 '16 at 01:34
  • Sorry, I miss read the results. The extra table being included looks like a bug with the query processing. You can try removing the pagination and play with different options to see if you can help isolate the components involved that is causing the query to misbehave. If it happens with other options, it might make it easier to narrow down where a fix should go. – Chris Jun 17 '16 at 14:08

1 Answers1

1

It works after I removed tenant_id from the primary key by the following setting:

@TenantDiscriminatorColumn(primaryKey = false, ...)

But still, JOIN and EXIST option does not work although I do not need them. It looks that in JOIN and EXIST sql the needed pids are not included in the where condition. and that's why it loads all other unneeded entries

Jacky
  • 8,619
  • 7
  • 36
  • 40