Hibernate Inner Join OneToMany Mapping throws HibernateQueryException

Question

I am new to Hibernate. I have established a OneToMany mapping between User and Expense. I am trying to return expenses for a User for the last week. This is the MySQL query that I am using.

select SUM(amount) from Expense INNER JOIN User ON Expense.user_id = User.id AND User.username ='testUser' WHERE created >= curdate() - INTERVAL DAYOFWEEK(curdate())+1 DAY AND created < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY;

When I try to use this query in hibernate, I get a HibernateQueryException

 String query = "select SUM(amount) from Expense INNER JOIN User ON Expense.user_id = User.id AND user.username ='sarvam' WHERE created >= curdate() - INTERVAL DAYOFWEEK(curdate())+1 DAY AND created < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY";
 List list = session.createQuery(query).list();

The error I get is-

Exception in thread "main" org.hibernate.QueryException: outer or full join must be followed by path expression [select SUM(amount) from com.challenge.pojo.Expense INNER JOIN User ON Expense.user_id = User.id AND user.username ='sarvam' WHERE created >= curdate() - INTERVAL DAYOFWEEK(curdate())+1 DAY AND created < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY]
    at org.hibernate.QueryException.generateQueryException(QueryException.java:120)
    at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)
    at org.hibernate.hql.internal.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:233)
    at org.hibernate.hql.internal.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:193)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:298)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1825)
    at com.challenge.dao.ExpenseDAO.getExpensesForLastWeek(ExpenseDAO.java:52)
    at com.challenge.dao.ExpenseDAO.getExpensesForLastWeek(ExpenseDAO.java:44)
    at com.challenge.dao.Test.main(Test.java:27)
Caused by: org.hibernate.QueryException: outer or full join must be followed by path expression
    at org.hibernate.hql.internal.classic.FromParser.token(FromParser.java:253)
    at org.hibernate.hql.internal.classic.ClauseParser.token(ClauseParser.java:93)
    at org.hibernate.hql.internal.classic.PreprocessingParser.token(PreprocessingParser.java:118)
    at org.hibernate.hql.internal.classic.ParserHelper.parse(ParserHelper.java:43)
    at org.hibernate.hql.internal.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:223)
    ... 10 more

Can anyone please help me fix it.

score 1 · Accepted Answer · answered May 28 '17 at 23:21

createQuery(String queryString) Create a new instance of Query for the given HQL query string.

createSQLQuery(String queryString) Create a new instance of SQLQuery for the given SQL query string.

You're using the first one which expects HQL as an input, for using native SQL you sould use the second one.

String query = "select SUM(amount) from Expense INNER JOIN User ON Expense.user_id = User.id AND user.username ='sarvam' WHERE created >= curdate() - INTERVAL DAYOFWEEK(curdate())+1 DAY AND created < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY";
List list = session.createSQLQuery(query).list();

Session Documentation

qwerty · Answer 2 · 2017-05-28T23:53:42.607

You might want to take a look at: I can't make a inner join between two tables in hibernate hql query

If using HQL you have to their Object-Oriented style which might look something like this:

String query = "select SUM(amount) from Expense exp INNER JOIN User u ON e.user_id = u.id AND u.username = 'sarvam' ...";
List list = session.createQuery(query).list();

(untested)

Alternatively you can keep using standard SQL syntay if you instead use:

List list = session.createSQLQuery(query).list();

Then you might want to read Chapter 16 of the Hibernate Documentation.

Keep in mind that the SQL dialect depends on the underlying Database. Depending on your Application there might be a different database used on a different system which could break your SQL statements.

Hibernate Inner Join OneToMany Mapping throws HibernateQueryException

2 Answers2