Hibernate查询以在Datetime列的时间范围内选择行

I have a table DeviceData with columns Username, Watts, and CreatedOn, where CreatedOn is of type Datetime (yyyy-MM-dd HH:mm:ss). All three columns form a composite key and are an @Embeddable class in Hibernate.

我有一个表DeviceData，列是Username、Watts和CreatedOn，其中CreatedOn的类型是Datetime (yyyyyyy - mm -dd HH:mm:ss)。这三列组成了一个复合键，是Hibernate中的一个@ embed类。

@Embeddable
public class TimeSeriesPowerPK implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    private String username;
    private Integer watts;
    private String dateTime;

    public TimeSeriesPowerPK(String username, Integer watts, String dateTime) {
        this.username = username;
        this.watts = watts;
        this.dateTime = dateTime;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Integer getWatts() {
        return watts;
    }

    public void setWatts(Integer watts) {
        this.watts = watts;
    }

    public String getDateTime() {
        return dateTime;
    }

    public void setDateTime(String dateTime) {
        this.dateTime = dateTime;
    }

}

How should I write my query in the DAO shown below to retrieve all Watts values of rows whose createdOn is between the current time and a user entered number of hours or minutes?

我应该如何在下面显示的DAO中编写查询，以检索在当前时间和用户输入的时间或分钟数之间的createdOn的所有沃茨值?

public List<Integer> getPowerUsageForUser(String minutes) {
    List<Integer> powerUsedList = sessionFactory.getCurrentSession().createQuery("***query***").list();
    return powerUsedList;
}

Any help is greatly appreciated! Thanks in advance!

非常感谢您的帮助!提前谢谢!

EDIT: Updated question to retrieve a particular column's value of all selected rows.

编辑:更新问题，检索特定列的所有选定行的值。

3 个解决方案

#1

Use following code

使用以下代码

public List<TimeSeriesPowerPK> getPowerUsageForUser(String minutes) {
Query query = sessionFactory.getCurrentSession().createQuery("From TimeSeriesPowerPK where dateTime <=:param1 AND dateTime >= :parma2 ");
query.setParameter("param1",currentDateObject);
query.setParameter("param2",userEnteredDateObject);
List<TimeSeriesPowerPK> powerUsedList=query.list();
return powerUsedList;

}

Note that dateTime of the class must be mapped to createdOn column of database table. Hope it helps.

注意，类的dateTime必须映射到数据库表的createdOn列。希望它可以帮助。

#2

You can use Criteria

您可以使用标准

public List<TimeSeriesPowerPK> getPowerUsageForUser() {

        Criteria powerCriteria= session.createCriteria(TimeSeriesPowerPK.class);
        powerCriteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

        powerCriteria.add(Restrictions.le("dateTime",currentDateObject));
        powerCriteria.add(Restrictions.ge("dateTime",userEnteredDateObject));

        return powerCriteria.list();
    }

#3

Try this

试试这个

Query qrFetch=ursession.createQuery("from TimeSeriesPowerPK where dateTime between :start_date and :end_date");
qrFetch.setParameter("start_date",currentDateObject);
qrFetch.setParameter("end_date",userEnteredDateObject);
List<TimeSeriesPowerPK> timeSeriesPowerPKList=qrFetch.list();

#1