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);
}
