CODEWITHSUNDEEP
javasqlhibernatedatecriteria
Alyona
Alyona

Reputation: 1792

Get object from database based on time period using Criteria?

I want to retrieve object from my database based on house_id and month/year. I'm storing full date in SQLite database. There should be single result for each month. This is my code:

public static Costs getCosts(Date lookUpDate, House house){
        Costs costs = null;
        Session sess = mainApp.getSessionFactory().openSession();
        Transaction tx = null;
         try {
             tx = sess.beginTransaction();

             // create criteria builder
             CriteriaBuilder builder = sess.getCriteriaBuilder();
             // create criteria
             CriteriaQuery<Costs> query = builder.createQuery(Costs.class);
             // specify criteria root
             Root<Costs> root = query.from(Costs.class);
             query.select(root).where(builder.and(builder.equal(root.get("house"), house),
             builder.equal(root.get("date"), lookUpDate))); //where month/year equal to lookUpDate
             costs = sess.createQuery(query).getSingleResult();

             tx.commit();
         }
         catch (Exception e) {

How should I adjust it, so that Criteria will be looking for specific month and year?

I'm really looking for solution how to change my Criteria so that it will look for specific month and year rather than selecting all possible costs list and then going through each object comparing dates.

UPDATE: Working code based on François LEPORCQ answer:

public static Costs getCosts(Date lookUpDate, House house) {
        Costs costs = null;

        Calendar myCalendar = Calendar.getInstance();
        myCalendar.setTime(lookUpDate);
        myCalendar.set(Calendar.DAY_OF_MONTH, 1);
        Date monthStart = new java.sql.Date(myCalendar.getTimeInMillis());
        myCalendar.add(Calendar.DAY_OF_MONTH,
                (myCalendar.getMaximum(Calendar.DAY_OF_MONTH) - myCalendar.get(Calendar.DAY_OF_MONTH)));
        Date monthEnd = new java.sql.Date(myCalendar.getTimeInMillis());

        System.out.println("Start: " + monthStart);
        System.out.println("End: " + monthEnd);
        Session sess = mainApp.getSessionFactory().openSession();
        Transaction tx = null;
        try {
            tx = sess.beginTransaction();

            // create criteria builder
            CriteriaBuilder builder = sess.getCriteriaBuilder();
            // create criteria
            CriteriaQuery<Costs> query = builder.createQuery(Costs.class);
            // specify criteria root
            Root<Costs> root = query.from(Costs.class);
            query.select(root)
                    .where(builder.and(builder.equal(root.get("house"), house),
                            builder.greaterThanOrEqualTo(root.get("date"), monthStart),
                            builder.lessThanOrEqualTo(root.get("date"), monthEnd)));
            List<Costs> costsList = sess.createQuery(query).getResultList();
            if(!costsList.isEmpty()){
                costs = costsList.get(0);
            }

            tx.commit();
        } catch (Exception e) {
            if (tx != null)
                tx.rollback();
            System.out.println(e);
            throw e;
        } finally {
            sess.close();
        }

        return costs;

As a lookUpDate parameter I was passing modification date(LocalDate.now()) and was checking if there are any records already existing this month.

Upvotes: 0

Views: 344

Answers (1)

Fran&#231;ois LEPORCQ
Fran&#231;ois LEPORCQ

Reputation: 552

In pseudo code

lookupStartDate = 01/month/year
lookUpEndDate = lookupStartDate + 1 month

where date >= lookupStartDate and date < lookUpEndDate

Use java.util.Calendar to add one month to your start date

try something like this

...

//I assume that the day of your lookupStartDate is the first day of month
Calendar myCal = Calendar.getInstance();
myCal.setTime(lookUpStartDate);    
myCal.add(Calendar.MONTH, +1);    
Date lookUpEndDate = myCal.getTime();

...

query.select(root).where(
    builder.and(
        builder.equal(root.get("house"), house),
        builder.greaterThanOrEqualTo(root.get("date"), lookUpStartDate),
        builder.lowerThan(root.get("date"), lookUpEndDate)
    )
);

...

Upvotes: 1

Related Questions