Introduction

While answering questions on the Hibernate forum, I stumbled on the following question regarding the PostgreSQL :: cast operation used in a JPA and Hibernate entity query.

Since it’s a very interesting use case, 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 of the :: type cast operator conflicts with the JPA : named parameter syntax.

So, how can we fix it?

There are two ways we can fix this issue. We can either escape the : character using a \ or use the CAST function.

Escaping the : character

If we escape each of the : character using \, so that :: becomes \:\:, then everything will work just fine:

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();

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

Using the CAST function

Another option to fix this issue is by 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.

I'm running an online workshop on the 11th of October about High-Performance SQL.

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW