CODEWITHSUNDEEP
javamysqlsqlhibernatejpa
Rajath
Rajath

Reputation: 2188

Hibernate query to select rows within a time range of a Datetime column

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.

@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?

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.

Upvotes: 3

Views: 5808

Answers (3)

Yograj Sudewad
Yograj Sudewad

Reputation: 343

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

Upvotes: 1

Ramzan Zafar
Ramzan Zafar

Reputation: 1600

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();
    }

Upvotes: 1

Darshan Lila
Darshan Lila

Reputation: 5868

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.

Upvotes: 3

Related Questions