Jpa namedquery with left join fetch

java hibernate jpa annotations named-query
user3296520 picture user3296520 · Jul 21, 2014 · Viewed 13.1k times · Source

this is my namedquery:

@NamedQuery( name = "User.findOneWithLists", query = "SELECT u FROM User u " + "LEFT JOIN FETCH u.aTemplates " + "LEFT JOIN FETCH u.bTemplates " + "LEFT JOIN FETCH u.bp " + "LEFT JOIN FETCH u.aCredentials " + "LEFT JOIN FETCH u.st WHERE (st.deleted = false) " + "LEFT JOIN FETCH u.bCredentials " + "LEFT JOIN FETCH u.cl " + "WHERE u.id= :id")

My problem is that I get an error when the application starting:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: LEFT ....

On the st side there is an annotation 

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

Any idea how can I handle this where clause?

Answer

Roman C picture Roman C · Jul 21, 2014

Check a SQL syntax, you can't use left join after where clause. If you are looking at the SQL generated form that named query you will see that joined tables in the query the where clause comes after joins and should specify equal condition that links those tables by the keys. The primary key of the main table on the left and the foreign key of the joined table on the right. The joined table is specified by the property of your many-to-one association.

@NamedQuery(
    name = "findOneWithLists",
    query = "from Table t left join User u where u.id= :id"
)

Related questions

org.hibernate.MappingException: Could not determine type for: java.util.Set

Although this question asked many times and I have already used all the suggestion but still I am getting this error. The User.java is @Entity @Table(name = "USER") public class User implements UserDetails, Serializable { private static final long serialVersionUID = 2…

Read More
Do I need <class> elements in persistence.xml?

I have very simple persistance.xml file: <?xml version="1.0" encoding="UTF-8"?> <persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence …

Read More
Hibernate Annotations - Which is better, field or property access?

This question is somewhat related to Hibernate Annotation Placement Question. But I want to know which is better? Access via properties or access via fields? What are the advantages and disadvantages of each?

Read More