Hibernate, PostgreSQL & the @LOB annotation

If you use Hibernate and you need to store large objects with the @LOB annotation, it can get tricky when you need to support PostgreSQL and other databases. In that article, I like to walk you through that trickiness.

The @LOB annotation

If you like to store larger strings or data in your database, you can use the JPA annotation @LOB (javax.persistence.Lob). Then you can store larger strings/data in that column.

PostgreSQL large-object store

Databases like Oracle DB are storing large data within the column. But PostgreSQL does it differently. PostgreSQL has a dedicated system table to store large-object data. You can use operations like lo_get(…) or lo_put(..) to retrieve or store data.

Unfortunately, Hibernate uses the large-object table from PostgreSQL if you put the @LOB annotation on a String field. But PostgreSQL also has the TEXT type for a column to store a string value up to 1 GB. That should be enough for many use-cases and is much easier to handle if you like to do database operations without Hibernate.

How to use the TEXT column type instead of the large-object table in PostgreSQL

According to that Stackoverflow post, you can add another annotation to your @LOB string to store the string value within a TEXT column:

@Type( type = "org.hibernate.type.TextType")

That works great if you only need to support PostgreSQL.

What do you do if you need to support different databases?

Imagine you need to support Oracle DB and PostgreSQL. If you configure Hibernate to use TextType instead of the default MaterializedClobType, you get a database error if you insert a string with more than 4.000 chars. So that easy solution is not working fine, unfortunately.

One solution is to extend the JDBC database dialect class for PostgreSQL to treat CLOB types differently. But if you work on an application that should support different databases, you may have a configuration parameter for your app to define the database dialect. That could lead to annoying errors in production. Also, you maybe need to update your extension in the future to cope with newer database versions.

An alternative is to create a database-agnostic type for large objects that handles the type differently for PostgreSQL. Then you need to add the @Type annotation to the @LOB string like so:

@Column( name = "MY_LARGE_DATA_FIELD" )
@LOB
@Type( type = "me.geeknet.DatabaseAgnosticLobType" )
private String myLargeDataField;

And here is a code example how such a custom type could look like:

package me.geeknet;

import java.io.InputStream;
import java.util.Locale;
import java.util.Properties;

import static java.util.Objects.isNull;

import org.hibernate.type.AbstractSingleColumnStandardBasicType;
import org.hibernate.type.descriptor.java.StringTypeDescriptor;
import org.hibernate.type.descriptor.sql.ClobTypeDescriptor;
import org.hibernate.type.descriptor.sql.SqlTypeDescriptor;
import org.hibernate.type.descriptor.sql.VarcharTypeDescriptor;

public class DatabaseAwareLobType extends AbstractSingleColumnStandardBasicType<String> {
   
   public static final DatabaseAwareLobType INSTANCE = new DatabaseAwareLobType();

   private static Properties properties;

   /* Initializes the DatabaseAwareLobType with the given Hibernate configuration. */
   static {;
      try( InputStream inputStream = DatabaseAwareLobType.class.getClassLoader().getResourceAsStream( "db-config.properties" ) ) {
         properties = new Properties();
         properties.load( inputStream );
      }
      catch( Exception e ) {
         e.printStackTrace();
         properties = null;
      }
   }

   public DatabaseAwareLobType() {
      super( getDbDescriptor(), StringTypeDescriptor.INSTANCE );
   }

   public static SqlTypeDescriptor getDbDescriptor() {
      if( isPostgres() ) {
         return VarcharTypeDescriptor.INSTANCE;
      }
      else {
         return ClobTypeDescriptor.DEFAULT;
      }
   }

   private static boolean isPostgres() {
      if( isNull( properties ) ) {
         return false;
      }

      return properties.get( "hibernate.dialect" )
                       .toString()
                       .toLowerCase( Locale.ROOT )
                       .contains( "postgres" );
   }

   @Override
   public String getName() {
      return "database_aware_lob";
   }
}