JPA with PostgreSQL and UUID (Hibernate)

[map]

Hi,
unfortunately, I have not found any solution to use PostgreSQL with it's native type uuid (which should be mapped to java.util.UUID). That is why I am going to describe my solution (could probably be mapped to EclipseLink,too). Hopefully, this will help somebody.

UserType:

public class UuidUserType implements UserType {

	@SuppressWarnings( "unchecked" )
	public Class returnedClass() {
		return UUID.class;
	}

	public int[] sqlTypes() {
		return new int[] { Types.OTHER };
	}
	
	public boolean equals( Object x, Object y ) throws HibernateException {
		return (x == y) || (x != null && y != null && (x.equals(y)));
	}
	
	public Object nullSafeGet( ResultSet rs, String[] names, Object owner ) throws HibernateException, SQLException {
		return rs.getObject(names[0]);
	}
	
	public void nullSafeSet( PreparedStatement st, Object value, int index ) throws HibernateException, SQLException {
		if ( value == null ) {
			st.setNull( index, Types.OTHER );
			return;
		} else {
			st.setObject(index, value);
		}
	}

	public Object assemble( Serializable cached, Object owner ) throws HibernateException {
		return deepCopy(cached);
	}

	public Serializable disassemble( Object value ) throws HibernateException {
		return (UUID) deepCopy(value);
	}

	// UUID is immutable, so we do not copy it actually
	public Object deepCopy( Object value ) throws HibernateException {
		return (UUID) value;
	}

	public int hashCode( Object x ) throws HibernateException {
		return x.hashCode();
	}

	public boolean isMutable() {
		return false;
	}

	public Object replace( Object original, Object target, Object owner ) throws HibernateException {
		return original;
	}

}

Dialect:

public class PostgreSQLDialectUuid extends PostgreSQLDialect {
	
	public PostgreSQLDialectUuid() {
		super();
		registerColumnType(Types.OTHER, "uuid");
	}

}

ExampleEntity:

@Entity
@TypeDef( name="UUIDUserType", typeClass = UuidUserType.class)
@NamedQueries({
	@NamedQuery(name="deleteUuidEntities", query="delete from UuidEntity u"),
	@NamedQuery(name="selectUuidEntities", query="select u from UuidEntity u")
})
public class UuidEntity {
	@Id
	@Type(type="UUIDUserType")
	private UUID id = UUID.randomUUID();

	public UUID getId() {
		return id;
	}
	
	public void setId(UUID id) {
		this.id = id;
	}
	
}

When using Spring, your configuration could look like:

	<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
		p:dataSource-ref="dataSource" 
		p:persistenceUnitName="test">
  	<property name="jpaVendorAdapter">
	    <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
	        <property name="showSql" value="true" />
	        <property name="databasePlatform" value="PostgreSQLDialectUuid" />
	    </bean>
    </property>

	</bean>

Sources (including testcases) are attached

Kind regards
Matthias

[rjs]

Even I can do it.

[marius.barbulescu]

Thanks for solution.
I'm working with Postgres 8.4 and it works with both uuid type column and character varying(36) type.

[JeitEmgie]

Strange but here it doesn't work as expected…

I had to make 2 changes in the Java code:

public Object nullSafeGet( ResultSet resultSet, String[] names, Object owner ) throws HibernateException, SQLException {
        Object o = resultSet.getString(names[0]) ;
        return resultSet.wasNull() ? null: UUID.fromString(o.toString()) ;
}

and

	public void nullSafeSet( PreparedStatement st, Object value, int index ) throws HibernateException, SQLException {
		if ( value == null ) {
			st.setNull( index, Types.OTHER );
			return;
		} else {
			st.setObject(index, value, Types.OTHER);
		}
	}

due to the nature of the changes I really wonder how it could work in your configuration…
without the first change, I get an error regarding the setting of the UUID field by reflection… quite understandable since your original version returns a PGobject…
without the second change, I get an error regarding the impossibility to determine the SQL type to be used…

and in applicationContext.xml, I had to give the full package name for PostgreSQLDialectUuid…

[gonzus]

Any updates on this? I am working with Spring Data 1.0.0.M1, Postgres 9.0 and postgresql-8.4-702.jdbc4.jar. I thought I would be able to map a java.util.UUID field in my entity directly into a Postgres UUID column, but it doesn't work; every time I try to insert a row in that table, I get the following in my log:

    [junit] Hibernate: insert into "Knx"."KnxOperation" (name) values (?)
    [junit] 2625 main WARN JDBCExceptionReporter: SQL Error: 0, SQLState: 42804
    [junit] 2625 main ERROR JDBCExceptionReporter: ERROR: column "name"
            is of type uuid but expression is of type bytea
    [junit]   Hint: You will need to rewrite or cast the expression.
    [junit]   Position: 49

This table is declared like this:

CREATE TABLE "Knx"."KnxOperation"
(
  id integer NOT NULL DEFAULT nextval('"Knx"."KnxOperation_id_seq"'::regclass),
  "name" uuid NOT NULL,
  CONSTRAINT "KnxOperation_PK_id" PRIMARY KEY (id),
  CONSTRAINT "KnxOperation_UK_name" UNIQUE (name)
)

And the entity is declared like this:

@Entity
@Table(schema="`Knx`", name = "`KnxOperation`")
public class KnxOperation
{
    public KnxOperation() {}

    public Long getId() { return this.id; }
    public void setId(Long id) { this.id = id; }

    public UUID getName() { return this.name; }
    public void setName(UUID name) { this.name = name; }


    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(columnDefinition = "uuid")
    private UUID name;
}

I have tried the following values in that @Column definition and always get the same error message:

1. uuid
2. uuid-binary
3. pg-uuid

Finally, this works fine on an SQL command window, creating a new row on the table:

insert into "Knx"."KnxOperation" (name)
  values ('36db183f-b1a1-4237-aea4-bc95b5a3bfd8');

Any hints?