CODEWITHSUNDEEP
hibernatejpaspring-data-jpacriteriacriteria-api
LimitX
LimitX

Reputation: 625

Find elements of specific type in single table inheritance type

Imagine the following Entity hierarchy:

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "type", columnDefinition = "varchar(60)")
abstract class Resource {

}


@Entity
@DiscriminatorValue("resource1")
class Resource1 extends Resource {
  @Embedded
  private Property property1;
}

@Entity
@DiscriminatorValue("resource2")
class Resource2 extends Resource {
  @Embedded
  private Property property2;
}

@Entity
@DiscriminatorValue("resource3")
class Resource3 extends Resource {
  @Embedded
  private Property property3;
}

@Entity
@DiscriminatorValue("resource4")
class Resource4 extends Resource {
  @Embedded
  private Property property4;
}

@Entity
class EntityUsingResource {
  @OneToMany(...)
  @JoinColumn(...)
  private List<Resource> resources;
}

I am trying to create a UI to search for EntityUsingResources and being able to filter for elements that have resources with a specific property.

So in the search field in the GUI you can type a value for lets say property4 and it filters all EntityUsingResources which have a resource of type Resource4 whos property4 equals the one you typed in.

So far I managed to do this by using this criteria api using springs specifications:

public static Specification<EntityUsingResource> 
withResource4HavingProperty4Like(String property4) {
    Join<EntityUsingResource, Resource> join = 
        root.join(EntityUsingResource_.resources, JoinType.INNER);
    Join<EntityUsingResource, Resource4> treatedJoin = 
      cb.treat(join, Resource4.class);

    return cb.like(
            treatedJoin.get(Resource4_.property4).get(Property_.value), 
            "%" + property4 + "%");
}

public static Specification<EntityUsingResource> 
withResource2HavingProperty2Like(String property2) {
    Join<EntityUsingResource, Resource> join = 
        root.join(EntityUsingResource_.resources, JoinType.INNER);
    Join<EntityUsingResource, Resource2> treatedJoin = 
        cb.treat(join, Resource2.class);

    return cb.like(
        treatedJoin.get(Resource2_.property2).get(Property_.value),
        "%" + property2 + "%");
}

I use those specifications with springs Specifications utility class as follows: where( withResource2HavingProperty2Like(property2) ).and( withResource4HavingProperty4Like(property4) );

Which I then pass to the JpaRepository and more or less return the result to the gui.

This creates the following SQL when searching for property1:

select 
    entity_using_resource0.id as entityId
from 
    entity_using_resource entity_using_resource0
inner join resource resourceas1_ on 
    entity_using_resource0.id=resourceas1_.entity_using_resource_id
inner join resource resourceas2_ on 
    entity_using_resource0.id=resourceas2_.entity_using_resource_id
inner join resource resourceas3_ on 
    entity_using_resource0.id=resourceas3_.entity_using_resource_id
inner join resource resourceas4_ on 
    entity_using_resource0.id=resourceas4_.entity_using_resource_id
where (resourceas4_.property1 like 'property1')
    and (resourceas2_.property1 like '%property1%') limit ...;

The problem is, this query produces a lot of duplicates. I tried using distinct which would solve the problem, but it raises another. In the EntityUsingResource entity I have a column which is of type json so using distinct won't work, as the database can't compare json values.

How would one write a query that also filters for the type of Resource using the criteria api?

Thanks in advance :-)

Upvotes: 0

Views: 980

Answers (1)

Konstantin Triger
Konstantin Triger

Reputation: 1741

If your goal is creating an effective query rather than using criteria API, you may use FluentJPA:

(property2 argument is automatically captured and passed as a parameter)

public List<EntityUsingResource > findEntitiesUsingResource2(String property2) {

    FluentQuery query = FluentJPA.SQL((EntityUsingResource entity) -> {

        List<Long> resourceIds = subQuery((Resource res) -> {

            SELECT(res.getEntityUsingResource().getId());
            FROM(res);
            WHERE(typeOf(res, Resource2.class) &&
              ((Resource2) res).getProperty2().getValue().matches("%" + property2 + "%"));
        });

        SELECT(entity);
        FROM(entity);
        WHERE(resourceIds.contains(entity.getId()));
    });

    return query.createQuery(em, EntityUsingResource.class).getResultList();
}

which produces the following SQL:

SELECT t0.*
FROM entity_using_resource t0
WHERE (t0.id IN (SELECT t1.entity_id
FROM resource t1
WHERE (t1.type = 'resource2' AND (t1.property2 LIKE  CONCAT( CONCAT( '%' ,  ?1 ) ,  '%' )  ))) )

Support of JPA inheritance is documented here.

Upvotes: 1

Related Questions