Implementing complex MAX function in Spring Boot/JPA query language

Question

Spring Boot here using JPA/Hibernate and CrudRepository impls for managing persistence to my DB tables.

I have the following MySQL table:

CREATE TABLE IF NOT EXISTS price_scarcity_configs (
    price_scarcity_config_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    price_scarcity_config_ref_id VARCHAR(36) NOT NULL,
    price_scarcity_config_version BIGINT NOT NULL,
    price_scarcity_config_updated_on DATETIME NOT NULL,
    price_scarcity_config_fizz INTEGER NOT NULL,

    CONSTRAINT pk_price_scarcity_configs PRIMARY KEY (price_scarcity_config_id),

    CONSTRAINT uc_price_scarcity_configs_ref_id_and_version UNIQUE (price_scarcity_config_ref_id, price_scarcity_config_version)
);

These records will be versioned and different versions of the "same" record will all share the same price_scarcity_config_ref_id. Hence 2+ records can have the same price_scarcity_config_ref_id but will have two distinct different versions.

I'm also using the following JPA/Hibernate entity to model it:

// Uses Lombok annotations to generate getters/setters, etc.

@MappedSuperclass
@Data
@EqualsAndHashCode(callSuper=false)
public abstract class BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String refId;

}

@Entity
@Table(name = "price_scarcity_configs")
@AttributeOverrides({
        @AttributeOverride(name = "id", column = @Column(name = "price_scarcity_config_id")),
        @AttributeOverride(name = "refId", column = @Column(name = "price_scarcity_config_ref_id"))
})
@Data
@EqualsAndHashCode(callSuper=false)
public class PriceScarcityConfiguration extends BaseEntity {

    @Column(name = "price_scarcity_config_version")
    private Long version;

    @Column(name = "price_scarcity_config_updated_on")
    private Date updatedOn;

    @Column(name = "price_scarcity_config_fizz")
    private Integer fizz;

}

I am now trying to write the PriceScarcityConfigurationRepository and need a fairly sophisticated query. Given a refId, I need to find the record who matches that ref id and has the highest/max version number. The raw SQL query to perform this is:

select
  *
from
  price_scarcity_configs pcs
inner join
  (
    SELECT
      price_scarcity_config_ref_id,
      MAX(price_scarcity_config_version) as max_ver
    FROM
      price_scarcity_configs
    group by
      price_scarcity_config_ref_id
  ) t
on
  t.price_scarcity_config_ref_id = pcs.price_scarcity_config_ref_id 
  and
  t.max_ver = pcs.price_scarcity_config_version;

Given my repository and using JPA/Hibernate's built-in query language/annos, how do I implement this query?

public interface PriceScarcityConfigurationRepository extends CrudRepository<PriceScarcityConfiguration,Long> {

    @Query("FROM PriceScarcityConfiguration WHERE ??? HOW TO IMPLEMENT THE ABOVE QUERY HERE ???")
    PriceSheetConfiguration fetchLatestVersionByRefId(@Param("refId") String refId);

}

Answer 1

You could use the following query instead and use setMaxResults(1)

FROM PriceScarcityConfiguration p WHERE p.refId = :refId ORDER BY p.version DESC

Or simply use the Spring Data notation

List<PriceSheetConfiguration> findFirstByRefIdOrderByVersionDesc(String refId);