DEV Community

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

Posted on β€’ Edited on β€’ Originally published at codever.dev

3 1

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.

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Sentry image

See why 4M developers consider Sentry, β€œnot bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

πŸ‘‹ Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay