CODEWITHSUNDEEP
javahibernatejpahibernate-criteria
Sebastian Lang
Sebastian Lang

Reputation: 492

Criteria API createAlias on column containing null values kills the query

I do have a simple entity named "Address" that has a couple of properties and relations defined by itself and some inherited from some superclass.

public class Base {
  private UUID id;
  private Date createdAt;
  @NotNull
  private User createdBy;  // User is a related Entity that cannot be null
  private DataImport dataImport;  // DataImport is another related entity that can be null
  //getter & setter
}

public class Address extends Base {
  private String street;
  @NotNull
  private Country country;  // related entity that can't be null
  //getter & setter
}

What I'm trying to achieve is with one query using the Criteria API, I want to get a list of Address objects containing all simple attributes like street and createdAt. At the same time I want only the IDs of the related entities if present: createdBy.id, dataImport.id and country.id.

I'm almost there using the following Criteria query:

entityManager.getDelegate().createCriteria(Address.class).add(criteriaExample)
 .excludeZeroes()
 .createAlias("createdBy", "subcreatedBy")
 .createAlias("country", "subcountry")
 .setProjection(Projections.projectionList().add(Projections.property("id").as("id"))
    .add(Projections.property("createdAt").as("createdAt"))
    .add(Projections.property("street").as("street")).
    .add(Projections.property("subcreatedBy.id").as("createdBy.id"))
    .add(Projections.property("subcountry.id").as("country.id")))
  .setResultTransformer(new AliasToBeanNestedResultTransformer(Address.class));

List<Address> result = criteria.list();

This works just perfect!

Problem occurs when I only add the "alias" for the dataImport relation.

...createAlias("dataImport", "subdataImport")...

Even without adding the Projection for dataImport.id to the query, it returns an empty list, meaning list.size() = 0, as soon as I add this alias.

My current guess is, that I can't put an alias on a nullable property. Does anybody have an idea what the solution might be? So, when the related entity is not null, I want to get it's ID. And I want it to be simply null, when the relation is not set.

Thanks in advance.

Upvotes: 1

Views: 878

Answers (1)

Sebastian Lang
Sebastian Lang

Reputation: 492

Stupid me should have read the documentation and set the CriteriaSpecification.LEFT_JOIN.

...createAlias("dataImport", "subdataImport", CriteriaSpecification.LEFT_JOIN)...

Upvotes: 1

Related Questions