How do I use a custom select statement in Hibernate using the HibernateDaoSupport class

Question

I am trying to write a custom select statement in Hibernate using the getHibernateTemplate() method. I am having problems with the resulting mapping.

Example Code:

List<User> users = getHibernateTemplate().find("Select user, sysdate as latestPost from User as user");
for (User user : users) {
assertNotNull(users.name);
}

The goal of the above line is to eventually have a join where I get the max(date) of posts made by the user. The problem I am having is that the resulting users list is not a list of User objects and I get a class cast exception.

Hopefully that is enough code. It is a greatly simplified version of my problem and a combination of snippets from various parts of my application.

Answer 1

Sounds like Hibernate isn't want you want here. Maybe you should start with SimpleJDBCTemplate and a RowMapper and build up from there.

Answer 2

Figured this out. I am already using a HibernateDaoSupport derived DAO class so this solution goes along with that.

String queryString = "Select {user.*}, (select max(submitted) from posts where post.user_id = user.id) MAX_POST from users user";
SQLQuery query = getSession().createSQLQuery(queryString);
query.addEntity("user", User.class);
query.addScalar("MAX_POST", Hibernate.DATE);

List results = query.list();
List<User> users = new ArrayList();
for (Object item : results) {
    Object[] element = (Object[]) item; 
    User user = (User)element[0];
    user.setMaxPost((Date)element[1]);
    users.add(user);
}

The above example is greatly simplified but should show a method you can use to solve this problem. I actually was able to include one of my EAGER fetches by incorporating into the SQL the neccessary tables and then use the addJoin method

Answer 3

Thanks That solved my problem