Spring Data JPA Specification Paging with Two Tables

user_1846656 0
Viewed 811

I have two Tables: Income and Expense in which I'm storing all my incomes and expense transactions and currently there is not relationship between these two tables.

@Entity
@Table(name = "expense")
public class Expense {

    private BigDecimal amount;
    private LocalDate dueDate;
    private LocalDate datePaid;
    private String description;
    ......
}

@Entity
@Table(name = "income")
public class Income {

    private BigDecimal amount;
    private LocalDate dueDate;
    private LocalDate datePaid;
    private String description;
    ......
}

At the moment I use Spring Data JPA's Specification feature to do it on an individual entity:

For Expense: Page<Expense> expensePage = expenseRepository.findAll(expenseSpecification, paginatedList);

For Income: Page<Income> depositPage = incomeRepository.findAll(incomeSpecification, paginatedList);

Now, I want to show all my transactions (both income and expense) on one table. So I'm now exploring performance effective solution to fetch data from both of these tables after applying filters and paginations.

I want to use specifications because the filters (amount, date etc...) are optional.

I can think of the following approaches:

  1. Run queries separately as mentioned above, merge the results and then apply pagination manually - this seems to be highly ineffective from performance perspective as it will be fetching whole table data
  2. use native SQL query with UNION but won't be able to apply filters because filters are dynamic and optional

select * from ( SELECT id, amount, paid_on as "date_paid", 'EXPENSE' as "type" FROM public.expense UNION SELECT id, amount, received_on as "date_paid", 'DEPOSIT' FROM public.deposit ) as annon_1 where annon_1.amount > 2 and annon_1.date_paid = '2021-08-10' order by annon_1.id Limit 5 offset 0

  1. Implement inheritance as both Income and Expense are transactions basically which has some shared attributes like date, amount, description etc... but It will be a big change as there is already lot of data in both of these tables. So I'll have to change database schema, domain classes and migrations to manage the existing data

Or if there is any other performance effective solution?

spring-data spring-data-jpa spring-boot hibernate java
2 Answers

You have another option which is, use a @Subselect entity.

@Entity
@Subselect("select i.id as id, true as income, i.description as description, i.date_paid as date_paid, i.due_date as due_date, i.amount as amount from income i union all select e.id as id, false as income, e.description as description, e.date_paid as date_paid, e.due_date as due_date, e.amount as amount from expense e")
public class Transaction {
    @EmbeddedId
    private TransactionId id;
    @Column(name = "amount")
    private BigDecimal amount;
    @Column(name = "due_date")
    private LocalDate dueDate;
    @Column(name = "date_paid")
    private LocalDate datePaid;
    @Column(name = "description")
    private String description;
}

@Embeddable
public class TransactionId {
    @Column(name = "id", nullable = false)
    private Long id;
    @Column(name = "income", nullable = false)
    private boolean income;
}

You can then query this like you query income and expense already.

On top of that, I can recommend you to use Blaze-Persistence for this purpose that works on top of JPA/Hibernate and adds support for set operations like UNION ALL in case you need to create this UNION more dynamically i.e. alter the query based on some runtime conditions. I also think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Transaction.class)
public interface TransactionDto {
    @IdMapping
    TransactionIdDto getId();
    String getDescription();
    LocalDate getDatePaid();
    LocalDate getDueDate();
    BigDecimal getAmount();

    @EntityView(TransactionId.class)
    interface TransactionIdDto {
        @IdMapping
        Long getId();
        boolean isIncome();
    }
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

TransactionDto a = entityViewManager.find(entityManager, TransactionDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<TransactionDto> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary!

user_4124460

Use native query and inside query use the 'UNION ALL' key to fetch data from a table.

SELECT 
    t3.*
FROM
    ((SELECT 
        t1.amount, t1.duedate, t1.datePaid, t1.description
    FROM
        EXPENSES t1) UNION ALL (SELECT 
        t2.amount, t2.duedate, t3.datePaid, t4.description
    FROM
        Income t2)) t3.

For JPA query.

@Query(value = " SELECT t3.* FROM ((SELECT t1.amount, t1.duedate, t1.datePaid, t1.description FROM EXPENSES t1) UNION ALL (SELECT t2.amount, t2.duedate, t3.datePaid, t4.description FROM Income t2)) t3 ?#{#pageable}", countQuery = "SELECT count(*) FROM ((SELECT t1.amount, t1.duedate, t1.datePaid, t1.description FROM EXPENSES t1) UNION ALL (SELECT t2.amount, t2.duedate, t3.datePaid, t4.description FROM Income t2)) t3 ", nativeQuery = true)

user_50896450
Related