C64 is ready

#Hibernate

Hibernate 4 schema generation with Maven

While upgrading my blog software Cilla to Java 8 and Hibernate 4, I found out that the old hibernate3-maven-plugin refused to create schema.sql files. Well, it wasn't really surprising. The name of the plugin already implied that the plugin won't play with the next major release of Hibernate.

I could not spot an official update of the plugin. Instead, I found Kai Moritz new Hibernate 4 maven plugin, which turned out to be very useful.

One key feature is to set up and initialize a local database for unit testing. I don't need this feature for Cilla (yet 😉). All I need is a hbm2ddl style generation of a SQL schema file for setting up new instances of my blog software from scratch. It turned out that the plugin was easily configured that way, and so it got almost a drop-in replacement for the old plugin.

This is what the <plugins> section of the project's pom file looks like:

<plugin>
    <groupId>de.juplo</groupId>
    <artifactId>hibernate4-maven-plugin</artifactId>
    <version>1.0.4</version>
    <executions>
        <execution>
            <goals>
                <goal>export</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <hibernateDialect>org.hibernate.dialect.PostgreSQL82Dialect</hibernateDialect>
        <target>NONE</target>
        <type>CREATE</type>
    </configuration>
</plugin>

With the target set to NONE, the schema.sql file is quietly generated while building the project. If set to SCRIPT, a copy will be dumped to stdout.

A CREATE type only generates create statements of the database. The default is BOTH, which creates drop and create statements.

Since no actual database is created, there is no need to add user, password and url parameters.

A list of all configuration options can be found here. The plugin is available at Maven Central.

hibernate3-maven-plugin fails with Java 1.7

If you're using Maven's hibernate3-maven-plugin for creating a DDL file from your entities, you might encounter the following error when using Java 1.7:

Execution default of goal org.codehaus.mojo:hibernate3-maven-plugin:2.2:hbm2ddl failed:
An AnnotationConfiguration instance is required

The reason seems to be a broken JRE detection in the Mojo code, which mistakenly assumes that Java 1.7 does not support annotations. However, I haven't checked that in depth.

The fix is pretty easy. In the plugin configuration of the hibernate3-maven-plugin, add an implementation property to the componentProperties like this:

<plugin>
  <groupId>org.codehaus.mojo</groupId>
  <artifactId>hibernate3-maven-plugin</artifactId>
  <version>2.2</version>
  <configuration>
    <componentProperties>
      <implementation>annotationconfiguration</implementation>
    </componentProperties>
  </configuration>
</plugin>

This enforces the use of an AnnotationConfiguration instance.

String LOBs on PostgreSQL with Hibernate 3.6

For String properties that may contain more than 255 characters, it is advised to add a @Lob annotation to the appropriate property. For example, to keep a comment text in an entity, one would write:

@Lob
public String getComment() { return comment; }
public void setComment(String comment) { this.comment = comment; }

On PostgreSQL, a large string is usually kept in a TEXT column (instead of VARCHAR). However, after updating to Hibernate 3.6, an exception was suddenly thrown when accessing such a property (along with an SQLState: 22003 from PostgreSQL):

org.postgresql.util.PSQLException: Bad value for type long : This is some text...
	at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultSet.java:2796)
	at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:2019)
	at org.postgresql.jdbc4.Jdbc4ResultSet.getClob(Jdbc4ResultSet.java:43)
	at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getClob(AbstractJdbc2ResultSet.java:384)
        ... and more

Obviously, the PostgreSQL connector tried to interprete the textual content as a long integer now, which - of course - results in a failure. PostgreSQL knows two ways of storing binary large objects, either as BYTEA within the table space, or as OID in a separate place and referenced by a numerical identifier. It seems that Hibernate 3.6 suddenly treats TEXT columns like OID columns as well.

In the internet, I have found similar problems related to @Lob annotated byte[] properties. A common solution was to add a @Type annotation to the property.

An attempt to add @Type(type = "org.hibernate.type.MaterializedClobType") did not help at all. Instead, @Type(type = "org.hibernate.type.TextType") did the trick:

@Lob
@Type(type = "org.hibernate.type.TextType")
public String getComment() { return comment; }
public void setComment(String comment) { this.comment = comment; }

Update: An alternative proposed by valsaraj is to use @Column(columnDefinition = "text"), which requires not to use @Lob at Strings at all:

@Column(columnDefinition = "text")
public String getComment() { return comment; }
public void setComment(String comment) { this.comment = comment; }

I think this is the better solution, however it requires the underlying database to support the text column type.

For PostgreSQL, it now works fine again, even without needing to alter the table column type. However I couldn't check the impact of the annotation on other DBMS (like Oracle). Your feedback is welcome.

How to fetch a random entry with Hibernate

I recently found myself in the situation where I needed Hibernate to query a single, random entry from a table of Picture entities.

There is a simple way. Some DBMS allow to shuffle the result set by bringing the rows into a random order. For instance, in MySQL it is possible to use a query like this:

SELECT id FROM picture ORDER BY rand() LIMIT 1;

Since Hibernate delegates unknown function calls to the underlying DBMS, rand() could be used in a HQL query as well:

q.createQuery("FROM picture ORDER BY rand()").setMaxResults(1);

However this query would require MySQL, so we would sacrifice the benefit of Hibernate acting as an abstraction layer to the underlying database. HQL on the other hand does not offer a similar function.

A solution is to use the pagination technique. First we count the number of entries, and then select a random entry using setFirstResult(). With Hibernate Criteria, it would look something like this:

Criterion restriction = yourRestrictions;
Object result = null;  // will later contain a random entity
Criteria crit = session.createCriteria(Picture.class);
crit.add(restriction);
crit.setProjection(Projections.rowCount());
int count = ((Number) crit.uniqueResult()).intValue();
if (0 != count) {
  int index = new Random().nextInt(count);
  crit = session.createCriteria(Picture.class);
  crit.add(restriction);
  result = crit.setFirstResult(index).setMaxResults(1).uniqueResult();
}

restriction contains further restrictions to the result set (like only pictures that have been published). At the end, result contains a random single entry from the Picture entity, or null if the result set was empty.