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

Since you're talking about what's really a deployment issue (auto-generated DDL), can't you just manually create the table as TEXT in Postgres and just not specify anything in the class? — chrylis -cautiouslyoptimistic-
– chrylis -cautiouslyoptimistic-, Commented Feb 18, 2015 at 16:30
Yes, I would like to be able to generate the DB from hibernate. — knoe
– knoe, Commented Feb 18, 2015 at 16:52
@chrylis When you say it like this, it sounds wrong :D My prefered way would be I use the annotations in the JPA-way described in the first place (Simply @Lob). And let hibernate/jdbc do the magic in the background. Like I know, an @Lob String should result in clob in DB2, Oracle, longvarchar in H2/HSQLDB, longtext or text in MySQL and text in PostgreSQL. My problem is, that hibernate and postgresql jdbc create an error, where should not be any issue. — knoe
– knoe, Commented Feb 18, 2015 at 19:47
I was looking for the opposite you wanted and your question was my answer. Curious. I just added the columnDefinition = "text". Thanks. — Neniel
– Neniel, Commented Aug 22, 2016 at 19:03

holmis83 · Accepted Answer · 2015-02-18 18:25:33Z

8

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.

edited Feb 18, 2015 at 18:25

answered Feb 18, 2015 at 16:41

holmis83

16.7k5 gold badges88 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

knoe Over a year ago

Yes, this will works in most cases, when the table should not be generated by the application. It results in a PostgreSQL column type varchar(1000000000). Which is limited, the text type is defined to store text without any limitation.

knoe Over a year ago

As far as I know Hibernate and PostgreSQL's jdbc driver, are not really compatible to each other at this point, hibernate tries to get a clob, because the lob annotation and the jdbc driver offers the content of the lob not the address. I don't know which is the right way, but the definitely should arrange a solution, so the end user can use this stuff. Because it's just ugly to stumble into this problem.

user330315 Over a year ago

@knoe: You can also use varchar without a limit in Postgres. In Postgres there is really no difference at all between text or varchar so just go with whatever the obfuscation layer works best with

knoe Over a year ago

@holmis83 BTW: I tried this just now, and I get this: PSQL ERROR: length for type varchar cannot exceed 10485760 @a_horse_with_no_name Yes, this is quite the same as to use the @Column(columnDefinition="text"), when I get you right. This would be the same as solution two from the original question.

holmis83 Over a year ago

@knoe Updated my answer. I would not recommend auto-generated DDL for a production system.

|

Eliska P. · Accepted Answer · 2019-03-26 21:23:51Z

6

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

answered Mar 26, 2019 at 21:23

Eliska P.

731 silver badge5 bronze badges

2 Comments

datenhahn Over a year ago

For me the uppercase TEXT did not work with postgres, it had to be all lowercase, otherwise I got the error message ERROR: type "TEXT" does not exist at character. This worked: @Column(columnDefinition="text")

DV82XL Over a year ago

This worked for me (uppercase and lower) and I did not have to recreate the table. (postgres 13)

Predrag Maric · Accepted Answer · 2015-02-18 16:33:46Z

4

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.

answered Feb 18, 2015 at 16:33

Predrag Maric

24.5k5 gold badges57 silver badges72 bronze badges

4 Comments

knoe Over a year ago

Yes, I want to generate DB from code. If I wouldn't need it. I figured out, that @Column(columnDefinition = "longvarchar") private String description; Would just work fine, because the hibernate/jdbc driver combination then expects text and not a clob-address. Which is the problem with the long/text error from the link. But the table creation would just fail, because postgresql doesn't know the type longvarchar. If I could define an alias like longvarchar=text in PostgreSQL by myself, this could be interesting...

knoe Over a year ago

Which is more or less, the same as your solution, because the columnDefiniton is only used at table definition.

user330315 Over a year ago

@knoe: there is absolutely no difference between varchar and text in Postgres.

knoe Over a year ago

@a_horse_with_no_name That's right, but the generated column for a String without annotation is varchar(255) but I don't want it to be limited, that is the problem in the first place.

Torsten Krah · Accepted Answer · 2015-12-15 10:31:45Z

4

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.

answered Dec 15, 2015 at 10:31

Torsten Krah

3783 silver badges9 bronze badges

6 Comments

knoe Over a year ago

Looks interesting, I'll give it a try. But I still don't see why this should be needed at all.

Torsten Krah Over a year ago

You don't need it - it works already using CLOB from the JDBC Driver with OIDs to work with the String. If you want to change that it would be better addressed at the JDBC Driver - imho Hibernate is fine here.

Michael J Lawley Over a year ago

The downside of just using the "works already" CLOB like this is extremely poor performance - imho this is not fine

Torsten Krah Over a year ago

It may not be fine, but its not hibernates responsibility to fix that - that's why i did write you should address this to the jdbc driver developers.

chrisinmtown Over a year ago

Please explain how to wire this in - I think this will help me run JUnit tests with Derby, and deploy to MariaDB, with MariadDB column type definition annotations in the code.

|

Collectives™ on Stack Overflow

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

4 Answers 4

7 Comments

2 Comments

4 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

2 Comments

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related