What is the correct Hibernate Query syntax for a j

Question

I have been at this for hours without success. For everything I try from other incidents logged here or examples elsewhere, the HQL syntax always seems to be wrong in my method:

 Session session = sessionFactory.openSession();
    try { 


        Query query = session.createQuery("select p from Person p join p.books where p.personId = :personId").setParameter("personId", personId);

        List<Person> persons = query.list();    
        Person person = persons.get(0);
        Hibernate.initialize(person.getBooks());
        return person;
    }        
    finally {
        session.close();            
    }    

And every time Hibernate gives me a message like:

Your page request has caused a SQLGrammarException: could not prepare statement error:

My two Java classes are:

@Entity
@Table(name = "PERSON")
public class Person implements Serializable {

// Attributes.    
@Id
@Column(name="PERSON_ID", unique=true, nullable=false)    
@GeneratedValue(strategy=GenerationType.AUTO)
private Integer personId;

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

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

@Column(name="TELEPHONE", nullable=false, length=10)
private String telephone;

@Column(name="EMAIL", nullable=false, length=50)
private String email;

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY)     
private List<Book> books;

And:

Entity
@Table(name = "BOOK")
public class Book implements Serializable {

// Attributes.
@Id
@Column(name="BOOK_ID", unique=true, nullable=false)
@GeneratedValue(strategy=GenerationType.AUTO)
private Integer bookId;

@Column(name="AUTHOR", nullable=false, length=50)
private String author;

@Column(name="TITLE", nullable=false, length=50)
private String title;

@Column(name="DESCRIPTION", nullable=false, length=500)
private String description;

@Column(name="ONLOAN", nullable=false, length=5)
private String onLoan;

@ManyToOne
@JoinColumn(name="person_Id")    
private Person person;

Can someone please tell me where I am wrong?

Hibernate returns this if is of any use:

select person0_.PERSON_ID as PERSON_I1_1_0_, book2_.BOOK_ID as BOOK_ID1_0_1_, person0_.ADDRESS as ADDRESS2_1_0_, person0_.EMAIL as EMAIL3_1_0_, person0_.NAME as NAME4_1_0_, person0_.TELEPHONE as TELEPHON5_1_0_, book2_.AUTHOR as AUTHOR2_0_1_, book2_.DESCRIPTION as DESCRIPT3_0_1_, book2_.ONLOAN as ONLOAN4_0_1_, book2_.person_Id as person_I6_0_1_, book2_.TITLE as TITLE5_0_1_, books1_.PERSON_PERSON_ID as PERSON_P1_1_0__, books1_.books_BOOK_ID as books_BO2_2_0__ from PERSON person0_ left outer join PERSON_BOOK books1_ on person0_.PERSON_ID=books1_.PERSON_PERSON_ID left outer join BOOK book2_ on books1_.books_BOOK_ID=book2_.BOOK_ID where person0_.PERSON_ID=?

Using a query as follows:

Query query = session.createQuery("select from Person as p left join fetch p.books where p.personId = :personId").setParameter("personId", personId);

The stack trace is:

Your page request has caused a QuerySyntaxException: unexpected token: from near line 1, column 8 [select from library.model.Person as p left join fetch p.books where p.personId = :personId] error:

 org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91)

org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109) org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:304) org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:203) org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158) org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:126) org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:88) org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190) org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301) org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236) org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800) library.dao.PersonDAOImpl.get(PersonDAOImpl.java:57) library.service.PersonService.get(PersonService.java:32) library.service.PersonService$$FastClassBySpringCGLIB$$7dacb914.invoke() org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:711) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644) library.service.PersonService$$EnhancerBySpringCGLIB$$b855c56c.get() library.controller.PersonController.idValid(PersonController.java:112) library.controller.PersonController.getLogin(PersonController.java:80) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:483) org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215) org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:690) org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:945) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:876) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961) org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852) javax.servlet.http.HttpServlet.service(HttpServlet.java:621) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837) javax.servlet.http.HttpServlet.service(HttpServlet.java:722) org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304) org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240) org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:164) org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:498) org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164) org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100) org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:562) org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:394) org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:243) org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:188) org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:302) java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) java.lang.Thread.run(Thread.java:744)

Answer 1

Use JOIN FETCH to eagerly load the linked collection:

FROM Person as p LEFT JOIN FETCH p.books WHERE p.personId = :personId