java - 使用 Criteria 根据时间段从数据库获取对象？

Question

我想根据house_id和月/年从数据库中检索对象。我将完整日期存储在 SQLite 数据库中。每个月应该有一个结果。这是我的代码:

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) {

我应该如何调整它，以便 Criteria 查找特定的月份和年份？

我真的在寻找如何更改我的Criteria的解决方案，以便它查找特定的月份和年份，而不是选择所有可能的成本列表，然后遍历每个对象来比较日期。

更新:基于 François LEPORCQ 答案的工作代码:

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;

作为 lookUpDate 参数，我传递了修改日期(LocalDate.now())，并检查本月是否已有任何记录。

Answer 1

伪代码

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

where date >= lookupStartDate and date < lookUpEndDate

使用 java.util.Calendar 将开始日期添加一个月

尝试这样的事情

...

//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)
    )
);

...