CODEWITHSUNDEEP
sqlpostgresqlspring-data-jpalob
Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

JPA with Postgres anomalously writes text in @Lob column as a number

I am trying to use a @Lob column with a Java String type to map its content to TEXT inside Postgres. Here is the relevant entity:

@Entity(name="metadata")
public class Metadata {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "created_on")
    @ColumnDefault(value="CURRENT_TIMESTAMP")
    @Generated(GenerationTime.INSERT)
    private LocalDateTime createdOn;

    @Lob
    @Column(name = "content")
    private String content;

    @Column(name = "draft")
    private Boolean draft;

    @OneToMany(cascade = javax.persistence.CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "metadata")
    private List<Attachment> attachments;

    public void addAttachment(Attachment attachment) {
        if (attachments == null) {
            attachments = new ArrayList<>();
        }
        attachments.add(attachment);
        attachment.setMetadata(this);
    }

    // getters and setters
}

I have code which creates a new Metadata entity based on use input. I verify manually in IntelliJ debug mode that this entity has the content set to its intended value (which happens to be a JSON string). However, when I check Postgres after running the code, I see this data:

my_db=> select * from metadata;
 id | content |       created_on        | draft
----+---------+-------------------------+-------
  1 | 49289   | 2021-04-26 14:21:25.733 | t
(1 row)

Note carefully that the strange values 49289 is appearing where we would expect to see a JSON string. Note that I also verified from the command line that the correct table is what was created:

CREATE TABLE scarfon_attachment (
    id bigint NOT NULL,
    contents text,
    filename character varying(255),
    scarfon_id bigint NOT NULL
);

All the other columns in the entity/table are working as expected. What could be the problem with the @Lob annotation. For reference, I am running a fairly old version of Postgres (9.2), but it is not that ancient.

Upvotes: 7

Views: 9014

Answers (2)

West Side
West Side

Reputation: 182

When data is stored in a LOB column, it is not stored directly in the regular row data like other columns. Instead, it is stored in a separate location in the database, and the row only contains a reference (usually a numeric identifier) to that separate location.

The reason you see numbers like 1124 instead of the real TEXT code when you query the database directly is that the actual TEXT content is stored in a separate location, and the number 1124 is a reference (or pointer) to that location. This is a way to manage and optimize the storage of large data that would not fit well within the regular row data structure.

When you retrieve the data using the application, the JPA will handle the process of retrieving the LOB data and mapping it back to the layout field as a string containing the actual TEXT.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

My first doubt here owed to many sources suggesting multiple ways for creating a TEXT column. For example, this Baeldung post suggests using @Lob in addition to use a definition with the @Column annotation.

As it turns out, @Lob is not interpreted the same by all databases. In the case of Postgres, just using @Lob alone will result in Postgres storing the column contents in a different table, with the column annotated with @Lob just storing an ID for each entry in that table. While it has been suggested here that also specifying the correct type via the @Type annotation can remedy this problem, I decided to go with the second suggestion by the Baledung post, which is using @Column:

@Lob
@Column(columnDefinition="TEXT")
private String content;

This worked fine, and the resulting Postgres table had a TEXT definition as expected. The only potential issue with the above might center around portability to other SQL databases which perhaps do not support a TEXT type, or maybe support some alternative. I did not test beyond Postgres and H2, but in both cases the above was working without issues.

Upvotes: 4

Related Questions