Hibernate criteria Restrictions:- how to fetch current and last month data

Question

Here is my method to get the specific month and year data from created date.

    @Override
    public List getMonthlyOrdersSummary(LabOrderConditions labOrderConditions) {
        Criteria criteria = getCurrentSession().createCriteria(LabOrder.class);

        criteria.add(Restrictions.gt("orderNo", 0));
        if (StringUtils.isNotBlank(labOrderConditions.getClientCode()))
            criteria.add(Restrictions.eq("clientCode", labOrderConditions.getClientCode()));

        if (labOrderConditions.getAccountCodesList() != null) {
            criteria.add(Restrictions.in("practice.code", labOrderConditions.getAccountCodesList()));
        }


        if (StringUtils.isNotBlank(labOrderConditions.getClientsCodes())) {
            if (labOrderConditions.getCallingPlace().equalsIgnoreCase("allClients")) {
                List<String> items = Arrays.asList(labOrderConditions.getClientsCodes().split("\\s*,\\s*"));
                criteria.add(Restrictions.in("clientCode", items));
            }
        }

        criteria.add(Restrictions.sqlRestriction("YEAR(createdDate)=" + labOrderConditions.getYear()));
        ProjectionList projectionList = Projections.projectionList();

        if (labOrderConditions.getCallingPlace().equalsIgnoreCase("allClients")) {
            projectionList.add(Projections.groupProperty("clientCode"), "clientCode");
        }

        projectionList.add(Projections.sqlGroupProjection("MONTHNAME(createdDate) as orderMonthName", "MONTHNAME(createdDate)",
                new String[] { "orderMonthName" }, new Type[] { StandardBasicTypes.STRING }));
        projectionList.add(Projections.sqlGroupProjection("Month(createdDate) as orderMonthNo", "Month(createdDate)", new String[] { "orderMonthNo" },
                new Type[] { StandardBasicTypes.INTEGER }));

        projectionList.add(Projections.rowCount());
        criteria.setProjection(projectionList);
        criteria.addOrder(org.hibernate.criterion.Order.asc("createdDate"));
        return criteria.list();
    }

So how to write restriction for current and last month.

I just want something like criteria.add(Restrictions.in("MONTHNAME(createdDate)", labOrderConditions.getCompareMonthList()));

Please if anyone have any suggestion share with me.

Thanks Sitansu

score 1 · Accepted Answer · answered Mar 31 '16 at 08:01

1

From your labOrderConditions.getCompareMonthList(), create the corresponding start and end Date objects in Java, then:

criteria.add(Restrictions.between("createdDate", startDate, endDate));

If you have several intervals, you may also add "or" restrictions.

Would that solve your problem, or do you expect another result?

answered Mar 31 '16 at 08:01

Anthony Drogon

1,704
1
17
24

Thanks i want data like current month(March) and last month(February) all data fetch base on month – Sitansu Mar 31 '16 at 08:04
What is your database? For example in PostgreSQL, you could use "GROUP BY date_trunc('month', createdDate)", so the answer in Hibernate might depend on your database too. – Anthony Drogon Mar 31 '16 at 08:13
we are using Mysql database – Sitansu Mar 31 '16 at 08:15
1

In MySQL, you have the equivalent "EXTRACT (YEAR_MONTH FROM createdDate)", which might work better than MONTHNAME or Month. Then, if you want a WHERE clause over that field, you can define an alias for it, but I would suggest to use the "between" restriction over the "createdDate" field, as stated above instead. – Anthony Drogon Mar 31 '16 at 08:28
I forgot to mention that, to filter over aggregate function, you'd require a HAVING statement, which cannot be achieved using the Criteria API. Try to execute the statement you want to achieve in SQL over your database first. Again, having a sqlGroupProjection for the field you want to SELECT is ok, but for the WHERE clause, apply it directly on the createdDate field, not the grouped alias. SELECT EXTRACT(YEAR_MONTH FROM createdDate) FROM table WHERE createdDate BETWEEN ... AND ... GROUP BY EXTRACT(YEAR_MONTH FROM createdDate); is what you want. – Anthony Drogon Mar 31 '16 at 12:34

Hibernate criteria Restrictions:- how to fetch current and last month data

1 Answers1