Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate: Problem with subset query

 
Choon-Chern Lim
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a DAO bean that consist of the following: id, var1, var2, var3, var4.

When I run the follow query, it works:-



When I try to subset the query by doing the following...



... it fails with the following exception:-


Why is Hibernate throwing this exception and how do I fix it?

Thanks.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The exception is a SQLException so I can only presume you have a mapping file that includes a property which is mapped to a field on a table that does not exist. Do you?
 
Choon-Chern Lim
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul,

All my properties are mapped properly in the mapping file, but it seems like if I use native SQL, I would need to select all columns rather than subset of columns.

I switched to HQL using createQuery() instead of createSQLQuery(), and now it works fine. The only issue I have with createQuery() is that I have to manually map the queried results into my bean. Is there a way I could have the results mapped directly back into my DAO bean?

Thanks.
 
Chris Richardson
author
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

A few comments:

1. One minor point: I think your bean is a persistent entity not a DAO. DAOs contain database access logic.

2. I believe a Hibernate SQL queries must be return all of the columns corresponding to the properties of the object. Since you SELECT does not return var3 you get this error "Invalid column name var3"

3. If you just want to populate a subset of the entity's properties you could use an HQL query that looks like this "select new Table(t.id, t.var1, t.var2) from Table t where ..". See http://www.hibernate.org/hib_docs/v3/reference/en/html/queryhql.html#queryhql-select

4. Another option is to use lazy property fetching: http://www.hibernate.org/hib_docs/v3/reference/en/html/performance.html#performance-fetching-lazyproperties

However, are you sure that you really want to do this. If Table is mapped to the database but you only need to retrieve a subset of properties then perhaps you should

5. Are you sure that you want to do this? Retrieving only a subset of an object's properties can be confusing unless you use option #4. Also, retrieving a non-persistent copy of your object (as your SQL query does or my HQL query in point #3 does ) can be confusing since updates to it will be ignored.

Sometimes it can be cleaner to create a DTO such as TableSummary that contains just the required properties and is not mapped to the database. It would be retrieved using "select new TableSummary(t.id, t.var1, t.var2) from Table t where .."

Chris
 
Choon-Chern Lim
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Chris,

I was looking at the "select new Table(t.id etc...)"... it doesn't seem feasible for me to do so since I would end up with lots of constructors.

The reason I opt to select a subset of columns because my web screen only requires certain columns for display. I'm reluctant to query out all columns since I don't use them, and I would think it may affect the performance (I may be wrong since this data may have already be cached).
 
Chris Richardson
author
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

Unless you

(a) have a large number of columns
(b) have columns containing large values
(c) are accessing a large number of rows
(d) have identified performance problems

its unlikely that the overhead of returning all of a table's columns is significant.

Chris
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic