how to write the sql queries to hibernate

Question

id   name     date
1.   ask    2018-04-25 12:30:59
2.   msk    2018-04-25 12:40:43
3.   sdf    2017-05-25 12:42:34

id=int---->in java id-->int
name=varchar(25)----> in java name-->string
date=datetime------->in java date--->Timestamp

my sql query=select * from table where year(date)='2018';

o/p:1.   ask    2018-04-25 12:30:59
    2.   msk    2018-04-25 12:40:43
select * from table where month(date)='05'
o/p:3.   sdf    2017-05-25 12:42:34


please help me i dont know 
how to write this query in hibernate

How to write the above queries in hibernate? I have tried lot things but I didn't get any solution. If I use to_char() in hibernate it will give a unexpected token error.

WHY do You write sql queries to hibernate on the beginning?

Jacek Cz
– Jacek Cz

2018-04-27 14:38:33 +00:00
Commented Apr 27, 2018 at 14:38 — Jacek Cz
– Jacek Cz, Commented Apr 27, 2018 at 14:38

Michu93 · Accepted Answer · 2018-04-27 10:46:32Z

1

You do not have to use to_char() function. Hibernate supports year and date functions.

Refer this link: https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html and look under "14.10. Expressions".

So your hql would simply look like this:

select t from table t where YEAR(t.date)='2018';

and select t from table t where MONTH(t.date)='05';

edited Apr 27, 2018 at 10:46

Michu93

5,8618 gold badges61 silver badges99 bronze badges

answered Apr 27, 2018 at 9:45

Ankur

9126 silver badges11 bronze badges

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

Comments

KUNDAN KUMAR · Accepted Answer · 2018-04-27 10:34:47Z

0

there is no need to use to_char() method here. you can simply use this query inside your Dao Implementation class.

for years:

session.createSQLQuery("select * from table where year(date) = :year")
       .setParameter("year",2018)
       .list();

for Months

session.createSQLQuery("select * from table where month(date) = :month")
       .setParameter("month",05)
       .list();

answered Apr 27, 2018 at 10:34

KUNDAN KUMAR

295 bronze badges

Collectives™ on Stack Overflow

how to write the sql queries to hibernate

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related