Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » duplicate tenant_id in WHERE clause for @Multitenant(@Multitenant support - duplicate tenant_ids tests in generated SQL)
duplicate tenant_id in WHERE clause for @Multitenant [message #1753030] Wed, 01 February 2017 09:34
Nick Stephen is currently offline Nick StephenFriend
Messages: 1
Registered: February 2017
Junior Member
Hi.

I'm using single-table multi-tenancy, with the tenant_id column being a part of the PK of each row.

When I look at the SQL generated by em.find() it is including the tenant_id *twice* in the generated SQL. This is leading to the DB generating multiple execution plans and sometimes choosing a poor optimisation choice.

An example of generated code from an em.find() on a multi-tenant table:

SELECT TENANT_ID, REF_ID, VAL FROM TEST_TABLE WHERE (((TENANT_ID = ?) AND (REF_ID = ?)) AND (TENANT_ID = ?))
bind => [1, R1, 1]

Note that TENANT_ID is specified twice in the query.

If I replace em.find() by a simple query

select i from Resource where i.refId=:id

then I get the expected query form with TENANT_ID just specified once:

SELECT TENANT_ID, REF_ID, VAL FROM TEST_TABLE WHERE ((REF_ID = ?) AND (TENANT_ID = ?))
bind => [R1, 1]

Here is my JPA entity:

@Entity
@Table(name = "TEST_TABLE")
@XmlRootElement
@Multitenant
@TenantDiscriminatorColumn(name = "TENANT_ID", contextProperty = "tenant_id", primaryKey = true)
@NamedQueries({
@NamedQuery(name = "Resource.findAll", query = "SELECT t FROM Resource t"),
@NamedQuery(name = "Resource.findByRefId", query = "SELECT t FROM Resource t WHERE t.refId = :refId"),
@NamedQuery(name = "Resource.findByVal", query = "SELECT t FROM Resource t WHERE t.val = :val")})
@IdClass(ResourcePK.class)
public class Resource implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@Column(name = "REF_ID")
protected String refId;

@Column(name = "VAL")
private Integer val;

public Resource() {
}

public Integer getVal() {
return val;
}

public void setVal(Integer val) {
this.val = val;
}

// plus hashCode, equals, toString
}

public class ResourcePK implements Serializable {

@Basic(optional = false)
@Column(name = "REF_ID")
private String refId;

public ResourcePK() {
}

public ResourcePK(String refId) {
this.refId = refId;
}


public String getRefId() {
return refId;
}

public void setRefId(String refId) {
this.refId = refId;
}

// plus hashCode, equals, toString
}


If I remove the 'primaryKey = true' from tenant discriminator column then the query takes the expected form, but 'deletes' do not include the tenantId .

reproduced using eclipselink JPA 2.1 in netbeans 8.1

Anything I'm doing wrong, or is this a bug?

Thx


Previous Topic:How to add a Window Function to a JPA Criteria Query?
Next Topic:moxy is not able to convert a List<String> to JSON
Goto Forum:
  


Current Time: Fri Dec 15 10:31:57 GMT 2017

Powered by FUDForum. Page generated in 0.01137 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software