JCH
Java Community | Help. Code. Learn.•10mo ago
Steadhaven

Indexing in JPA to ensure a good performance when searching for yearly data?

Hi, I am trying to figure out if my indexing makes sense: 
package com.example.demo.model;

import jakarta.persistence.*;
import jakarta.validation.constraints.DecimalMin;
import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.NotNull;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity 
@Table(name = "payment", indexes = {
        @Index(name = "idx_payment_company_payment_service_date", columnList = "company_id, payment_service_id, YEAR(date)") 
})
public class Payment {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) 
    @Column(name = "payment_id", updatable = false, nullable = false)
    private Long paymentId;

    @DecimalMin(value = "0.01", message = "Amount must be greater than 0")
    @Column(name = "amount", nullable = false, precision = 10, scale = 2)
    private BigDecimal amount;

    @NotNull(message = "Date of payment must be specified")
    @Column(name = "date", nullable = false)
    private LocalDateTime date;

    @NotBlank(message = "Payment method must be specified")
    @Column(name = "payment_method", nullable = false)
    private String paymentMethod;

    @NotNull
    @ManyToOne(fetch = FetchType.LAZY) 
    @JoinColumn(name = "company_id", nullable = false)
    private Company company;

    @NotNull
    @ManyToOne(fetch = FetchType.LAZY) 
    @JoinColumn(name = "payer_id", nullable = false)
    private Payer payer;

    @NotNull
    @ManyToOne(fetch = FetchType.LAZY) 
    @JoinColumn(name = "payment_service_id", nullable = false)
    private PaymentService paymentService;

    // Getters and Setters...
}
package com.example.demo.model;

import jakarta.persistence.*;
import jakarta.validation.constraints.DecimalMin;
import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.NotNull;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity 
@Table(name = "payment", indexes = {
        @Index(name = "idx_payment_company_payment_service_date", columnList = "company_id, payment_service_id, YEAR(date)") 
})
public class Payment {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) 
    @Column(name = "payment_id", updatable = false, nullable = false)
    private Long paymentId;

    @DecimalMin(value = "0.01", message = "Amount must be greater than 0")
    @Column(name = "amount", nullable = false, precision = 10, scale = 2)
    private BigDecimal amount;

    @NotNull(message = "Date of payment must be specified")
    @Column(name = "date", nullable = false)
    private LocalDateTime date;

    @NotBlank(message = "Payment method must be specified")
    @Column(name = "payment_method", nullable = false)
    private String paymentMethod;

    @NotNull
    @ManyToOne(fetch = FetchType.LAZY) 
    @JoinColumn(name = "company_id", nullable = false)
    private Company company;

    @NotNull
    @ManyToOne(fetch = FetchType.LAZY) 
    @JoinColumn(name = "payer_id", nullable = false)
    private Payer payer;

    @NotNull
    @ManyToOne(fetch = FetchType.LAZY) 
    @JoinColumn(name = "payment_service_id", nullable = false)
    private PaymentService paymentService;

    // Getters and Setters...
}
I need to be able to quickly lookup yearly payments that a given company have received for a given year.
1 Reply
JavaBot
JavaBot•10mo ago
⌛ This post has been reserved for your question.
Hey @Steadhaven! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically closed after 300 minutes of inactivity.
TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here. 💤 Post marked as dormant
This post has been inactive for over 300 minutes, thus, it has been archived. If your question was not answered yet, feel free to re-open this post or create a new one. In case your post is not getting any attention, you can try to use /help ping. Warning: abusing this will result in moderative actions taken against you.

Did you find this page helpful?