0

I have the following issue using EclipseLink.

<groupId>org.eclipse.persistence</groupId>
<artifactId>eclipselink</artifactId>
<version>2.6.4</version>

I have a UserEntity:

@Entity
@Table(name = TableNames.CC_Users)
public class UserEntity extends AbstractEntity {

   @EmbeddedId
   private UserId id;

....

   @OneToOne (optional = true, orphanRemoval = true, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
   @PrimaryKeyJoinColumns({
     @PrimaryKeyJoinColumn(name = Columns.vccIdCN, referencedColumnName = Columns.vccIdCN),
     @PrimaryKeyJoinColumn(name = Columns.userIdCN, referencedColumnName = Columns.userIdCN)
   })
   private ClientUserEntity clientUser;

...
}

Then I have the ClientUserEntity:

@Entity
@Table(name = TableNames.CC_Client_Users)
public class ClientUserEntity extends AbstractEntity {

   @EmbeddedId
   private UserId id;

...
}

UserId is an Embedable with 2 string fields: vccId and userId.

When I try to get the users that are not ClientUser using the following JPQL:

"SELECT us FROM " + UserEntity.class.getSimpleName() + " us LEFT JOIN us.clientUser cu WHERE us.id.vccId = :vccId AND cu IS NULL";

I see the following Query in the debugger:

[EL Fine]: sql: ... --Thread(Thread[main,5,main])--SELECT ... t1.language, ... FROM cc_user t1 LEFT OUTER JOIN cc_client_user t0 ON ((t0.user_id = t1.user_id) AND (t0.vcc_id = t1.vcc_id)) WHERE (((t1.vcc_id IS NULL) AND (t1.user_id IS NULL)) AND (t1.vcc_id = ?))

First problem is that JPA didn't generate a proper SQL query, it looks like a JPQL query instead of SQL. Second problem is that the WHERE conditions are wrong. ((t1.vcc_id IS NULL) AND (t1.user_id IS NULL)) those fields are the fields of the embedded id. I don't understand why JPA is adding that condition to the WHERE. Maybe it's an unsupported scenario ?

Something similar happens if I use a different JPQL:

 "SELECT us FROM " + UserEntity.class.getSimpleName() + " us WHERE us.id.vccId = :vccId AND us.clientUser is null"

JPA generates:

SELECT ... FROM cc_user WHERE ((vcc_id = ?) AND ((vcc_id IS NULL) AND (user_id IS NULL)))

In this case, its an SQL, but the WHERE is still wrong. Looks like JPA doesn't support the embedded id scenario for IS NULL case.

I would expect an SQL like explained here.

Should I use Native Query in this case?

Community
  • 1
  • 1
Simon De Uvarow
  • 90
  • 13
  • Finally I decided to add a boolean column in the UserEntity, that is updated each time clientUser changes. **"SELECT us FROM " + UserEntity.class.getSimpleName() + " us WHERE us.id.vccId = :vccId AND us.isCCLevel = true"** – Simon De Uvarow May 02 '17 at 10:09
  • The SQL is correct - it is based on the mappings you've created. The two is Null clauses come from your "cu IS NULL"; there are two foreign keys to the ClientUserEntity, so both would be null if the reference is null. Since the clientUser reference is tied to the primary key, it really shouldn't be changed without special care. My guess is you want to have a basic mapping to the user_id that you can check/set independently from the clientUser reference. – Chris May 02 '17 at 14:15
  • Ok .. Yes Chris. That is what I finally did. I added a "calculated value" in the entity, in this case a boolean, that tells me if the property is null or not. I just have care to update correctly the boolean. thanks! – Simon De Uvarow May 03 '17 at 15:06

0 Answers0