CODEWITHSUNDEEP
javahibernatepostgresqljpajdbc
knoe
knoe

Reputation: 664

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

I'm developing an application using:

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:

Upvotes: 41

Views: 61637

Answers (4)

Eliska P.
Eliska P.

Reputation: 73

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

Upvotes: 6

Torsten Krah
Torsten Krah

Reputation: 368

If you want to use plain JPA you could just remap the used CLOB type on the Dialect like this:

public class PGSQLMapDialect extends PostgreSQL9Dialect {


  @Override
  public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {
    if (Types.CLOB == sqlTypeDescriptor.getSqlType()) {
      return LongVarcharTypeDescriptor.INSTANCE;
    }
    return super.remapSqlTypeDescriptor(sqlTypeDescriptor);
  }


}

So it won't use the CLOB mapping from the JDBC driver which uses a OID for the column and stores/loads the text via large object handling. This would just result in setString and getString calls on the createt text column on the Postgres JDBC Driver via the VarcharTypeDescriptor class.

Upvotes: 4

holmis83
holmis83

Reputation: 16644

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.

Upvotes: 8

Predrag Maric
Predrag Maric

Reputation: 24433

I would go with simple private String description;. The column type is only a problem if you are generating the database from your code, because it will be generated as varchar instead of text.

It is great to code in database agnostic way, and without any JPA vendor specific things, but there are cases where this just isn't possible. If the reality is that you will have to support multiple database types with all their specifics, then you have to account for those specifics somewhere. One option is to use columnDefinition for defining column type. Another is to leave the code as it is, and just change the column type in the database. I prefer the second one.

Upvotes: 3

Related Questions