使用Criteria根据时间段从数据库获取对象?
问题描述:
我想从我的数据库检索对象,基于house_id和月/年。我在SQLite数据库中存储完整日期。每个月应该有单一的结果。这是我的代码:使用Criteria根据时间段从数据库获取对象?
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
,以便它会查找特定的月份和年份,而不是选择所有可能的成本列表,然后通过每个对象比较日期。
更新:根据弗朗索瓦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()
),如果有已经存在这个月的任何记录被检查。
答
在伪代码
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)
)
);
...
从SQL的角度来看,你要值日期字段大于或等于该年 - 月的第一天并且小于下一年的第一个月的第一天。 –
我明白了,但我不知道如何将其实施到标准中 – Alyona