Category Archives: JavaEE

Hibernate, PostgreSQL, and @Lob String

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

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.

Property Injection with CDI

Over the last several months, I’ve been working on a large enterprise web application development effort using Java EE 7. As a long user of the Spring Framework for Java web application development, one of the things I immediately missed were the simple property injection techniques supported by Spring’s PropertyOverrideConfigurer and PropertyPlaceholderConfigurer.

Out of the box, CDI provides no good solution for property injection. However, CDI’s really outstanding extension mechanism makes it really easy to get CDI to do things that it doesn’t inherently support. I made a CDI extension named Pinject that provides all of the property injection support needed by the typical Java web application.

It’s as simple as this:

[java]
package com.example.myapp;

public class MyBean implements MyService {

@Inject
@Property
private URL location;

@Inject
@Property
private int maxRetries;

}
[/java]

And we place a properties file named beans.properties on the classpath containing the
values to inject:

[java]
com.example.myapp.MyBean.location=http://www.google.com
com.example.myapp.MyBean.maxRetries=3
[/java]

Pinject provides a great deal of flexibility in resolving and converting property values and injecting them into your beans. It even provides its own extension points so that you can easily cover use cases I didn’t think to provide. Please check it out and let me know if you find it useful.

Wildfly Datasource Deployment

One of the things I really like about Wildfly and its immediate predecessors is the ability to deploy JDBC drivers and datasource configurations without needing to edit the configuration or restart the container.

JDBC Driver Deployments

The ability to treat a JDBC driver as a deployment depends on the Java service loader mechanism that was introduced with JDK 6 and incorporated in JDBC 4.  Virtually every database vendor has a JDBC4-compliant driver that can be auto-deployed in Wildfly by simply dropping the JAR file for the driver into the container’s deployments directory.  Once the JDBC driver has been deployed, one or more datasources can be configured that make use of it.

DataSource Deployments

The ability to deploy datasource configurations is enabled by IronJacamar which Wildfly uses for its Java Connector Architecture (JCA) support.   An IronJacamar datasource configuration is specified in an XML file that defines the JNDI name for the datasource along with all of the configuration needed to access and manage the underlying database resource.

Here’s an example (non-XA) datasource configuration for a MySQL datasource. As you can see, most of the element and attribute values in the file are fairly intuitive.

[xml]



jdbc:mysql://localhost:3306/my_database
com.mysql.jdbc.Driver
mysql-connector-java-5.1.29.jar

my_username my_password








[/xml]

You can deploy a datasource descriptor such as the previous example, by placing it a file with name that is suffixed with -ds.xml and dropping it into the deployments directory. The example datasource shown above might be named my_datasource-ds.xml.

By copying my_datasource-ds.xml into the deployments directory, IronJacamar will use the database driver specified in the descriptor to create a datasource object and bind it into the container’s JDNI registry with the given JNDI name. Once the datasource is deployed, your applications can access it just as they would any other JNDI resource.

XA DataSource Deployments

IronJacamar also supports XA datasources, which are important if you need to make use of transactions that involve more than one database and/or a database and a JMS message broker.

The configuration descriptor for an XA datasource is quite similar to the basic datasource, as you can see in the following example for an XA datasource using a PostgreSQL driver.

[xml]


localhost
5458
my_database
org.postgresql.xa.PGXADataSource

postgresql-9.1-902.jdbc4.jar

my_username my_password



true
false





[/xml]

Note that the property names specified by <xa-datasource-property> are driver specific — a driver from another database vendor would use different property names.

References

MySQL, Java EE, and Transactions

This probably isn’t news to anyone but me, but I thought I’d post it anyway for anyone else who reads the documentation only as a last resort.

I was recently debugging a Java EE application that was performing some errant database updates at times when it should have been rolling back the transaction. My first thought was that the (checked) exception being thrown wasn’t properly marked for rollback. After some investigation, I determined that the transaction was indeed being rolled back, but the database seemed to be ignoring the rollback and updating the database anyway.

Digging in a bit more, I found out that the application was using MySQL 5.1.x. I spent some time reviewing the user manual and discovered that the default database engine in that version of MySQL does not support transactions. I found this a bit surprising (hasn’t transaction support been around for a couple of decades now?) but switching to a different database engine in the MySQL configuration was simple enough. I found the instructions for doing so in the section of the manual entitled Setting the Storage Engine.

Basically it all boiled down to adding this line to the my.cnf configuration file:

default_storage_engine = innodb

It was necessary to drop the application schema and recreate it.

Apparently, later versions of MySQL use the InnoDB storage engine by default.