In my jpa repository, I can query records created in certain year:
@Query("select t from Ticket t where year(t.createDate) = ?1")
List<Ticket> findByYearAndSort(int year, Sort sort);
How to do this in jpa specification instead? Something like this doesn't work:
static Specification<Ticket> createdIn(int year) {
return (root, query, cb) -> cb.equal(root.get("year(createDate)"), year);
}
This throws the exception below:
InvalidDataAccessApiUsageException: Unable to locate Attribute with
the the given name [year(createDate)] on this ManagedType
[onlineTicket.utility.domain.Ticket]; nested exception is
java.lang.IllegalArgumentException: Unable to locate Attribute with
the the given name [year(createDate)] on this ManagedType
[onlineTicket.utility.domain.Ticket]]
My class:
package onlineTicket.utility.domain;
import java.time.*;
import javax.persistence.*;
import javax.validation.constraints.*;
import org.springframework.data.annotation.*;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import org.springframework.format.annotation.DateTimeFormat;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
@Entity
@JsonIgnoreProperties(value = {"createTime", "updateTime"}, allowGetters = true)
@EntityListeners(AuditingEntityListener.class)
@Table(name="ticket", schema="utility")
@Access(AccessType.FIELD)
public class Ticket {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@NotBlank
@Size(min = 6, max = 6)
@Column(name = "number", unique = true)
private String number;
@NotNull
@DateTimeFormat(pattern = "yyyy-MM-dd")
@Column(name = "create_date")
private LocalDate createDate;
@Column(name = "user_group")
@Enumerated(EnumType.STRING)
private UserGroup group;
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm")
@Column(name = "request_time")
private LocalDateTime requestTime;
@CreatedDate
@Column(name = "create_time")
private LocalDateTime createTime;
@LastModifiedDate
@Column(name = "update_time")
private LocalDateTime updateTime;
public Ticket(){}
Ticket(String number, LocalDate createDate) {
this.number = number;
this.createDate = createDate;
}
public final long getId() {
return id;
}
public final String getNumber() {
return number;
}
public final LocalDate getCreateDate() {
return createDate;
}
public final LocalDateTime getRequestTime() {
return requestTime;
}
}
There should be nothing wrong with the createDate (create_date) column, because my repository.findByYearAndSort() works well.
Resolved:
Based on the link suggested, I change my function to:
return (root, query, builder) -> builder.equal(
builder.function("YEAR", Integer.class, root.get("createDate")), year);
Thank you all.