Correct JPA Annotation for PostgreSQL's text type without Hibernate Annotations

Question

I'm developing an application using:

• Java 1.7
• JPA (included in javaee-api 7.0)
• Hibernate 4.3.8.Final
• PostgreSQL-JDBC 9.4-1200-jdbc41
• PostgreSQL 9.3.6

And I would like to use the PostgreSQL text datatype for some String attributes. As far as I know, in JPA this should be the correct annotation, to use text in PostgreSQL:

@Entity public class Product{     ...     @Lob     private String description;     .... } 

When I annotate my entity like this, I run into errors which look like this: http://www.shredzone.de/cilla/page/299/string-lobs-on-postgresql-with-hibernate-36.html

In short: It seems that hibernate and jdbc go not hand in hand for clob/text-types.

The solution described is working:

@Entity public class Product{     ...     @Lob     @Type(type = "org.hibernate.type.TextType")     private String description;     ... } 

But this has a significant downside: The source code needs hibernate at compile time, which should be unnecessary (That's one reason for using JPA in the first place).

Another way is to use the column annotation like this:

@Entity public class Product{     ...     @Column(columnDefinition = "text")     private String description;     ... } 

Which works nicely, BUT: Now I'm stuck with databases which have a text type (and is also called text ;) ) and if another database will be used in the future the annotations can be overlooked easily. Thus the possible error can be hard to find, because the datatype is defined in a String and therefore can not be found before runtime.

Is there a solution, which is so easy, I just don't see it? I'm very sure that I'm not the only one using JPA in combination with Hibernate and PostgreSQL. So I'm a little confused that I can't find more questions like this.

Just to complete the question, the persistence.xml looks like this:

<?xml version="1.0" encoding="UTF-8"?> <persistence version="1.0"   xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   xsi:schemaLocation="http://java.sun.com/xml/ns/persistence                         http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">   <persistence-unit name="entityManager">     <provider>org.hibernate.ejb.HibernatePersistence</provider>     <class>com.app.model.Product</class>     <properties>       <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />       <property name="javax.persistence.jdbc.url"         value="jdbc:postgresql://localhost:5432/awesomedb" />       <property name="javax.persistence.jdbc.user" value="usr" />       <property name="javax.persistence.jdbc.password" value="pwd" />       <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />       <property name="hibernate.jdbc.use_streams_for_binary" value="false" />       <property name="hibernate.hbm2ddl.auto" value="create-drop" />       <property name="show_sql" value="true" />     </properties>   </persistence-unit> </persistence> 

UPDATE:

• This problem is more or less equivalent with this question, the picked answer is the second way to do it described in this question, which I don't like due to the hibernate runtime dependency: store strings of arbitrary length in Postgresql

• This seems to be kind of related to: https://hibernate.atlassian.net/browse/JPA-48

Answer 1

Since the text type is not a part of the SQL standard there is no official JPA way I guess.

However, the text type is quite similar to varchar, but without the length limit. You can hint the JPA implementation with the length property of @Column:

@Column(length=10485760) private String description; 

Update: 10 MiB seems to be the maximum length for varchar in postgresql. The text is almost unlimited, according the documentation:

In any case, the longest possible character string that can be stored is about 1 GB.

Answer 2

I just had to add this annotation:

@Column(columnDefinition="TEXT") 

It did not work on its own. I had to recreate the table in the database.

DROP TABLE yourtable or just alter column type to text with ALTER TABLE statement