Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly.
Hypersistence Optimizer is that tool!
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.
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.
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.
Based on my book, High-Performance Java Persistence, this workshop teaches you various data access performance optimizations from JDBC, to JPA, Hibernate and jOOQ for the major rational database systems (e.g. Oracle, SQL Server, MySQL and PostgreSQL).
Escaping colon with backslash works too, like ::date.
Thanks for the tip