*
The moose likes Object Relational Mapping and the fly likes HQL and Oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "HQL and Oracle" Watch "HQL and Oracle" New topic
Author

HQL and Oracle

Vikas Kapoor
Ranch Hand

Joined: Aug 16, 2007
Posts: 1374
Hello friends,

I am working on web development project.
In which we are using Spring and Hibernate.
and we use Oracle as Database.

My problem is like this.
I am ready with queries which run in oracle without any problem.
I want to convert them in HQL.But i think there many things which do not
support in HQL (I think).
For example:



If i paste this as is to get executed in HQL than this don't work.but if i
remove the line with ts (line no 7) then it works fine.

And same kind of conversion problem i am facing for other queries also.

So my question is how do i map SQL for oracle with HQL for Hibernate?
The way i follow is copy the query from oracle(running condition) and paste it as is in *.hbm.xml file and see whether it works or not.
Is this the only way or some better way exists that i can follow.

Please provide you help.

Thanks

Vishal Pandya
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

Remember HQL uses objects not tables or fields. So copying an SQL query into your code as HQL most likely won't work. And kind of means to me if it does work for you, that your mapping one to one with the tables and your Java objects, which kind of defeats the benefits of OO. But, anyway, besides that, in HQL you won't have everything that SQL has, but you can get close, and in some cases when Hibernate cannot parse a particular piece of HQL, it sends that part as is to the database. so I am not sure what "ts" stand for, but I am usre you can replace it with some value, maybe use a bind variable in the HQL and call setParameter() on it to set its value.

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Vikas Kapoor
Ranch Hand

Joined: Aug 16, 2007
Posts: 1374
Mark,
First sorry for providing inadequate information.

Remember HQL uses objects not tables or fields. So copying an SQL query into your code as HQL most likely won't work. And kind of means to me if it does work for you, that your mapping one to one with the tables and your Java objects, which kind of defeats the benefits of OO.

yes i have modified the actual query to its equivalent HQL (except that ts) and it works but if i add ts then it doesn't.Either it doesn't support it or need to replace it with something else.

But, anyway, besides that, in HQL you won't have everything that SQL has, but you can get close, and in some cases when Hibernate cannot parse a particular piece of HQL, it sends that part as is to the database. so I am not sure what "ts" stand for, but I am usre you can replace it with some value, maybe use a bind variable in the HQL and call setParameter() on it to set its value.

Here, ts isn't variable in my case. After bit googling i found that it is a keyword. ts means temporary segment.but i couldn't find its alternative in HQL. And as you said if Hibernate couldn't parse something then it sends to database then i shouldn't get an exception if it has parsed it successfully. So please give some clear idea on this.

Some grassroots questions.

1)We know that hibernate (HQL) supports more then one databases. Does it mean that it supports all the functions,keywords,etc. of all the databases or it means its support to only those things which are common to all the databases? I mean is hibernate superset? Hope you get me.

2) If we assume that Hibernate supports only common functionalities then any other option?? We have to go for native sql is it?


Thanks,

Vishal Pandya
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

1) Hibernate HQL won't support something that is Database specific, so ts would be something specific. Now you can use a tool like P6spy to intercept the actual sql query sent to the database to see what it does with ts. But I think the issue is that the ts is within a statement portion that probably gets parsed and rewritten, where the ts might end up in the wrong place.

But I am sure if you just used native SQL with a SQLQuery object, then you will be fine with the original SQL query as is, and you can still have it put the values into your Java objects, instead of arrays of Objects.

Mark
 
Don't get me started about those stupid light bulbs.
 
subject: HQL and Oracle