File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Dynamic sql with hibernate

 
Narendra Nathmal
Greenhorn
Posts: 5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is something like possible with hibernate

<property name="colname" column="?" />

In other words i want to selectively pick columns i want and which are not necessarity defined in the .hbm mapping file. The .hbm has only the key columns.

.hbm has
<class name="classname" table="tablename">
<id ....
<property name="colnm" column="?" />
</class>

The table in question is big with lots of columns. A user sees only certain columns which are defined in a template or wants to looks for only certain column's detail view.

How best to achieve something like this. I know with jdbc it is easy. How about hibernate??

Thanks
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No - it would be silly to alter the mapping of the underlying entity just to return different columns for a particular query. Hibernate is there to map entity fields to Object properties. If you want to restrict a view of an object you need to do it in code.
 
Narendra Nathmal
Greenhorn
Posts: 5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The issue really is a legacy table which has lots of columns. The table supports multiple views with common key (no normalisation here!). By view i mean not typical database views but a set of columns form a view for a key. Yes i can query all the data then pass on only column which the user is requesting. I was hoping to use the power of dynamic sql. The object need not be persisted since it is for query purposes only. I was hoping to build the query at runtime and get results in a java object. Are you suggesting not to use hibernate for something like this?

Thanks
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I was hoping to use the power of dynamic sql

Hmm. By "power" what do you mean? What specifically is it about changing column names in a query that you think is useful in this case?
 
Narendra Nathmal
Greenhorn
Posts: 5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
From what i understand (correct me if i am wrong, i am newbie to hibernate) if i want to query a column from a table i need to define a persistant storage which defines a property for this column. If a table has fifty columns which i may query i need to define all of these in the .hbm and corresponding java class.

In other words i cannot have something like this ...
<class name=name table=table>
<key ...
<property name=alias />

And my query is like this ....
select variable-col into alias from table where ....

Can i map a variable-col to a propety defined in the .hbm but for which i do not know the column name since it depends on the users request.

If this is not clear, never mind. I will wait for others to respond.

Thanks
 
pascal betz
Ranch Hand
Posts: 547
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use Projection to creaete "arbitrary" Objects from a Query.

But to insert data you still need all the properties in a single class. Or a class hierarchy:
BasicView -> SomewhatExtendedView -> ExtendedView
You can map this hierarchy to the same table but will need a discriminator column.

It might be cumbersome to have one (or several) "persistence" classes to store the data and several "view" classes to display it. But it could solve your problem.


You can not map unrelated (not in the same hierarchy) to one table. Well perhaps you can; as long as you only use the ID to retrieve a row. but it is surely not intended and you will run into deep troubles :-)

perhaps hibernate 3 offers help but i think your legacy DB design is the problem and im not sure if any ORM can do what you need.

pascal
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I understand what you are trying to do Narendra Nathmal, just not why you want to do it in SQL. In your example

select variable-col into alias from table where...

what wrong with swapping "table" for Object (I know the syntax is different for HQL but thats the idea anyway). That's why I asked if there was some benefit you hoped to get from "dynamic SQL".


If a table has fifty columns which i may query i need to define all of these in the .hbm and corresponding java class.

Not necessarily, but what's the problem if you do? Currently I'm working with a legacy DB design which has some tables with hundreds of fields per entity. We have performance issues in the systems as a result of this - but they don't come from the Hibernate layer. Remember that for most DBs there is no performance gain from selecting just a few columns from a table and all of them.

What you are asking is basically to change the architecture of your ORM layer dynamically to respond to UI, rather than using the routes avaliable to you in this architecture to dynamically represent the model.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic