Announcement Announcement Module
Collapse
No announcement yet.
JPA with PostgreSQL and UUID (Hibernate) Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JPA with PostgreSQL and UUID (Hibernate)

    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:
    Code:
    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:
    Code:
    public class PostgreSQLDialectUuid extends PostgreSQLDialect {
    	
    	public PostgreSQLDialectUuid() {
    		super();
    		registerColumnType(Types.OTHER, "uuid");
    	}
    
    }
    ExampleEntity:
    Code:
    @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:
    Code:
    	<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
    Last edited by map; May 7th, 2009, 07:34 AM. Reason: added Sources (as attachment)

  • #2
    thanks

    Even I can do it.

    Comment


    • #3
      Thanks

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

      Comment


      • #4
        Strange but here it doesn't work as expected…

        I had to make 2 changes in the Java code:

        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

        Code:
        	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…
        Last edited by JeitEmgie; Jul 7th, 2010, 04:22 AM.

        Comment


        • #5
          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:

          Code:
              [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:

          Code:
          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:

          Code:
          @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:

          Code:
          insert into "Knx"."KnxOperation" (name)
            values ('36db183f-b1a1-4237-aea4-bc95b5a3bfd8');
          Any hints?

          Comment

          Working...
          X