Execute a query using Hibernate-Java

Question

I have a query

select userid from userinfo where DATEDIFF(ExpiryDate,curdate())<100;

how can this query be executed using hibernate??

I have tried this

List<String> usernames = (List<String>)(getSession().createQuery("select userid from userinfo where DATEDIFF(ExpiryDate,CURRENT_DATE)<100;").list());
     for (Iterator iterator = usernames.iterator(); iterator.hasNext();){
      String username= (String) iterator.next();
      System.out.println(username);
     }

But this does'nt return any result is there any other way to do this??

change DATEDIFF(ExpiryDate,CURRENT_DATE) to DATEDIFF(ExpiryDate,now()) and see if this works — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 9, 2014 at 10:57
are u able to get the query and then try it directly on mysql..since if the query is getting sent as select userid from userinfo where DATEDIFF(ExpiryDate,now()) < 100 ; it should work. — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 9, 2014 at 11:28
@AbhikChakraborty: in mysql it is working fine, but here it is not returning any result — Rishabh
– Rishabh, Commented Apr 9, 2014 at 12:21

user3489875 · Accepted Answer · 2014-04-09 12:37:34Z

2

If it's in the native SQL dialect of your database, then you have to use this:

List<String> usernames = (List<String>)(getSession().createSQLQuery("yourNativeSqlQuery").list());

answered Apr 9, 2014 at 12:37

user3489875

4574 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rishabh Over a year ago

first of all thanks as this does the job , but something native HQL would have been awesome. thanks once again

Nayan Wadekar · Accepted Answer · 2014-04-09 12:39:39Z

1

DATEDIFF is MySQL specific keyword, not valid in HQL.

SELECT u.userid FROM userinfo u WHERE u.expiryDate BETWEEN CURRENT_DATE() AND :end_date

Try executing above query & set parameter end_date as (current_date+100).

answered Apr 9, 2014 at 12:39

Nayan Wadekar

11.6k4 gold badges52 silver badges74 bronze badges

Collectives™ on Stack Overflow

Execute a query using Hibernate-Java

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related