Tag: ORM

JPA and Fixed-length text fields in databases

Lucky me. I took over a system whose database is awash with compound keys. The more I work with this stuff, the more justification I find for always having a simple sequence key as the primary key, despite the apparent extra overhead.

I’ve had a long-running problem where parent-child (one-to-many) relationships weren’t able to fetch their children. At best, the collection came up empty. At worst, accessing the children collection property of the parent would throw a NullPointerException because the “bag” property of the PersistentBagCollection wasn’t initialized.

I spent a LOT of time with Hibernate’s trace-level logging turned on and the only thing I could determine was that the children were, in fact, being correctly loaded and formed into a collection, but the collection wasn’t the collection that was bound to the parent – instead the child collection was being silently lost.

One thing that did look suspicious, however was that the trace showed two collections found. I went round and round with this, added various bits of code that made objects more identifiable in the debugger, and finally – at long last ended up with a set of trace messages showing the owner primary keys of these collections.

Lo, and behold! One of them showed trailing spaces in the key field values, the other did not.

Trailing spaces in ORM fields have much the same effect and being sloppy with upper/lower case in Java filenames under Windows. Sometimes you get away with it, but not always. Because of the trailing spaces, the two primary keys didn’t compare equal, the “real” collection couldn’t be attached to its parent, and data was lost.

I suppose that this problem could be avoided by proper implementation of the equals() and hashCode() methods of the offending primary key objects, but I found it easier to simply force the key objects to be space-padded and to be more careful about what I passed to search functions, since the ultimate problem lies in the fact that sending a space-trimmed value to a Hibernate JPA query will match and return a space-padded object.

The ultimate cure for this would probably be to use fixed-size character arrays instead of String objects, but there are several problems here as well:

  1. Java’s static type checking doesn’t cover mismatches on array size.
  2. Individual characters in a character array can be null
  3. The ORM class-generating tools I use render fixed character fields as Strings, not arrays
  4. I’m not actually sure that either JPA or Hibernate support character array properties. I have enough grief trying to portably represent boolean and enum values.

Using the Apache OpenJPA command-line tools

Stuff that’s unfortunately not concentrated into a convenient ready-to-use example in the Apache OpenJPA docs. But that’s what this blog is all about!

When using the reverse engineering, schema, and other tools directly from a shell script (not Ant or Maven), the default place to get datasource definitions and related options is in META-INF/persistence.xml. This file is mandatory even in cases where you don’t actually connect to the database, such as generating Java source from an XML schema (reverse generation).

Because the tools are using a validating parser, a schema name is REQUIRED. Example, supplying the JPA schema via the xmlns attribute:

<?xml version="1.0"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">
  <persistence-unit name="openjpa">
    <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
    <properties>
      <property name="openjpa.ConnectionURL" value="jdbc:hsqldb:tutorial_database"/>
      <property name="openjpa.ConnectionDriverName" value="org.hsqldb.jdbcDriver"/>
      <property name="openjpa.ConnectionUserName" value="sa"/>
      <property name="openjpa.ConnectionPassword" value=""/>
      <property name="openjpa.Log" value="DefaultLevel=WARN, Tool=INFO"/>
      <property name="openjpa.jdbc.DBDictionary" value="StoreCharsAsNumbers=false"/>
    </properties>
  </persistence-unit>
</persistence>


CAUTION: You should delete the orm.xml file when re-running these tools. Otherwise they will use the old copy, which may not be in sync with your current efforts.

ORM and Imprecise Data Types

JPA is great in a lot of ways. But there’s one problem a lot of people have with JPA and ORM in general – imprecise keys.

Technically, this is as much a JDBC problem as an ORM problem. It just seems to cause more problems in ORM. Perhaps because ORM lets you concentrate less on the raw data details.

Anyone who has taken a basic programming course has (hopefully) had it pointed out to them that it’s dangerous to compare for exact equality on floating-point numbers. Most binary representations of floating point are imprecise on fractions, and even a simple 0.1 has no simple binary value.

Less obvious, however, is that times and dates are also imprecise formats. Technically, not dates, but in the real world, dates and times are often intermingled even when you don’t expect them to be.

There are 3 primary time/date representations commonly found in Java:

java.sql.Date

java.sql.Date

DBMS-specific dates.

I won’t address high-precision time data types. They’re less likely to surprise people.

java.sql.Date is, in theory granular to one day. In practice, it’s a subclass of java.util.date, so that’s not literally true.

java.util.Date is granular to one millisecond. Since java.sql.Date doesn’t enforce granularity, you can get in trouble with java.sql.Dates which have time-of-date day in them. Especially when dealing with Calendar timezone conversions.

Things get even more interesting when these objects are used in ORM and persisted out to a database. Oracle Dates have a granularity of 1 second – there’s no standard Java time class that reflects this. So if you persist out a java Data object, it may – generally will – get silently truncated. Thus your in-memory date and your database dates will not compare equal!

As bad as this is, it’s worse if you try and use that date as a primary or foreign key. You’ll get invalid results, since there’s no such actual value in the database. More insidiously, in an ORM environment, you can make queries without realizing it. That is, if you retrieve an object that’s liked to another object and the actual linkage was a date, the linkage may fail for non-obvious reasons.

There’s no easy fix for this. You can write your own Date class that enforces the granularity of your choice (truncates/rounds to seconds in the case of Oracle), but then you have to configure the data type mapping in your ORM configuration. You can write accessor functions to do the same thing, but if you forget to use one, the program will fail for non-obvious reasons. It’s best to avoid using dates as keys, but this isn’t always an option, and even non-key dates have their perils.

Oracle isn’t the only – or perhaps even the worst – offender. It turns out that PostgreSQL has an even more insidious date problem. By default, the Oracle time data types are floating-point. Which means that they’re imprecise. And if dates are bad as keys, floating-point numbers are a thousand times worse!

There are 2 possible ways to handle that. One is to build a custom copy of the PostgreSQL server using the option of internally storing fractional seconds in interger form. Probably not going to happen, since not only does this mean you have to have permission to run a non-standard server, but also the internal table data won’t be freely interchangable with standard-build tables. This would be an even worse problem than it is, except that PostgreSQL is notorious for changing internal structure even between minor releases.

The other alternative is to define the time value with fractional seconds truncated – for example, as TIMESTAMP(0). It is, unfortunately, not possible to accurately represent millisecond values in a timedate value on a stock PostgreSQL server, so the next best thing is to simply hack them off if you intend to retrieve by time or date.

Detached objects and JSF

JSF and JPA have proven to be more problematic than expected. The upside of the JSF framework is that the datamodel objects can be presented more or less right up to the page view layer without recourse to Data Transer Objects (DTOs). The downside, is that what’s presented isn’t always clear, leading to the dreaded OptimisticLockingException.

Officially, you get an Optimistic Locking Exception when your in-memory model is out of sync with the database (the database is more up-to-date than the model). In actuality, all that really needs to happen is for the ORM manager to think the model is out of sync with the database.

This perception seems to be distressingly easy cause. I’d blame it on a bug in OpenJPA, but I had similar issues with the detach/attach model of JDO. I’ve not seem any good writeups on how to prevent the problem, but I’ve come up with a means of handling it (right or wrong).

In Struts, the issue was more obvious. It may be the same mechanism in JSF, just more obscure.

Here’s what happens:

1. You fetch in a record, display it, get the user’s input back.

2. You merge the update with the database. OB so far.

By default, JSF will redisplay the same form. If you then do more changes and attempt to merge them, you get an OptimisticLockingException.

In theory, this shouldn’t be happening, but in the current and development releases of OpenJPA (up to 1.2.0), I can do only one merge.

As it turns out, the simple solution is to do the update, then fetch a brand-new copy of the object if further edits are required/anticipated. That makes it almost a return to the old-time concept of EJB where a handle could be used to re-activate a copy of the EJB. Only instead of a handle, use the object’s primary key. This should be low-overhead assuming the object’s still in cache, but it is irritating.

As to what’s actually causing the problem, it seems that the “dirty” flags for the object don’t get cleared when the merge is done and the EntityManager merge() method returns the object. So on the next merge, the database is updated, but the original pre-merge criteria were applied.