Reading DB table using Hibernate
I've finally enabled hibernate persistence in my application, and it looks like everything is moving smoothly on the writing side. The database is being written to as it should. My final step in incorporating hibernate will be to have a javabean read the database to return an array of objects for a datatable to display.
I've been following this tutorial: http://www.codeguru.com/cpp/misc/misc/interfacingtootherlanguages/article.php/c10079__1/
The save method in that tutorial needed a small fix, so I'm wondering how reliable the method for loading objects is.
This is what I'm using right now:
public Project[] listProjects()throws Exception{
Project[] projectAll;
ArrayList projAlist =new ArrayList();
//Log4J initialisation
org.apache.log4j.BasicConfigurator.configure();
//***RELEVENT HIBERNATE CODE
// Create SessionFactory and Session objects
SessionFactory sessionFactory =new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();
//Query using Hibernate Query Language
String sqlString ="FROM project as p";
Query query = session.createQuery(sqlString);
//Iterate through stored objects
for (Iterator it = query.iterate(); it.hasNext();){
Project tempProjObj = (Project) it.next();
projAlist.add(tempProjObj);
}
session.close();
//***END RELEVENT HIBERNATE CODE
// Create an array of type Projects from projAlist arraylist
projectAll =new Project[projAlist.size()];
// Populate projectAll array with Project objects from projAlist arraylist in reverse order
for (int i = 0; i < projAlist.size(); i++){
projectAll[i] = (Project) projAlist.get( (projAlist.size() - i -1) );
}
return projectAll;
}
I recognise that any number of things may be going wrong. I've tried playing around with the HQL statement a bit to see what kind of results I can get, but have settled on using the syntax provided by the tutorial. Using a test class, I receive this error in calling this method:
Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: project is not mapped. [FROM project as p]
I have a mapping configuration file for the object. Do I need to add anything or even have another file for mapping an object from a relational database or should I be using the same mapping as that for object -> schema? Does anyone have a good resource for valid SQL/HSQL statements? I only need to learn basic SQL statements to make appropriate queries to the database, and plan to learn more SQL from there. I'm confused about the compatibility between SQL and HSQL, and what constitutes a valid SQL statement. For example, MySQL query browser is using this to query the table:
SELECT * FROM hibernate10.project p
The same statment does not seem to be valid in the java class, as it causes even more problems. Can someone explain to me what is going on here?
Also, the Log4J initialization line is there because I was getting errors concerning Log4J. Is there a more appropriate thing to do? That line doesn't seem so elegant, and I'm not currently using Log4J for anything.
Thank you all for your help
[4026 byte] By [
mon.goosea] at [2007-11-27 10:25:53]
# 1
You have:
String sqlString = "FROM project as p";
The word 'sql' in 'sqlString' implies hibernate uses sql. It doesn't , it uses hql.
Here is an example:
String hqlStatement="from Person p where p.userName=?";
In the above, 'Person' refers to the Person.java class, not the PERSON database table, and 'p.userName' refers to Person.java class's getUserName() and setUserName(), not to the database table PERSON's fields USER_NAME.
Note hibernate is case sensitive. The following is incorrect:
String hqlStatement="from person p where p.userName=?"; because hibernate is now looking for person.java whereas it should be Person.java.
There is way too much in hibernate to explain further. I suggest reading a book like 'hibernate in action' and creating a persistent layer independent of the business logic layer like I did. I took me many weeks of refactoring to get it right.
The hibernate persistent layer should be developed and stand by itself independent of the business logic needs. It should map the database tables and thier associations and provide a rich set of objects that any business logic can use not just your current business logic.
I suggest creating a new project that only implements the hibernate peristent layer with some simple tables such as PERSON and PET with a one to many relationship, and HOUSE which has a one to one relationship with PERSON. Create a Person, Pet and House class and create JUNIT tests to fetch/insert/update a person graph ( a graph is an object (Person) with is assocations (Pet, House) attached). Then implement the following:
one to many relationship (Person person, Pet pet)
one to one relationship (Person person, House house)
one to many relationship where both sides of the relationship aer the same type (Person parent, Person childPerson) (this is the most difficult to implement).
Next, learn how to properly configure and use lazy loading and the difference between attached and detached graphs and how to update/insert/delete with them.
# 2
I have already created a simple sample project and plan to follow your suggestion and model certain relationships between one class and another because I will eventually have to do the same thing with my larger project. However, at this point I am stuck at retrieving objects without throwing exceptions. I can not proceed further until I can read back objects. As soon as I have accomplished this with just one table and no relationships I will proceed to attempt implementing multiple tables with one-to-one and one-to-many relationships.
For the time being I have accomplished saving my Project objects with the ID and property columns, and they are being written to the database as they should. I would like to retrieve these values and have the ability to re-create the Project objects from the column values.
I understand that hibernate uses it's own query language, and you're right I should have specified HQL. Thanks for informing me that the project in "FROM project as p" should refer to the Project class. I had assumed it should refer to the table that is associated to the class. This explains the “project is not mapped.” error.
In my case I had a class Project and a table project associated to it. I've changed the case of my table to PROJECT since this seems to be an accepted notation. Therefore, my HQL statement should read "FROM Project p" which refers to the Project class associated to the PROJECT table correct?
I have tried this HQL statement, and it does appear to pull appropriate values from the columns, but I can't get around the exceptions that are thrown.
For example, my original code, as shown above but with the correct HQL statement, produces this:
Exception in thread "main" org.hibernate.LazyInitializationException: could not initialize proxy - the owning Session was closed
at org.hibernate.proxy.AbstractLazyInitializer.initialize(AbstractLazyInitializer.java:56)
at org.hibernate.proxy.AbstractLazyInitializer.getImplementation(AbstractLazyInitializer.java:98)
at org.hibernate.proxy.CGLIBLazyInitializer.intercept(CGLIBLazyInitializer.java:158)
at beans.Project$$EnhancerByCGLIB$$a4dd9bbc.getName(<generated>)
at beans.Tester.main(Tester.java:11)
I wanted to see what is going on on an individual iteration basis, so I used this code:
public void listProjects() throws Exception {
// Create SessionFactory and Session objects
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();
//Query using Hibernate Query Language
String hqlString = "FROM Project as p";
Query query = session.createQuery(hqlString);
//Iterate through stored objects
for (Iterator it = query.iterate(); it.hasNext();) {
Project tempProjObj = (Project) it.next();
System.out.println("Name: " + tempProjObj.getName());
System.out.println("Client: " + tempProjObj.getClient());
System.out.println("Location: " + tempProjObj.getLocation());
}
session.close();
}
My database table "PROJECT" has the columns: id, name, client, location (in that order)
The table I am trying to read has:
ROW1-> id: 1 name: name1 client: client1 location: location1
ROW2-> id: 2 name: name2 client: client2 location: location2
ROW3-> id: 3 name: name3 client: client3 location: location3
Using a test class and executing the listProjects() method listed above yields the following output:
Name: name1
Client: client1
Location: location1
Name: name2
Client: client2
Location: location2
Name: name3
Client: client3
Location: location3
Exception in thread "main" java.lang.NullPointerException
at beans.Tester.main(Tester.java:11)
As you can see, it managed to read all of the rows and get the appropriate values, but still throws a NullPointerException as if it's going for a fourth row. I'd like to find a way to prevent this. Is there a reason why Iterator it.hasNext() is returning true even if the next row doesn't exist?
# 3
I suspect your for loop is not correct and is looping past the end of the result size:for (Iterator it = query.iterate(); it.hasNext();)
How about using a while loop instead?
Also, in your for loop, you are adding "attached" Project objects to your list, then you call session.close() which makes them all detached. If you have lazy loading in your hibernate configurations, you are best off calling Hibernate.initialize() on each Project object before putting it in the list to ensure they are fully populated with data before they become detached.
I worked on a hibernate persistent layer for several weeks (mostly on my own time) and I went through the book 'Hibernate in Action' 3 or more times. I still had to experiment a lot to get it right (a lot of JUnit testing to confirm its working right). I still need to experiment with lazy loading to see how it works. I didn't use it on a project yet.
From my experience, unless your project can really benifit from a lazy loading performance boost (sending attached objects to the business layer), I suggest you use a non ORM such as JDBC with JDO. (I haven't looked into ibatis ORM) I think you will have to experiment with Hibernate a LOT before trusting your knowledge of it enough to implement it on a project. Once you commit a project to using Hibernate, it would be difficult to yank it out and use JDBC (I suspect). And your really cant mix hibernate with JDBC/JDO, therefore once your committed, your committed.
If you return detached objects instead of attached objects to the business logic layer, it would make your life easier, but that kills lazy loading and its performance boost. Therefore kill a lot of the reason for using Hibernate.
