I have a JPA 2 application ( with Hibernate 3.6 as the JPA implementation ) that uses Postgresql ( with the 9.0-801.jdbc3 JDBC driver ).
I am having trouble mapping "timestamp with time zone" fields into my JPA entities.
Here is an example:
CREATE TABLE theme
(
id serial NOT NULL,
# Fields that are not material to the question have been edited out
run_from timestamp with time zone NOT NULL,
run_to timestamp with time zone NOT NULL,
CONSTRAINT theme_pkey PRIMARY KEY (id ),
CONSTRAINT theme_name_key UNIQUE (name )
)
I have tried to map as follows:
@Entity
@Table(schema = "content", name = "theme")
public class Theme extends AbstractBaseEntity {
private static final long serialVersionUID = 1L;
@Column(name = "run_from")
@NotNull
@Temporal(TemporalType.TIMESTAMP)
private Date runFrom;
@Column(name = "run_to")
@NotNull
@Temporal(TemporalType.TIMESTAMP)
private Date runTo;
/* The rest of the entity has been edited out */
I keep on getting an exception with the following root cause: Caused by: org.hibernate.HibernateException: Wrong column type in public.backend_themetopic for column created. Found: timestamptz, expected: date
What I have tried
java.util.Calendar
with java.util.Date
- made no differencejava.sql.Timestamp
- complained that I cannot apply the @Temporal
annotation to a Timestamp
org.joda.time.DateTime
with a custom @Type
annotation ( @Type(type="org.joda.time.contrib.hibernate.PersistentDateTimeTZ")
) also did not workConstraints
My question is: how should I map these timezone aware timestamps into my JPA entities?
I eventually made this "work" - in a hackish sort of way - by turning off schema validation.
Previously, I had <property name="hibernate.hbm2ddl.auto" value="validate"/>"hibernate.hbm2ddl.auto"
in my persistence.xml. When I commented out this property, my app server started and the model "worked".
The final form of my entity was:
@Entity
@Table(schema = "content", name = "theme")
public class Theme extends AbstractBaseEntity {
private static final long serialVersionUID = 1L;
@Column(name = "run_from", columnDefinition = "timestamp with time zone not null")
@NotNull
@Temporal(TemporalType.TIMESTAMP)
private Date runFrom;
@Column(name = "run_to", columnDefinition = "timestampt with time zone not null")
@NotNull
@Temporal(TemporalType.TIMESTAMP)
private Date runTo;
/* Getters, setters, .hashCode(), .equals() etc omitted */
After reading quite a bit on this, I got the impression is that there is no easy way to map Postgresql timestamp with time zone columns.
Some JPA implementation + database combinations support this natively ( EclipseLink + Oracle is one example ). For hibernate, with jodatime extensions, it is possible to store timezone aware timestamps using a normal timestamp + a varchar field for the timezone( I could not do that since I was constrained from changing the database schema ). Jadira user types or completely custom user types can also be used to tackle this problem.
I need to note that my use-case for this entity is "read only", so I could get away with a seemingly naive "solution".