How do solve the PostgreSQL :: cast operator issue with JPA and Hibernate

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 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 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.

Enter your email address to follow this blog and receive notifications of new posts by email.

Advertisements

2 thoughts on “How do solve the PostgreSQL :: cast operator issue with JPA and Hibernate

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s