wood burning stoves*
The moose likes Object Relational Mapping and the fly likes Dynamic sql with hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Dynamic sql with hibernate" Watch "Dynamic sql with hibernate" New topic
Author

Dynamic sql with hibernate

Narendra Nathmal
Greenhorn

Joined: Aug 04, 2004
Posts: 5
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

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Narendra Nathmal
Greenhorn

Joined: Aug 04, 2004
Posts: 5
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

Joined: Apr 14, 2004
Posts: 10336


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

Joined: Aug 04, 2004
Posts: 5
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

Joined: Jun 19, 2001
Posts: 547
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

Joined: Apr 14, 2004
Posts: 10336

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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Dynamic sql with hibernate