DEV Community

Cover image for How to do a Select clause comparison of LocalDateTime in JPA
Adrian Matei for Codever

Posted on • Updated on • Originally published at codever.dev

 

How to do a Select clause comparison of LocalDateTime in JPA

The native SQL query we want to map in JPA is similar to the following:

select *
from PARTNER
where EVENT_TIMESTAMP >= timestamp '2021-09-17 10:00:00'
  and EVENT_TIMESTAMP < timestamp '2021-09-17 11:00:00'
Enter fullscreen mode Exit fullscreen mode

where the two timestamps should come as query parameters.

With JPA you can use a TypedQuery for example and set the LocalDateTime values to query parameters via the setParameter method:

@Stateless
public class PartnerDataRepository {

    @Inject private EntityManager em;

    public List<PartnerData> findPartnerDataWithinInterval(
      LocalDateTime fromDatetime, LocalDateTime toDatetime) {
        TypedQuery<PartnerData> query =
            em.createNamedQuery(
                PartnerData.FIND_PARTNER_DATA_IN_TIME_INTERVAL, PartnerData.class);

        query.setParameter(PartnerData.FROM_DATETIME, fromDatetime);
        query.setParameter(PartnerData.TO_DATETIME, toDatetime);

        return query.getResultList();
    }
}
Enter fullscreen mode Exit fullscreen mode

In the named query itself you can directly pass the parameters before : as usual, and the implementation (in my case Hibernate) takes care of the rest :

@Entity
@Access(AccessType.FIELD)
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@Table(name = PartnerData.TABLE_NAME)
@NamedQueries({
 @NamedQuery(
      name = PartnerKerndaten.FIND_PARTNER_DATA_IN_TIME_INTERVAL,
      query = "select m from PartnerData m where eventTimestamp >= :fromDatetime and eventTimestamp < :toDatetime")
})
public class PartnerData {
  public static final String TABLE_NAME = "PARTNER";

  public static final String FROM_DATETIME = "fromDatetime";
  public static final String TO_DATETIME = "toDatetime";

  public static final String FIND_PARTNER_DATA_IN_TIME_INTERVAL =
      "findPartnerDataWithinInterval";

  //... rest ignored for brevity
}
Enter fullscreen mode Exit fullscreen mode

Shared ❤️ from Codever. 👉 use the copy to mine functionality to add it to your personal snippets collection.

Top comments (0)

An Animated Guide to Node.js Event Loop

Node.js doesn’t stop from running other operations because of Libuv, a C++ library responsible for the event loop and asynchronously handling tasks such as network requests, DNS resolution, file system operations, data encryption, etc.

What happens under the hood when Node.js works on tasks such as database queries? We will explore it by following this piece of code step by step.