Hibernate access to PostgreSQL custom data types

Hibernate ORM
1

Hello Team,

We are looking for your help on the below given issue.

Issue: We have created one custom data type in PostGreSQL database as given below:

CREATE TYPE temporal as (
start_date timestamp,
end_date timestamp
);

and this needs to be accessed by application using hibernate ORM, but concern is that we can’t select the any subtype (start_date or end_date) of temporal without using brackets () around custom data type as given below:

Create table testTemporal temporal_date temporal;

Select (temporal_date).start_date from testTemporal;

When we are trying to access start_date column using latest JPA/HIbernate version, it is not generating the queries without brackets around temporal_date like (temporal_date.start_date), which is throwing SQL Exception by saying that temporal_date is no table found in database. ( We are not using any native queries).

so please advise how to handle such scenario?

Anticipating a reply soon.

Regards
Anurag

2

When it comes to mapping such type, you could try to write a CompositeType to support this temporal custom type.

However, you won’t be able to reference individual columns in JPQL queries. You could only do that with native SQL.

So, you are better off using an Embeddable on the Java side that maps to the start_date and end_date columns in your table. This way, you need the start_date and end_date columns to be added to the table instead of having them maped as a DB-specific type.

3

Thanks Vlad for your response. Is there any plan to incorporate this feature in coming releases where custom types can be used by using JPQL?

4

The query parser has been rewritten in 6.0, so only after releasing 6.0 we could try to investigate it. You could add a Jira issue for this.

5

Thanks for your quick response !!