Explore property to SQL column name mapping for using Sort.by(…) with native queries #3980
Description
I'm encountering an error Unknown column 'createdAt' in 'order clause' error when passing "createdAt" property with Spring Data's PageRequest.
The createdAt field is inherited from a @MappedSuperclass. I'm correctly using the entity property name, not the column name. The strange part is that the error often goes away after a few application restarts, only to reappear later.
Why does Spring Data JPA/Hibernate intermittently fail to resolve the correct column name for an inherited property during sorting, and what is the correct way to prevent this from happening?
Problematic Code:
Pageable pageable = PageRequest.of(request.getPageNumber(), request.getPageSize(), Sort.by("createdAt").ascending());
Page<Submission> page = submissionRepo.findAll(pageable);The Error:
[http-nio-8080-exec-2] DEBUG org.hibernate.SQL - SELECT * FROM submissions WHERE user_id = ? order by createdAt asc limit ?
[http-nio-8080-exec-2] WARN o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 1054, SQLState: 42S22
[http-nio-8080-exec-2] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Unknown column 'createdAt' in 'order clause'Entity class and its Parent
@EqualsAndHashCode(callSuper = true)
@Entity
@Table(name = "submissions")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@SQLRestriction("is_deleted = false")
public class Submission extends BaseAuditableEntity<UUID> {
@Column(name = "full_name", nullable = false)
private String fullName;
@Column(name = "order_id")
private String orderId;
@Column(name = "phone_number", nullable = false)
private String phoneNumber;
@Column(name = "address", nullable = false, columnDefinition = "TEXT")
private String address;
@Column(name = "registration_email", nullable = false)
private String registrationEmail;
@Column(name = "id_card_back_path", nullable = false)
private String idCardBackPath;
@Column(name = "id_card_front_path", nullable = false)
private String idCardFrontPath;
@Column(name = "portrait_photo_path", nullable = false)
private String portraitPhotoPath;
@Column(name = "status", nullable = false, length = 20)
@Enumerated(EnumType.STRING)
private SubmissionStatus status;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "user_id")
private AppUser user;
}
@MappedSuperclass
@Data
@EqualsAndHashCode(callSuper = true)
@EntityListeners(AuditingEntityListener.class)
public class BaseAuditableEntity<K> extends BaseEntity<K> implements Auditable, SoftDeletable {
@CreatedDate
@Column(name = "created_at", updatable = false, nullable = false)
private LocalDateTime createdAt;
@LastModifiedDate
@Column(name = "updated_at")
private LocalDateTime updatedAt;
@Column(name = "is_deleted")
private boolean isDeleted;
@Column(name = "deleted_at")
private LocalDateTime deletedAt;
}