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.

Download free ebook sample

Email Address

10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:

• A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
• 3 chapters from my book, High-Performance Java Persistence,
• a 10% discount coupon for my book.