Sunday, January 08, 2006

Experiments with EJB 3.0 @Id annotation

I have been messing around with @Id annotation in EJB 3.0 for generating primary keys.

Firstly, there appears to be a change in the latest version (Proposed Final Draft) of the EJB 3.0 specification which is not supported in Glassfish yet. The latest version requires a separate annotation called @GeneratedValue to define the type of Id generator. I downloaded Eclipse Dali yesterday, and this seems to support the new method.

The previous syntax supported five generator types - NONE, AUTO, SEQUENCE, IDENTITY and TABLE. In the new syntax, NONE is no longer required, as it is implied if there is no @GeneratedValue annotation. The remaining four generator types are supported.

By default, if no generator type is specified, then NONE is implied. This means that the developer must set the Id property or field correctly before attempting to persist the entity.

I have some trouble getting to terms with SEQUENCE and IDENTITY generators. Not all databases support both of these, so it seems to me that if you annotate your Entity with either of these then you are going to end up with non-portable code. My recommendation is to avoid these.

IDENTITY generator type is meant to be used when the underlying DBMS supports the IDENTITY column type - this is an autoincrement column that is maintained by the DBMS itself. For example, you can create a table in Apache Derby with a column defined as "GENERATED ALWAYS AS IDENTITY". Note that systems that support this also support a mechanism for retrieving the last generated IDENTITY value. This method is not the equivalent of using a trigger to populate the primary key - as is sometimes done in Oracle. With the trigger approach, there is no support for retrieving the last generated IDENTITY value.

The SEQUENCE generator is meant for systems that support Sequences. I know of Oracle and PostgreSQL that support sequences.

The TABLE generator, as its name implies, relies upon a special table for generating sequences. This is a portable method, as it does not rely upon native generators. Here is an example annotation that specifies a table generator:

@TableGenerator(name="IDGEN",
table=@Table(name = "SEQUENCE", schema = "DIBYENDU"),
pkColumnName="SEQ_NAME",
valueColumnName="SEQ_COUNT",
pkColumnValue="ID_SEQUENCE",
allocationSize=10)
Finally, there is the AUTO generator type. This is meant to use the best strategy available in the DBMS. Unfortunately, Glassfish seems to insist on using a table generator, at least when I tested this with Apache Derby. It also assumes that you have created a table called SEQUENCE in the default schema for the JDBC Connection ID - there is no way to specify the schema. The expected table structure is not documented as far as I can see, but you can guess it by looking at the SQL logs. You can also use the DDL generation facility in Glassfish to get the basic DDL for creating this table.

Speaking of DDL generation facility in Glassfish, I think it is not very useful at the moment. The DDL generated looses information - for example, it does not respect any @Column attributes other than name.

No comments: