0

I use spring boot 3.4.2, spring data jpa and oracle

I have this query

@Query("""
            select n from Notice n
            join fetch n.noticeType nt
            join n.deposit d
            where d.id = :id
            """)
Page<Notice> findByDepositId(Long id, Pageable pageable);


@Data
@Entity
public class Notice {
    @Id
    private Long noticeId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "notice_type_id", nullable = false)
    @NotNull
    private NoticeType noticeType;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "deposit_id", nullable = false)
    @NotNull
    private Deposit deposit;
    
    private LocalDate noticeDate;
}

@Data
@Entity
public class NoticeType {

    @Id
    private Long noticeTypeId;
    private String description;

    @OneToMany(fetch = FetchType.LAZY), mappedBy="noticeType")
    private Set<Notice> notices = new HashSet<>();

}

@Data
@Entity
public class Deposit {
    @Id
    private Long id;
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "deposit", cascade = {CascadeType.ALL})
    private List<Notice> notices = new ArrayList<>();
}

When findByDepositId is called without Pageable that work fine...

If a sort is done with noticeDate, I get this error

seem like a bad conversion is done

org.hibernate.query.sqm.produce.function.FunctionArgumentException: Parameter 1 of function 'lower()' has type 'STRING', but argument is of type 'java.time.LocalDate' mapped to 'DATE'

tried with spring boot 3.4.3, same issue

schema sql of notice

CREATE TABLE notice (
    notice_id NUMBER(38,0),
    id NUMBER(38,0),
    notice_type_id NUMBER(38,0),
    notice_date DATE,
    CONSTRAINT notice_pk PRIMARY KEY (notice_id),
    CONSTRAINT SYS_C00144111 CHECK ("notice_id" IS NOT NULL),
    CONSTRAINT SYS_C00144112 CHECK ("id" IS NOT NULL),
    CONSTRAINT SYS_C00144113 CHECK ("notice_type_id" IS NOT NULL),
);

strangely, with that work

Page<Notice> findByDepositDepositId(Long id, Pageable pageable);


select n1_0.notice_id,
       n1_0.id
       n1_0.notice_date,
       n1_0.notice_type_id
from notice n1_0
where n1_0.id = ?
order by n1_0.notice_date

So when @Query is used, there is something who tried to put lower

4
  • Cannot reproduce. Works fine for me. Commented Feb 18 at 8:02
  • I tried to reproduce this with some assumption as to your other entities. I used H2 and JPA repositories to do the queries within unit tests. They succeeded, with and without the order by clause. Perhaps you could share your oracle schema? Commented Feb 18 at 13:13
  • 1
    A suggestion on a probably unrelated matter: it is not advisable to use lombok Data on JPA entities. You would want to replace the annotation with Getter, Setter and if necessary with NoArgsConstructor, AllArgsConstructor. Commented Feb 18 at 13:38
  • what is the value of ignorecase flag that you pass during sort Commented Feb 18 at 16:28

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.