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, Aug 25 '15 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, Aug 26 '15 at 04:23

score 4 · Accepted Answer · edited May 23 '17 at 10:30

4

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 '17 at 10:30

Community

1
1

answered Jul 14 '16 at 02:03

Lin

129
1
8

I think you can accept your own answer after 24 hours or you can accept arch's if it really helped you. – NobleUplift Jul 14 '16 at 16:53

score 2 · Answer 2 · answered Aug 27 '15 at 09:07

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 '15 at 09:07

arch

1,363
2
14
30

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 : http://stackoverflow.com/questions/2345419/is-it-possible-to-get-the-sql-alias-of-a-join-table-for-a-hibernate-sqlrestricti 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 !! – Lin Aug 28 '15 at 07:11
You should put your comment into an answer @Lin so I can upvote that as well! – NobleUplift Jul 12 '16 at 23:15

How to use MySQL functions in Hibernate Criteria Query

2 Answers2