Best way to query a view with different complex where clauses for multiple reports using JPA-CodePudding

I am trying to come up with a nice way of implementing multiple report pages based on the same base entity.

Imagine I want to have multiple reports based on some complex properties of a car - basically the same SELECT and the same FROM but with different complex WHERE clauses.

Report examples:

R1: list of all cars of brand A that did inspection on period B to C, with piece D installed, used in country E.
R2: list of all cars manufactured this year
...
R20: list of ...

On top of this, I want to have a filtering component that will help look for a specific case. We can assume that the filtering component is the same for all these reports, since the fields/columns will be (almost) the same for all reports.

The most straightforward solution is to create 20 views with all the statements, create 20 entities to map these DB views, and create repositories for all of them. But I guess this can be achieved in a smarter way.

My initial idea was to create a base entity CarReportBaseEntity that holds around 50 fields/columns.

@Entity
@Table(name = "CAR_REPORT_VIEW")
public abstract class CarReportBaseEntity<T extends CarReportBaseEntity<T>> implements Serializable, FilteredEntity<T> {

    @Id
    @Column(insertable = false, updatable = false)
    private Long id;

    @Column(insertable = false, updatable = false)
    private Long manufacturedYear;

    etc...
}

Then for each different report, I would just create an entity like:

@Entity
@Where(clause = "manufacturedYear = 2020")
class ManufacturedIn2020ReportEntity extends CarReportBaseEntity<ManufacturedIn2020ReportEntity> {
}

and similarly for other reports:

@Entity
@Where(clause = "some complex where clause")
class SomeOtherReportEntity extends CarReportBaseEntity<SomeOtherReportEntity> {
}

This would allow me to quickly create new reports by just adding one class and by tweaking the @Where annotation, including all the functionalities shared by the FilteredEntity. However this doesnt work since it creates a @Inheritance(strategy = InheritanceType.SINGLE_TABLE) by default, which then breaks the whole application since I don't have any DTYPE column specified. I don't want to need to specify anything, I just want to create classes for the same Entity but with different complex where clauses.

What could I do to solve this in an elegant way?

CodePudding user response：

You need only:

the entity class that you already have CarReportBaseEntity
a factory that creates 20 CriteriaQueries containing the jpa criateria for each of your reports
a bunch of (service) methods that get a CriteriaQuery parameter and add further filtering (eg. whereCarBrandStartsWith(CriteriaQuery q, String prefix))

To get a report you get the CriteriaQuery object for that report and just get that result list. If you need to further filter those results you'd pass the criteria object to the service method you want

Edit 1:

we would really benefit from multiple classes since then we could extend the FilterEntity with specific columns/order for each report case

Your criteria queries don't have to always return CarReportBaseEntity. For each report you can define whatever select clause you want (CriteriaQuery::multiselect) and wrap the result in whatever dto you want (CriteriaBuilder::createQuery(resultType)). The advantage I see in this approach is that you can keep all your query logic in one place, in your factory, instead of splitting it between 20 classes and inside one of many annotations.