0

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.

len
  • 376
  • 2
  • 19

0 Answers0