Tag Archives: jpa

Hibernate, PostgreSQL, and @Lob String

4 Replies

Suppose we have the following entity which represents a persistent note — just some arbitrary text. When we use @Lob on an attribute of type String, the JPA specification says that the persistence provider should use a large object of character type to store the attribute value.

[java]
@Entity
@Table(name = “note”)
@Access(AccessType.FIELD)
class NoteEntity {

@Id
private Long id;

@Lob
@Column(name = “note_text”)
private String noteText;

public NoteEntity() { }

public NoteEntity(String noteText) { this.noteText = noteText }
}
[/java]

The challenge for the persistence provider is that large object support in databases is not standardized, and thus the means by which this feature is implemented tends to be rather database specific.

When using Hibernate with the PostgreSQL dialect, the manner in which a string attribute annotated with @Lob is recorded in the database is unusual. If you’re combining Hibernate-based persistence on a PostgreSQL database with direct SQL access (e.g. for data migration, warehousing, etc), you need to be aware of how Hibernate persists @Lob String attribute values in order to avoid losing information.

In PostgreSQL 9.x, all large objects are stored in a single table. Each large object has a unique identifier (UID). PostgreSQL provides many functions that can be used in SQL DML statements to perform CRUD operations on large objects.

The Hibernate PostgreSQL9Dialect stores @Lob String attribute values by explicitly creating a large object instance, and then storing the UID of the object in the column associated with attribute. If we ask Hibernate to generate the schema for our NoteEntity, we’d see that it gets mapped to table like this:

  Column   |  Type  | Modifiers 
-----------+--------+-----------
 id        | bigint | not null
 note_text | text   |

The thing to notice here is that it uses text type for the column associated with our @Lob String. This column can store the text of the comment itself, and it allows text of arbitrary length, but Hibernate doesn’t use the column in that way. Let’s persist a couple of notes and see what happens.

[java]
entityManager.persist(new NoteEntity(“This is a note!”));
entityManager.persist(new NoteEntity(“This is another note!”));
[/java]

If we then query the note table directly we’ll see something like this:

  id   | note_text  
-------+-----------
 16675 | 64419
 16677 | 64420

Obviously, the text of our notes isn’t really in the column. So where is it? The answer is that Hibernate explicitly created a large object for each note, and stored the UID of the object in the column. If we use some PostgreSQL large object functions, we can retrieve the text itself.

[sql]
SELECT id,
convert_from(loread(
lo_open(note_text::int, x’40000′::int), x’40000′::int), ‘UTF-8’)
AS note_text
FROM note
[/sql]

  id   | note_text  
-------+-----------------------
 16675 | This is a note!
 16677 | This is another note!

Okay, so why do you care? So long as Hibernate is the only thing that is manipulating your database, you probably don’t. Well, you might care if you wanted to do ad-hoc queries of the notes, since the query for the note text is not exactly intuitive. You certainly will care if you plan to use SQL queries to extract the data from the database and put it somewhere else — you probably don’t want to copy the string containing the UID for the large object that contains the text into another database.

Hibernate Envers, @OneToMany List, and @OrderColumn

2 Replies

Using Hibernate Envers with a @OneToMany association for a List property that uses @OrderColumn requires a little extra glue. Without this extra glue, Envers will throw a NullPointerException in a lazy initializer when you try to load revisions.

Suppose our domain model has a Sponsor entity. A Sponsor can have multiple business names, represented by a Sponsor Name entity. The names are ordered by preference, so we use a @OneToMany with a List. Here’s how we might map the Sponsor entity.

[java]
@Entity
@Audited
@Access(AccessType.FIELD)
public class SponsorEntity {

@OneToMany(mappedBy = “sponsor”, fetch = FetchType.LAZY, orphanRemoval = true)
@OrderColumn(name = “name_index”)
private List names = new ArrayList<>();

}
[/java]

And here’s the SponsorNameEntity.

[java]
@Entity
@Audited
@Access(AccessType.FIELD)
public class SponsorNameEntity {

@Column(nullable = false)
private String name;

@ManyToOne(optional = false, fetch = FetchType.LAZY)
private SponsorEntity sponsor;

}
[/java]

If we use these entities with Hibernate Envers, we’ll get an NPE when we try to load revisions of the SponsorEntity. It happens because the name_index column specified by the @OrderColumn annotation is not included in the audit table for SponsorNameEntity.

We can fix this easily, with an additional field on SponsorNameEntity and an extra annotation on SponsorEntity. The extra field on SponsorNameEntity is used to expose the value of the order column as a field:

[java]
@Entity
@Audited
@Access(AccessType.FIELD)
public class SponsorNameEntity {

// This field is used to capture the value of the column named
// in the @OrderColumn annotation on the referencing entity.
@Column(insertable = false, updatable = false)
private int name_index;

@Column(nullable = false)
private String name;

@ManyToOne(optional = false, fetch = FetchType.LAZY)
private SponsorEntity sponsor;

}
[/java]

In SponsorEntity we use an additional annotation to inform Envers about the field that contains the order column value:

[java]
@Entity
@Audited
@Access(AccessType.FIELD)
public class SponsorEntity {

@OneToMany(mappedBy = “sponsor”, fetch = FetchType.LAZY, orphanRemoval = true)
@OrderColumn(name = “name_index”)
@AuditMappedBy(mappedBy = “sponsor”, positionMappedBy = “name_index”)
private List names = new ArrayList<>();

}
[/java]

The positionMappedBy attribute of the @AuditMappedBy annotation informs Envers that the position of each entry in the list is given by the value of the name_index field that we added to SponsorNameEntity. It seems a little redundant, but we’re required to also specify the value of the mappedBy attribute, which should be the same as the value given in the @OneToMany annotation.

If you discover that you need this fix after you’ve already got some revisions out there in your audit tables, don’t forget to

  1. Add the column specified by @OrderColumn to the appropriate audit table. In our example, this column goes into the audit table for SponsorNameEntity.
  2. Initialize the column value in each of the existing rows. You can usually query the table associated with the entity to get the appropriate index value — e.g. in our case we’d query the table for SponsorNameEntity to get the name_index column value and use it to update the corresponding rows in the audit table.