DEV Community

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

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

How to do a Select clause comparison of LocalDateTime in JPA

#java #jpa #sql #codever

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)