CODEWITHSUNDEEP
javahibernatejpahql
SimonT
SimonT

Reputation: 165

Hibernate: using LocalDateTime to query a range

I'm using hibernate 5 with the hibernate-java8 addon to use LocalDateTime fields. I now have an entity which has a date field and a user field like in:

@Entity
public class Transaction {
    // ...
    private User user;
    private LocalDateTime date;
    // ..
}

Now I simply want to query all transactions of a user within a certain time range. So I use the following query:

SELECT t FROM Transaction t WHERE t.user = :owner AND t.date BETWEEN :from AND :to

Strange enough this query does not give me any results. I also tried using the < and > operators but that did not help either. When I leave out the time range part I get the correct list of transactions for the user. When I execute the SQL query generated by Hibernate in the MySQL workbench I also get the expected results. I use the following snippet to execute the (named) query:

public <T> List<T> findList(Class<T> type, String queryName,
        Map<String, Object> params) {
    final EntityManager em = this.entityManager.get();
    final TypedQuery<T> query = em.createNamedQuery(queryName, type);
    params.forEach(query::setParameter);
    return query.getResultList();
}

This is simply called providing the query listed above and a map of named parameters like:

findList(Transaction.class, Transaction.BY_USER_AND_RANGE,
            ImmutableMap.of("owner", owner, "from", from, "to", to));

In my test case a persisted a single Transaction with the current date and created a range from yesterday to tomorrow for the query. Inspecting the table in the MySQL workbench shows that the transaction is there and that the date field has the correct type and contains the correct value. Yet my query won't give me any results.

Is there anything I'm missing?

Upvotes: 3

Views: 8079

Answers (1)

Lakhan singh
Lakhan singh

Reputation: 41

The date you are passing as param should also be

LocalDateTime see the example code below might help you 

LocalDate date = LocalDate.of(2015, 8, 11);
TypedQuery<MyEntity> query = this.em.createQuery("SELECT e FROM MyEntity e WHERE date BETWEEN :start AND :end", MyEntity.class);
query.setParameter("start", date.minusDays(2));
query.setParameter("end", date.plusDays(7));
MyEntity e = query.getSingleResult();

Upvotes: 2

Related Questions