PostgreSQL文本类型的相关JPA注解

1. Introduction

In this quick tutorial, we’ll explain how to manage the PostgreSQL TEXT type using the annotations defined by the JPA specification.

2. The TEXT Type in PostgreSQL

When working with PostgresSQL we may, periodically, need to store a string with an arbitrary length.

For this, PostgreSQL provides three character types:

CHAR(n)
VARCHAR(n)
TEXT

Unfortunately, the TEXT type is not part of the types that are managed by the SQL standard. This means that if we want to use JPA annotations in our persistence entities, we may have a problem.

This is because the JPA specification makes use of the SQL standard. Consequently, it doesn’t define a simple way to handle this type of object using, for example, a @Text annotation.

Luckily, we have a couple of possibilities for managing the TEXT data type for a PostgreSQL database:

We can use the @Lob annotation
Alternatively, we can also use the @Column annotation, combined with the columnDefinition attribute

Let’s now take a look at the two solutions beginning with the @Lob annotation.

3. @Lob

As the name suggests, a lob is a large object. In database terms, lob columns are used to store very long texts or binary files.

We can choose from two kinds of lobs:

CLOB – a character lob used to store texts
BLOB – a binary lob that can be used to store binary data

We can use the JPA @Lob annotation to map large fields to large database object types.

When we use the @Lob record on a String type attribute, the JPA specification says that the persistence provider should use a large character type object to store the value of the attribute. Consequently, PostgreSQL can translate a character lob into a TEXT type.

Let’s suppose we have a simple Exam entity object, with a description field, which could have an arbitrary length:

@Entity
public class Exam {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Lob
    private String description;
}

Using the @Lob annotation on the description field, we instruct Hibernate to manage this field using the PostgreSQL TEXT type.

Note that when we use Hibernate with PostgreSQL, the storage mechanics become unusual when handling a String attribute annotated with @Lob. We must understand how Hibernate persists @Lob String attribute values to avoid losing information.

For instance, PostgreSQL stores the contents of a column annotated with @Lob in a separate table, the column itself will only store a UID for each entry in that table. Therefore, this behaviour may lead to information loss. To solve this problem, we can either use @Column(columnDefinition=”TEXT”) along with the @Lob annotation or use only @Column(columnDefinition = “TEXT”).

4. @Column

Another option for managing the TEXT type is to use the @Column annotation, together with the columnDefinition property.

Let’s use the same Exam entity object again but this time we’ll add a TEXT field, which could be of an arbitrary length:

@Entity
public class Exam {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    
    @Lob
    private String description;
    
    @Column(columnDefinition = "TEXT")
    private String text;

}

In this example, we use the annotation @Column(columnDefinition = “TEXT”). Using the columnDefinition attribute allows us to specify the SQL fragment which will be used when constructing the data column for this type.

5. Bringing It All Together

In this section, we’ll write a simple unit test to verify our solution is working:

@Test
public void givenExam_whenSaveExam_thenReturnExpectedExam() {
    Exam exam = new Exam();
    exam.setDescription("This is a description. Sometimes the description can be very very long! ");
    exam.setText("This is a text. Sometimes the text can be very very long!");

    exam = examRepository.save(exam);

    assertEquals(examRepository.find(exam.getId()), exam);
}

In this example, we begin by creating a new Exam object and persisting it to our database. We then retrieve the Exam object from the database and compare the result with the original exam we created.

To demonstrate the point, if we quickly modify the description field on our Exam entity:

@Column(length = 20)
private String description;

When we run our test again we’ll see an error:

ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Value too long for column "TEXT VARCHAR(20)"

6. Conclusion

In this tutorial, we covered two approaches for using JPA annotations with the PostgreSQL TEXT type.

We began by explaining what the TEXT type is used for and then we saw how we can use the JPA annotations @Lob and @Column to save String objects using the TEXT type defined by PostgreSQL.

As always, the full source code of the article is available on GitHub.

Persistence

REST

Security