Introduction

While answering questions on the Hibernate forum, I stumbled on the following question, and, since it’s very interesting, I decided to turn the answer into a dedicated blog post.

Domain Model

Considering we have the following entity:

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    @Id
    @GeneratedValue
    private Long id;

    private String title;

    @Column(name = "created_on")
    private LocalDateTime createdOn;

    //Getters and setters omitted for brevity
}

As I explained in this article, Hibernate supports Java 1.8 Date/Time types since version 5.1. Now that they were included in JPA 2.2, every JPA provider should start supporting them.

Now, let’s persist the following three Post entities:

Post part1 = new Post();
part1.setTitle("High-Performance Java Persistence, Part 1");
part1.setCreatedOn(
    LocalDateTime.now().with(
        TemporalAdjusters.previous(DayOfWeek.MONDAY)
    )
);
entityManager.persist(part1);

Post part2 = new Post();
part2.setTitle("High-Performance Java Persistence, Part 2");
part2.setCreatedOn(
    LocalDateTime.now().with(
        TemporalAdjusters.previous(DayOfWeek.TUESDAY)
    )
);
entityManager.persist(part2);

Post part3 = new Post();
part3.setTitle("High-Performance Java Persistence, Part 3");
part3.setCreatedOn(
    LocalDateTime.now().with(
        TemporalAdjusters.previous(DayOfWeek.THURSDAY)
    )
);
entityManager.persist(part3);

The first Post is created on a Monday, the second one on a Tuesday, and the last one on a Thursday.

The problem

We would now like to run a query so that we get all Post entities matching the same day of the week as the one we provide through a query parameter.

For this, we are using a native SQL query because it’s like a Magic Wand.

If you are running on PostgreSQL, you might want to use the date_part function and cast the parameter using the :: type conversion operator.

List<Post> posts = entityManager.createNativeQuery(
    "SELECT * " +
    "FROM post " +
    "WHERE " +
    "   date_part('dow', created_on) = " +
    "   date_part('dow', :datetime::date)", Post.class)
.setParameter("datetime", Timestamp.valueOf(
    LocalDateTime.now().with(
        TemporalAdjusters.next(DayOfWeek.MONDAY)))
    )
.getResultList();

However, when running the query above, Hibernate throws the following exception:

java.lang.IllegalArgumentException: Unknown parameter name : datetime
    at org.hibernate.query.internal.QueryParameterBindingsImpl.getBinding(QueryParameterBindingsImpl.java:208)
    at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:486)
    at org.hibernate.query.internal.NativeQueryImpl.setParameter(NativeQueryImpl.java:586)

That’s because the :: type cast operator conflicts with the JPA : named parameter syntax. So, how can we fix it?

The fix

We can easily fix this issue by just using the PostgreSQL CAST function.

List<Post> posts = entityManager.createNativeQuery(
    "SELECT * " +
    "FROM post " +
    "WHERE " +
    "   date_part('dow', created_on) = " +
    "   date_part('dow', cast(:datetime AS date))", Post.class)
.setParameter("datetime", Timestamp.valueOf(
    LocalDateTime.now().with(
        TemporalAdjusters.next(DayOfWeek.MONDAY)))
    )
.getResultList();

assertEquals(1, posts.size());
assertEquals(
    "High-Performance Java Persistence, Part 1", 
    posts.get(0).getTitle()
);

And that works as expected.

Conversion from String

Alternatively, we could convert from a String value as well. Consider that we get the query parameter as a String:

DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(
    "dd-MM-YYYY"
);

String dateString = dateTimeFormatter.format(
    LocalDateTime.now().with(TemporalAdjusters.next(DayOfWeek.MONDAY))
);

This time, instead of using a simple CAST function call, we need to use the TO_TIMESTAMP function:

List<Post> posts = entityManager.createNativeQuery(
    "SELECT * " +
    "FROM post " +
    "WHERE " +
    "   date_part('dow', created_on) = " +
    "   date_part('dow', to_timestamp(:datetime, 'dd-MM-YYYY'))", Post.class)
.setParameter("datetime", dateString)
.getResultList();

assertEquals(1, posts.size());
assertEquals(
    "High-Performance Java Persistence, Part 1", 
    posts.get(0).getTitle()
);

And, it works like a charm.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Native SQL queries are very powerful, and that’s why you should use them. However, few database-specific operators, like the :: type conversion operator might interfere with the JPA-specific named parameter syntax.

Fixing this issue is actually very easy, as you can replace the operator in question with its equivalent SQL function call.