Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!
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.
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.
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?
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.
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.
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.
