{"id":92,"date":"2015-08-26T15:40:54","date_gmt":"2015-08-26T15:40:54","guid":{"rendered":"http:\/\/www.solewing.org\/blog\/?p=92"},"modified":"2015-08-27T09:51:43","modified_gmt":"2015-08-27T09:51:43","slug":"hibernate-postgresql-and-lob-string","status":"publish","type":"post","link":"http:\/\/www.solewing.org\/blog\/2015\/08\/hibernate-postgresql-and-lob-string\/","title":{"rendered":"Hibernate, PostgreSQL, and @Lob String"},"content":{"rendered":"

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

[java]
\n@Entity
\n@Table(name = “note”)
\n@Access(AccessType.FIELD)
\nclass NoteEntity {<\/p>\n

@Id
\n private Long id;<\/p>\n

@Lob
\n @Column(name = “note_text”)
\n private String noteText;<\/p>\n

public NoteEntity() { }<\/p>\n

public NoteEntity(String noteText) { this.noteText = noteText }
\n}
\n[\/java]<\/p>\n

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.<\/p>\n

When using Hibernate with the PostgreSQL dialect, the manner in which a string attribute annotated with @Lob<\/code> 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<\/code> attribute values in order to avoid losing information.<\/p>\n

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.<\/p>\n

The Hibernate PostgreSQL9Dialect<\/code> stores @Lob String<\/code> 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<\/code>, we’d see that it gets mapped to table like this:<\/p>\n

\r\n  Column   |  Type  | Modifiers \r\n-----------+--------+-----------\r\n id        | bigint | not null\r\n note_text | text   | \r\n<\/pre>\n
The thing to notice here is that it uses text<\/code> type for the column associated with our @Lob String<\/code>.  This column can<\/em> 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.<\/p>\n
[java]
\nentityManager.persist(new NoteEntity(“This is a note!”));
\nentityManager.persist(new NoteEntity(“This is another note!”));
\n[\/java]<\/p>\n
If we then query the note table directly we’ll see something like this:<\/p>\n
\r\n  id   | note_text  \r\n-------+-----------\r\n 16675 | 64419\r\n 16677 | 64420\r\n<\/pre>\n
Obviously, the text of our notes isn’t really in the column.  So where is it?<\/em>  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.<\/p>\n
[sql]
\nSELECT id,
\n  convert_from(loread(
\n      lo_open(note_text::int, x’40000′::int), x’40000′::int), ‘UTF-8’)
\n  AS note_text
\nFROM note
\n[\/sql]<\/p>\n
\r\n  id   | note_text  \r\n-------+-----------------------\r\n 16675 | This is a note!\r\n 16677 | This is another note!\r\n<\/pre>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,14],"tags":[32,27,29,31,30],"class_list":["post-92","post","type-post","status-publish","format-standard","hentry","category-java","category-javaee","tag-clob","tag-hibernate","tag-jpa","tag-lob","tag-postgresql"],"_links":{"self":[{"href":"http:\/\/www.solewing.org\/blog\/wp-json\/wp\/v2\/posts\/92","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.solewing.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.solewing.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.solewing.org\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.solewing.org\/blog\/wp-json\/wp\/v2\/comments?post=92"}],"version-history":[{"count":10,"href":"http:\/\/www.solewing.org\/blog\/wp-json\/wp\/v2\/posts\/92\/revisions"}],"predecessor-version":[{"id":103,"href":"http:\/\/www.solewing.org\/blog\/wp-json\/wp\/v2\/posts\/92\/revisions\/103"}],"wp:attachment":[{"href":"http:\/\/www.solewing.org\/blog\/wp-json\/wp\/v2\/media?parent=92"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.solewing.org\/blog\/wp-json\/wp\/v2\/categories?post=92"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.solewing.org\/blog\/wp-json\/wp\/v2\/tags?post=92"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}