@Entity
public class Users {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "full_name", nullable = false, length = 50)
private String fullName;
@Column(name = "current_location", nullable = false)
private String currentLocation;
@Column(name = "gender", nullable = false, length = 6)
private String gender;
@Column(name = "birth_date", nullable = false)
private Timestamp birthDate;
}
我使用以下內容按性別過濾用戶
public class SearchSpecification implements Specification<Users> {
private List<SearchCriteria> list;
public SearchSpecification() {
this.list = new ArrayList<>();
}
public void add(SearchCriteria criteria) {
list.add(criteria);
}
@Override
public Predicate toPredicate(Root<UserActualDatum> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
predicates.add(builder.like(root.<String>get(criteria.getKey()), (String) criteria.getValue()));
return builder.and(predicates.toArray(new Predicate[0]));
}
}
但我想按年齡過濾用戶，我有birthDate列存盤用戶的出生日期。可以使用AGE()函式在 postgres 資料庫中計算年齡，但是當我來到 Spring Specification Query 時，我無法從出生日期計算年齡并通過計算年齡進行過濾。
uj5u.com熱心網友回復：
我添加了年齡列，我們無需任何計算就可以得到年齡
@Entity
public class Users {
...
@Formula("date_part('year',AGE(current_date,birth_date))")
@Column(name = "age")
private Integer age;
}
在 POSTGRES 上創建計算年齡的函式
CREATE OR REPLACE FUNCTION get_age( birthday timestamp )
RETURNS integer
AS $CODE$
BEGIN
RETURN date_part('year',age(birthday));
END
$CODE$
LANGUAGE plpgsql IMMUTABLE;
并且我通過添加從birth_date 計算的年齡列來修改表
CREATE TABLE Users (
....
birth_date timestamp not null,
age text GENERATED ALWAYS AS (get_age(birth_date)) stored
);
現在我可以過濾age,age作為從birth_date
當我們現在進行 Spring Boot 規范查詢時，這項作業
public class SearchSpecification implements Specification<Users> {
private List<SearchCriteria> list;
public SearchSpecification() {
this.list = new ArrayList<>();
}
public void add(SearchCriteria criteria) {
list.add(criteria);
}
@Override
public Predicate toPredicate(Root<UserActualDatum> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
predicates.add(builder.lessThanOrEqualTo(
root.<Integer>get(criteria.getKey()), (Integer) criteria.getValue()));
return builder.and(predicates.toArray(new Predicate[0]));
}
}
資源：如何在 postgresql 中計算派生的年齡屬性？
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/400967.html
上一篇：com.aaa.controller.AbcController中的欄位abc需要一個無法找到的型別為“com.aaa.repository.AbcRepository”的bean