get Current Date and limit number of rows returned USING HQL

just_a_kid · **Joined:** Tue Jul 11, 2006 10:41 pm **Posts:** 11

Hi ,

no i was wondering if we can swap database easily by only changing configuration files in Hibernate.

Because for example like gettting the current date from the database using hql is not straight forward

Since i m using SQL Server and using SQLServerDialect then my hql should be " select getDate() from something "

because using "current_date/ sysdate doesnt work for me"
What if i want to change to oracle?
i have to change my application code then..

Secondly , how do i limit number of rows using HQL which is portable across database

i use " select top 1 variable from table" doesnt work for me even though i use sql server?

Thanks

chucky · **Joined:** Mon Jun 12, 2006 5:33 am **Posts:** 63

Hi just_a_kid,
use formula since the current is not associated with a column that you provide (but the system). Try this:
1/ in your mapping file (from Oracle point of view) :

Code:

with corresponding getter/setter
2/ in your query:

Code:

session.createQuery("select p.currentDate from Person2 p")

hope this helps

chucky · **Joined:** Mon Jun 12, 2006 5:33 am **Posts:** 63

Hi just_a_kid,
for the second question:

Code:

Query q = sess.createQuery("from Person");
q.setFirstResult(20);
q.setMaxResults(10);
List pers = q.list();

just_a_kid · **Joined:** Tue Jul 11, 2006 10:41 pm **Posts:** 11

Hi Chucky,

Thanks for your help but this is not really what i wanted
and the select sysdate from dual would not work because i am using SQL Server.

Now i realise that hibernate is purely ORM tool and does not really promote database independence such as write HQL that can be used across database

it still relies on the dialect for example if you are using SQL Server Dialect you can getDate() to get the current date from the system

But tthe problem is if my customer want to swap the database to Oracle someday i still have to change my application code / HQL instead changing only the configuration.

Am i Correct?
Thanks

chucky · **Joined:** Mon Jun 12, 2006 5:33 am **Posts:** 63

Hi just_a_kid,
since you're just accessing a normal property from the application code side, you won't need to modify any code. Just the SQL in the formula (.hbm.xml the configuration file)