Accessing a Lob property from postgres in the view

Question

I have a standard Spring Framework (v3.1.2) java (v1.6) database backed (postgres v9.1) web application, which runs on Apache Tomcat (v6.0). Please note that I am using Hibernate 3.6.10.Final and Spring Data JPA 1.2.0.RELEASE.

I have two database entity classes - Chapter and ChapterText - with a one-to-one relationship between them...

@Entity @Table(name="chapter")
public class Chapter implements Comparator<Chapter> {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="CHAPTER_SEQ")
    @SequenceGenerator(name="CHAPTER_SEQ", sequenceName="chapter_seq", allocationSize=1)
    @Column(name="id")
    private Long id;

    @OneToOne(cascade=CascadeType.ALL, optional=false, fetch=FetchType.LAZY, orphanRemoval=true) @PrimaryKeyJoinColumn
    private ChapterText chapterText;

    // other properties plus setters and getters
}

@Entity @Table(name="chapterText")
public class ChapterText {

    @Id
    @GeneratedValue(generator="foreign")
    @GenericGenerator(name="foreign", strategy="foreign", parameters={ @Parameter(name="property", value="parent") })
    @Column(name="id")
    private Long id;

    @OneToOne(mappedBy="chapterText", optional=false)
    private Chapter parent;

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

    // setters and getters
}

Now, as ChapterText contains a @Lob property, which can potentially hold a large amount of text, i've marked the chapterText property in the Chapter class such that it will be lazily loaded.

I have the OpenEntityManagerInViewFilter setup and configured, so I can add a Chapter entity to the model (with the name "chapter"), then in my JSPX file I should be able to do this...

${chapter.chapterText.body}

This should lazily-load the ChapterText entity and include the value of the body property in the XHTML page generated for the calling browser.

This is an entrely standard way of displaying information from a database, which i've been using for years. However, this is the first time i've had to use a @Lob property with Postgres. When I do this, i'm presented with the following stack trace...

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: javax.servlet.jsp.JspException: javax.el.ELException: Error reading 'body' on type project.vo.db.ChapterText_$$_javassist_5
.
.
.
javax.servlet.ServletException: javax.servlet.jsp.JspException: javax.servlet.jsp.JspException: javax.el.ELException: Error reading 'body' on type project.vo.db.ChapterText_$$_javassist_5
.
.
.
javax.servlet.jsp.JspException: javax.el.ELException: Error reading 'body' on type project.vo.db.ChapterText_$$_javassist_5
.
.
.
javax.el.ELException: Error reading 'body' on type project.vo.db.ChapterText_$$_javassist_5
.
.
.
org.hibernate.exception.GenericJDBCException: could not load an entity: [project.vo.db.ChapterText#1]
.
.
.
org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.

From what i've been able to find out, Posgres requires accessing Large Objects to be accessed via a transaction with auto-commit set to false.

Annoyingly, the only approach i've been able to think of to get around this is to create a repository method which fetches the @Lob property, mark it with @Transactional and load the value into a bean specifically created for display purposes. While that does work, it seems a little inelegant.

Does anyone have any ideas for an alternative approach?

when you say a lot of text how much text is it? Are the lob streaming interfaces helpful or are you retrieving all at once? — Chris Travers, Apr 23 '13 at 09:11
Unfortunately, I couldn't find a solution to this. In the end, I moved the body property into the Chapter class and annotated it "@Column(name="chapterBody") @Lob @Basic(fetch=FetchType.LAZY)" — Jazz, Jan 17 '14 at 16:09

score 2 · Answer 1 · answered Apr 23 '13 at 12:37

First, I think you may want to rethink your design a bit. If the text is below 1GB, and you don't have to seek within it, you are probably better off with a text field than a LOB. Such is easier to process and manage. If the data is between 1GB and 2GB per row, you may need to use a LOB due to size. If you need to be able to query, chunking (i.e. seeking by offset) then LOBs are appropriate. If neither of these are appropriate, TEXT and BYTEA fields are preferable. Note that PostgreSQL automatically moves large attributes into secondary storage so you aren't making scans slower by having lots of inlined data (basically, anything over 4kb must be moved to secondary storage) so if that's your issue, then I wouldn't worry about it. TEXT fields are easier to manage so they would be my first choice. Also for text data, LOBs' encoding makes them worse for network traffic than TEXT fields unless you are chunking.

Now, if this is not acceptable, you can use lo_read() to read the lob and present as a bytea in your view.

At some point i'll look into doing a redesign. I'd say that the largest an average chapter will be is ~ 100,000 words. Lets say that the average word in a chapter has 5/6 characters, together with spaces... thats still no where near a gigabyte of data. — Jazz, Jan 17 '14 at 16:17

Accessing a Lob property from postgres in the view

1 Answers1