Hibernate could not resolve property error

Question

I am currently getting absolutely bally nowhere with a problem with Hibernate where I am given the message:

Your page request has caused a QueryException: could not resolve property: PERSON_ID of: library.model.Person [FROM library.model.Person p JOIN Book b ON p.PERSON_ID = b.PERSON_ID WHERE p.PERSON_ID = 2] error:

In the method below:

@Override
public Person getPersonAndBooks(Integer personId) {
    logger.info(PersonDAOImpl.class.getName() + ".listBooksForPerson() method called.");

    Session session = sessionFactory.openSession();                  
    Query query = session.createQuery("FROM Person p JOIN Book b ON p.PERSON_ID = b.PERSON_ID WHERE p.PERSON_ID = " + personId);                
    List<Person> persons = query.setResultTransformer(Transformers.aliasToBean(Person.class)).list();
    List<Book> books = persons.get(0).getBooks();
        for (Book b : books) {
           System.out.println("Here " + b.toString());
        }    
        return persons.get(0);  
    }        
    finally {
        session.close();
    }         
}

But I see nothing wrong in the SQL and it works perfectly well in Apache Derby.

I've tried a number of things on StackOverflow and elsewhere but nothing resolves the issue.

There are two classes in a simple application:

@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 Book:

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;

Each maps to database tables:

CREATE TABLE PERSON (
  PERSON_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),    
  NAME VARCHAR(50) NOT NULL,  
  ADDRESS VARCHAR(100) NOT NULL, 
  TELEPHONE VARCHAR(10) NOT NULL,
  EMAIL VARCHAR(50) NOT NULL,      
  CONSTRAINT PRIMARY_KEY_PERSON PRIMARY KEY(PERSON_ID)
)

And Book is:

CREATE TABLE BOOK (
  BOOK_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),    
  AUTHOR VARCHAR(50) NOT NULL,  
  TITLE VARCHAR(100) NOT NULL, 
  DESCRIPTION VARCHAR(500) NOT NULL,
  ONLOAN VARCHAR(5) NOT NULL,
  PERSON_ID INTEGER,   
  CONSTRAINT PRIMARY_KEY_BOOK PRIMARY KEY(ID),
  CONSTRAINT FOREIGN_KEY_BOOK FOREIGN KEY(PERSON_ID) REFERENCES PERSON(PERSON_ID)  
)

Can someone please tell me where I am going wrong?

And if when the SQL finally works, if I am using the right method to convert the output into a Person object where a Person has an arraylist of Book?

My method to get a books for a Person is:

// Calls books.jsp for a Person.
@RequestMapping(value = "/books", method = RequestMethod.GET)
public String listBooks(@RequestParam("personId") String personId, 
                        Model model) {    
    logger.info(PersonController.class.getName() + ".listBooks() method called."); 


    Person person = personService.get(Integer.parseInt(personId));  
    List<Book> books = bookService.listBooksForPerson(Integer.parseInt(personId));                

    // Set view.
    model.addAttribute("person", person);
    model.addAttribute("books", books);
    return "view/books";
}

Which does work.

Full stack trace follows:

Your page request has caused a LazyInitializationException: failed to lazily initialize a collection of role: library.model.Person.books, could not initialize proxy - no Session error:

org.hibernate.collection.internal.AbstractPersistentCollection.throwLazyInitializationException(AbstractPersistentCollection.java:575)
org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:214)
org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:554)
org.hibernate.collection.internal.AbstractPersistentCollection.read(AbstractPersistentCollection.java:142)
org.hibernate.collection.internal.PersistentBag.iterator(PersistentBag.java:294)
library.controller.PersonController.getLogin(PersonController.java:104)
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

In HQL no need to use ON condition in joins hibernate will generate this on runtime. And only our POJO variable name should be used in our HQL.

FROM Person p JOIN p.book b WHERE p.persionId=personid

Answer 2

Safer still than HQL is using the criteria API:

@RequestMapping(value = "/books", method = RequestMethod.GET)
public String listBooks(@RequestParam("personId") String personId, 
                        Model model) {    

    Criteria query = session.createCriteria(Person.class);
    query.addRestriction("personId", personId);
    Person me = query.list().get(1);
    List<Book> myBooks = me.getBooks();
    model.setAttribute("person", me);
    model.setAttribute("books", myBooks);
    return "view/books";
}

UPDATE

@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.EAGER)     
private List<Book> books;
}

Answer 3

If i understand correctly after you get Person from controller return (Person) session.get(Person.class, personId); This person instance is not having books as books are loaded by lazily. And when you call person.getBooks() it requires an open session to load the books but in your DAO session got already closed in finally block which internally causes LazyInitializationException: failed to lazily initialize a collection of role: library.model.Person.books, could not initialize proxy - no Session error:

Try to load books EAGERLY.

Change your code

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

Answer 4

You wrote your HQL query as if it was a SQL query. It's not. HQL and JPQL are different languages.

HQL never uses table and column names. It always uses entity names and their persistent field names (i.e. personId and not PERSON_ID) and their associations. Joins in HQL consists in navigating through association between entities. HQL queries thus don't have ON clauses.

A correct HQL query would be

 select p from Person p join p.books where p.id = :personId

Note that I use a named parameter in the query, that must be bound, instead of using concatenation which is the open door to SQL injection attacks (just as in SQL).

The above query would select the person identified by the given ID, unless it doesn't have any book. You don't need any result transformer to get the result of this query: it's a Person instance.

I strongly suggest you read the Hibernate documentation, which explains HQL queries.

That said, you don't need any query to implement a method to get a person by ID. All you need is

Person p = (Person) session.get(Person.class, personId);
// now you can display the person and its books.