How to use MySQL functions in Hibernate Criteria Query

Question

I am wrting a JAVA background job that runs everyday near midnight and check all the persons in the database who will have birthday tomorrow and sending an auto greeting email to them.

I know how to write the native query but having difficulty in wrting that in Hibernate Criteria query. So, I need to translate a native MySQL query to equivalent JPA criteria query.

SimpleDateFormat birthdayFormatter = new SimpleDateFormat("MMdd");
String birthDay = birthdayFormatter.format(tomorrow);

Native Query : select * from person WHERE DATE_FORMAT(birthday, '%m%d') = birthDay;

In Hibernate Criteria I have :

Criteria criteria = createCriteria();
criteria.add(Restrictions.eq("DATE_FORMAT(birthday, '%m%d')", birthDay));

That doesn't work . I also tried : Restrictions.sqlRestriction("DATE_FORMAT(birthday, '%m%d') = '" + birthDay + "'");

That also doesn't work.. Appreciate any advices..

Restrictions is nothing to do with JPA Criteria, that is Hibernate Criteria so decide what you are using. Secondly you can invoke ANY SQL function using JPQL "Function" (and the same in JPA Criteria). What happens when you do that? — Neil Stockton
– Neil Stockton, Commented Aug 25, 2015 at 10:31
Sorry for the confusion and thanks for pointing out. Indeed I am using Hibernate Criteria so my question should be "How to use MySQL functions in Hibernate Criteria Query" . Using JPQL , I was able to get the desired result .. I just would like to know how to do it in Hibernate . I realize in JPA Criteria , there is something like criteriaBuilder.function() . — Lin
– Lin, Commented Aug 26, 2015 at 4:23

Community · Accepted Answer · 2017-05-23 10:30:37Z

5

So I finally found out a way from this link : stackoverflow.com/questions/2345419/

I need to use Restrictions.sqlRestriction() like so :

criteria criteria = createCriteria(); criteria.add(Restrictions.sqlRestriction("DATE_FORMAT({alias}.birthday, '%m%d') = ?", birthDay, StringType.INSTANCE));

That works !!

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Jul 14, 2016 at 2:03

Lin

1392 silver badges8 bronze badges

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

1 Comment

NobleUplift Over a year ago

I think you can accept your own answer after 24 hours or you can accept arch's if it really helped you.

arch · Accepted Answer · 2015-08-27 09:07:32Z

2

Check below code, how to write Creteria Query according to your requirement:

        @PersistenceContext
        EntityManager em; 

        CriteriaBuilder cb=em.getCriteriaBuilder();
        //select * from person  
        CriteriaQuery<person> qry =cb.createQuery(person.class);
        Root root = qry.from(person.class);  

        // WHERE DATE_FORMAT(birthday, '%m%d') = birthDay  
        Predicate predicate = cb.like(root.get("DATE_FORMAT"), "%"+birthday+"%");  
qry.where(predicate);

TypedQuery<person> tq = em.createQuery(qry);  
return tq.getResultList();

answered Aug 27, 2015 at 9:07

arch

1,3792 gold badges16 silver badges30 bronze badges

2 Comments

Lin Over a year ago

Thanks arch !! I think your exmaple will work . I am just being headstrong curious to know how will it be in Hibernate Criteria Query without using criteria builder.. I finally found out a way from this link : stackoverflow.com/questions/2345419/… I need to use Restrictions.sqlRestriction() like so : criteria criteria = createCriteria(); criteria..add(Restrictions.sqlRestriction("DATE_FORMAT({alias}.birthday, '%m%d') = ?", birthDay, StringType.INSTANCE)); That works !!

NobleUplift Over a year ago

You should put your comment into an answer @Lin so I can upvote that as well!

Collectives™ on Stack Overflow

How to use MySQL functions in Hibernate Criteria Query

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related