Hibernate: Unknown column 'column_name' in 'order clause'

Question

I've been learning Hibernate for the past two weeks, but I'm now experiencing an issue I can't solve nor am I lucky to find a solution online.

My problem: I would like a message to have multiple responses. I have @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY), @JoinColumn(name = "message_id"), @OrderBy("responseDate DESC") for a list of responses. The responseDate belongs to the response table. I keep seeing the following error whenever I try to look up a message:

[ERROR] [13-March-2023 3:14:28.025 am | GMT-05:00] [main] [org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:142)] - Unknown column 'messagessu0_.response_date' in 'order clause'

Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not extract ResultSet...

I deleted the tables from the database then went ahead and commented out the @OrderBy("responseDate DESC") annotation, add a message then try to look up that message again, but the same error still shows.

What am I doing wrong here?

Message.java

@Entity
@Table(name = "Message")
public class Message implements Serializable {
    @Transient
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    @Column(name = "message_id", nullable = false)
    private int messageID;

    @Column(name = "type", nullable = false, length = 50)
    private String messageType;

    @Column(name = "category", nullable = false, length = 100)
    private String category;

    @Column(name = "details", nullable = false, length = 2000)
    private String details;

    @Column(name = "date_created", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date dateCreated;

    @Column(name = "resolved")
    private boolean resolved;

    @OneToOne(cascade = CascadeType.MERGE)
    @JoinColumn(name = "student_id")
    private Student student;

    @OneToOne(cascade = CascadeType.MERGE)
    @JoinColumn(name = "staff_id")
    private Staff staff;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "message_id")
    @OrderBy("responseDate DESC") // commented out here and still seeing the same error about unknown column
    private List<Response> responses;

    // Constructors
    // Getters & Setters
}

Response.java

@Entity
@Table(name = "Response")
public class Response implements Serializable {
    @Transient
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    @Column(name = "response_id", nullable = false)
    private int responseID;

    @Column(name = "content", nullable = false, length = 2000)
    private String content;

    @Column(name = "response_date", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date responseDate;

    @OneToOne(cascade = CascadeType.MERGE)
    @JoinColumn(name = "message_id")
    private Message message;

    // Constructors
    // Getters & Setters
}

What I used as a reference was another class of mine...

Staff.java

@Entity
@Table(name = "Staff")
@PrimaryKeyJoinColumn(name = "user_id")
public class Staff extends User implements Serializable {
    @Transient
    private static final long serialVersionUID = 1L;

    @Column(name = "role", length = 255)
    private String role;

    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "staff_id")
    @OrderBy("dateCreated DESC") // order by date_created which is from the message table and it worked
    private List<Message> assignedMessages;

    // Constructors 
    // Getters & Setters
}

Before I added the list of responses to the message class, everything was working fine. When I look up a staff member, I get back the list of messages that were sent by that staff member.

Where have I gone wrong? Any help or explanation would be greatly appreciated.

Answer 1

After taking a break and then going back through all the classes relating to the Message class, I saw what I did wrong.

@Entity
@Table(name = "Student")
@PrimaryKeyJoinColumn(name = "user_id")
public class Student extends User implements Serializable {
    @Transient
    private static final long serialVersionUID = 1L;

    @Column(name = "email", length = 255)
    private String email;

    @Column(name = "telephone", length = 255)
    private String telephone;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "student_id")
    @OrderBy("dateCreated DESC") // I had right here -> @OrderBy("dateCreated DESC, responseDate DESC")
    private List<Message> messagesSubmitted;

    // Constructors
    // Setters & Getters
}

So in the Student class, the error occurred apparently because I didn't remove responseDate from the @OrderBy annotation for some reason. I've recently updated the Message class by creating a Response class and linking them.

What I had initially was:

@Entity
@Table(name = "Message")
public class Message implements Serializable {
    ...
    @Column(name = "response", nullable = false, length = 2000)
    private String response;

    @Column(name = "response_date", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date responseDate;
    ...
}

To:

@Entity
@Table(name = "Message")
public class Message implements Serializable {
    ...
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "message_id")
    @OrderBy("responseDate DESC")
    private List<Response> responses;
    ...
}

So with that update, the responseDate went in the unknown, hence the reason for the error I was getting.