CODEWITHSUNDEEP
javahibernatehibernate-criteria
Rahul Agrawal
Rahul Agrawal

Reputation: 8971

Hibernate Criteria using restrictions

I have an entity as

public class CommissionSummary {    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    @Column(length = 100)
    private String advisorCode;
    @Column(length = 100)
    private String advisorName;
    private String advisorCodeParent;
    @Column(length = 100)
    private String advisorNameParent;
    @Column(length = 100)
    private String advisorPost;
    @Column
    private Double percentage;
    @Column
    private Double diffPercentage;
    @Column
    private Double saleAmount;
    @Column
    private Long saleCount;
    @Column
    **private Double commissionAmount;
    @Column
    private Integer month;
    @Column
    private Integer year;**
    //Getter Setter
}

On screen user is entering criteria to fetch the date between 2 dates.

Ex. From 01/Jan/2012 till 30/Jul/2012.

In CommissionSummary entity does not have date column, but it has month and year 2 separate columns.

I want to fetch CommissionSummary records for the period from date and to date given by user, based on month and year column.

So how to achieve this using Hibernate Criteria / Restrictions?

Note: Day field does not have any sense in user inputted from and to date.

Upvotes: 0

Views: 11857

Answers (4)

Tom Anderson
Tom Anderson

Reputation: 47243

You can break the criterion down into the disjunction of three smaller criteria:

  1. The commission's year is equal to the query's start year, and its month is greater than or equal to the query's start month
  2. The commission's year is greater than the query's start year and less than its end year
  3. The commission's year is equal to the query's nend year, and its month is less than or equal to the query's end month

You can write each of those as a conjunction of two simple comparisons. This looks like (not tested!):

int fromYear, fromMonth, toYear, toMonth;
Property year = Property.forName("year");
Property month = Property.forName("month");
session.createCriteria(CommissionSummary.class).add(Restrictions.disjunction()
    .add(Restrictions.and(year.eq(fromYear), month.ge(fromMonth))
    .add(Restrictions.and(year.gt(fromYear), year.lt(toYear))
    .add(Restrictions.and(year.eq(toYear), month.le(toMonth))
);

Upvotes: 3

user1512999
user1512999

Reputation: 247

You can use HQL to select what you want:

session.beginTransaction();
session.clear();

Query query = session.createSQLQuery(" from CommissionSummary CS  where  to_date(CS.year || '-' || CS.month || '-01', 'YYYY-MM-DD') between :startDate and :endDate)"

List result = query.list();

Upvotes: -1

Rahul Agrawal
Rahul Agrawal

Reputation: 8971

Thanks for all your answers @JB Nizet has told the correct approach, but which is from native SQL. I have tried below with success....

public List<CommissionSummary> getCommissionSummary(AdvisorReportForm advisorReportForm) {
        Criteria criteria = getSession().createCriteria(CommissionSummary.class);
        if (advisorReportForm.getAdvisorId() != null && advisorReportForm.getAdvisorId() > 0) {
            criteria.add(Restrictions.eq("advisorCode", advisorReportForm.getAdvisorId().toString()));
        }

        if (advisorReportForm.getFromDate() != null && advisorReportForm.getToDate() != null) {
            Calendar calFrom = Calendar.getInstance();
            calFrom.setTime(advisorReportForm.getFromDate());

            Calendar calTo = Calendar.getInstance();
            calTo.setTime(advisorReportForm.getToDate());

            Criterion crit1 = Restrictions.eq("month", calFrom.get(Calendar.MONTH) + 1);
            Criterion crit2 = Restrictions.eq("year", calFrom.get(Calendar.YEAR));
            Criterion critMonthYear1 = Restrictions.and(crit1, crit2);
            calFrom.add(Calendar.MONTH, 1); // increment loop by month

            Criterion critAll = critMonthYear1;
            while (calFrom.getTimeInMillis() < calTo.getTimeInMillis()) {
                Criterion crit1Loop = Restrictions.eq("month", calFrom.get(Calendar.MONTH) + 1);
                Criterion crit2Loop = Restrictions.eq("year", calFrom.get(Calendar.YEAR));
                Criterion critMonthYearLoop = Restrictions.and(crit1Loop, crit2Loop);
                critAll = Restrictions.or(critAll, critMonthYearLoop);
                calFrom.add(Calendar.MONTH, 1); // increment loop by month
            }
            criteria.add(critAll);

        }

        return criteria.list();
    }

As from date to to date difference is validated max upto 6 months. I don't see much performance issue. So used this.

For your reference here is generated SQL

SELECT * FROM CommissionSummary this_ 
WHERE this_.advisorCode=1 
AND (((((this_.month=11 AND this_.year=2011) OR (this_.month=12 AND this_.year=2011)) OR (this_.month=1 AND this_.year=2012)) 
OR (this_.month=2 AND this_.year=2012)) OR (this_.month=3 AND this_.year=2012))

Upvotes: -1

JB Nizet
JB Nizet

Reputation: 692121

There's no restriction available to do that. You'll have to create your own Criterion subclass which generates the appropriate SQL, or use Restrictions.sqlRestriction().

In SQL, on Oracle, the SQL restriction could look like the following:

to_date(c.year || '-' || c.month || '-01', 'YYYY-MM-DD') between :startDate and :endDate)

Upvotes: 0

Related Questions