CODEWITHSUNDEEP
javajpajpql
CorayThan
CorayThan

Reputation: 17825

Can't ORDER BY a column in JPQL

I'm trying to order by a field in a JPQL query declaration, and it seems like it should be really simple, but I keep getting a compiler error.

I'm trying to order by the UserClockDate column, which is a part of UserTime rows. But every time I try to compile I get the error:

SEVERE: Error in named query: fetchIfUserIsClockedInWithUser org.hibernate.QueryException: could not resolve property: UserClockDate of: models.UserTime [SELECT ut FROM models.UserTime ut WHERE USER_ID = :user ORDER BY ut.UserClockDate DESC]

If I just take out the ORDER BY it compiles fine.

Here's the relevant part of the class itself:

@NamedQueries({
        @NamedQuery(name = "fetchAllUserTimes", query = "SELECT ut FROM UserTime ut"),
        @NamedQuery(name = "fetchIfUserIsClockedInWithUser", query = "SELECT ut FROM UserTime ut WHERE USER_ID = :user ORDER BY ut.UserClockDate DESC") 
    })
@Entity
@Table(name = "userTime")
@Component
public class UserTime implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    @Column(name = "UserTimeId")
    private int userTimeId;

    @ManyToOne
    @JoinColumn(name = "USER_ID")
    private User user;

    @Column(name = "UserClockIn")
    @Type(type="org.joda.time.contrib.hibernate.PersistentDateTime")
    private DateTime userClockIn;

    @Column(name = "UserClockOut")
    @Type(type="org.joda.time.contrib.hibernate.PersistentDateTime")
    public DateTime userClockOut;

    @Column(name = "UserClockDate")
    @Type(type="org.joda.time.contrib.hibernate.PersistentDateTime")
    public DateTime userClockDate;

Any help you could give me would be greatly appreciated!

Upvotes: 0

Views: 704

Answers (2)

JB Nizet
JB Nizet

Reputation: 691625

JPQL works on entities, their mapped columns and associations. It doesn't work on tables and columns.

There is no USER_ID field in the UserTime entity. There is no UserClockDate in the UserTime entity.

The query should be

select ut from models.UserTime ut where ut.user = :user order by ut.userClockDate desc

Side note: all the fields os UserTime are part of UserTime. No need to repeat the user prefix everywhere. Why not name the fields clockIn, clockOut, clockDate instead?

Upvotes: 2

DataNucleus
DataNucleus

Reputation: 15577

You mean where you try to order by a field that doesn't exist? UserClockDate ought to be userClockDate

Upvotes: 5

Related Questions