Error org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Question

I have the hql query that I am executing but receive an error that I don't really understand the cause of.

This is my code:

@Override
public List<StaffRequest> getStaffLeaveRequest(String userID, Date startDate, Date endDate) 
{
    Session currentSession = sessionFactory.getCurrentSession();        

    List<StaffRequest> results = 
    currentSession.createQuery("select new com.timesheet_Webservice.CustomEntity.StaffRequest(lr.leave_ID, lr.leave_Employee, concat(s.staff_First_Name, ' ', s.staff_Last_Name), "
            + "(lr.leave_Days*8.5), lr.leave_Comments, '1805', concat(pro.project_Pastel_Prefix, ' - ', pro.project_Description), lr.leave_Start, lr.leave_End, lr.leave_IsApproved, "
            + "(select lt.leaveType_Description from LeaveType lt where lt.leaveType_ID = lr.leave_Type)) "
            + "from Staff s, Project pro, Leave lr "
            + "where lr.leave_Employee = s.staff_Code and pro.project_Code = 1805 and lr.leave_Approved = :userID and lr.leave_IsApproved = 0 and s.staff_IsEmployee <> 0 "
            + "and lr.leave_Start between :startDate and :endDate "
            + "order by concat(s.staff_First_Name, ' ', s.staff_Last_Name)")
            .setParameter("userID",userID).setParameter("startDate", startDate).setParameter("endDate", endDate).getResultList();   

    return results;
}

I get this error on the webpage when trying to execute it:

org.hibernate.exception.SQLGrammarException: could not extract ResultSet

And also this console error:

ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Leave leave2_ where leave2_.Leave_Employee=staff0_.Staff_Code and project1_.Proj' at line 1

It seems to indicate some fault at the where clause, but I don't see anything particularly wrong.

UPDATE: Entity classes

Project

@Entity
@Table(name="project")
public class Project {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="Project_Code")
    public int project_Code;

    @Column(name="Project_Customer")
    public int project_Customer;

    //A lot more attributes...

}

Staff

@Entity
@Table(name="staff")
public class Staff 
{
    @Id
    @Column(name="Staff_Code")
    public String staff_Code;
    ...
}

add Staff s, Project pro, Leave entity classes

Maciej Kowalski
– Maciej Kowalski

2019-02-04 10:41:01 +00:00
Commented Feb 4, 2019 at 10:41 — Maciej Kowalski
– Maciej Kowalski, Commented Feb 4, 2019 at 10:41
I do have entity classes for all 3 of them

Dean Strydom
– Dean Strydom

2019-02-04 11:33:47 +00:00
Commented Feb 4, 2019 at 11:33 — Dean Strydom
– Dean Strydom, Commented Feb 4, 2019 at 11:33
thats great. can we see them

Maciej Kowalski
– Maciej Kowalski

2019-02-04 11:34:40 +00:00
Commented Feb 4, 2019 at 11:34 — Maciej Kowalski
– Maciej Kowalski, Commented Feb 4, 2019 at 11:34
Sorry thought you meant I should create them. Added.

Dean Strydom
– Dean Strydom

2019-02-04 11:42:54 +00:00
Commented Feb 4, 2019 at 11:42 — Dean Strydom
– Dean Strydom, Commented Feb 4, 2019 at 11:42

Maciej Kowalski · Accepted Answer · 2019-02-04 12:08:14Z

1

1) As you using java.util.Date for Leave.leave_Start, you should annotate with proper @Temporal value:

@Temporal(TemporalType.TIMESTAMP)
@Column(name="Leave_Start")
Date leave_Start;

2) When setting your query date parameters, try using:

.setDate("startDate", startDate)

or

.setParameter("startDate", startDate, TemporalType.TIMESTAMP)

answered Feb 4, 2019 at 12:08

Maciej Kowalski

26.7k12 gold badges59 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dean Strydom Over a year ago

Did as you suggested but unfortunately the error persists. With some additional testing I narrowed the cause of the error down the the "from Leave lr" by removing the other where conditions. If i run a SQL port of this query in workbench it works perfectly.

Maciej Kowalski Over a year ago

Should Leave be uppercase?

Dean Strydom Over a year ago

If you mean the table name then no it should be lower case but I also already fixed that and still this stubborn error persists. Might just have to resort to using native query

Dean Strydom Over a year ago

I tried a very simple "from Leave" query and got the same error so something must be up with my entity class

Dean Strydom · Accepted Answer · 2019-02-06 10:52:44Z

0

Ok, after much testing I discovered the reason for the error. For some unknown reason the query seems to have a problem with the name of the referenced table 'leave' and produces the error whenever I try to retrieve data from it. If I however rename the table to something as simple as 'leaves' then the query executes successfully. Anyone might know why this is?

answered Feb 6, 2019 at 10:52

Dean Strydom

3055 silver badges22 bronze badges

1 Comment

Dean Strydom Over a year ago

leave is a reserved word in MySQL wich is why it didnt work.

MayEncoding · Accepted Answer · 2019-11-27 19:59:28Z

0

Apparently JPA follows some rule or convention of variable names and does not accept "_" and for some reason does not accept intermediate capital letters... It worked for me by changing all variables to lowercase

answered Nov 27, 2019 at 19:59

MayEncoding

971 gold badge1 silver badge15 bronze badges

1 Comment

Dev Over a year ago

it is not answer by anyway, should be a comment

Collectives™ on Stack Overflow

Error org.hibernate.exception.SQLGrammarException: could not extract ResultSet

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related