Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Postgresql and UUID eclipselink 2.4.0 
On Fri, 2012-08-10 at 13:30 +0200, Ancoron Luciferis wrote:
> On 08/10/2012 04:57 AM, Edward Mann wrote:
> > I am not sure what performance penalties if any this will introduce, but
> > this is how i got the uuid to work. I wanted to send this out so anyone
> > else having this issue can try this, and also get feedback on if this is
> > not a good thing to do.
> >
> > I changed all the UUID to String and made the following two changes to
> > the persistent.xml file
> >
> > for postgresql i am able to use ?stringtype=unspecified in the url for
> > the database connection.
> > Also i had to add
> > <property name="eclipselink.jdbc.bind-parameters" value="false" />
> > to the properties section. With both of those things are working as
> > expected. Granted all my tables are small so i might be in for a
> > surprise.
> >
> > Thanks for your input everyone.
> 
> Hi Edward,
> 
> glad to hear you got it working. But when you don't let EclipseLink bind 
> the parameters I think it may affect performance quite a lot, see here:
> 
> http://eclipse.org/eclipselink/documentation/2.4/jpa/extensions/q_bindparameters.htm
> 
> So, I think you're better off with a converter. I've done this for the 
> inet extension of PostgreSQL for different mappings, e.g. 
> java.net.InetAddress:
> 
> https://github.com/ancoron/pg-inet-maven/blob/master/org.ancoron.postgresql.jpa/src/main/java/org/ancoron/postgresql/jpa/eclipselink/InetAddressConverter.java
> 
> However, in case of UUID the PostgreSQL driver already is able to map 
> java.lang.UUID to the internal uuid type for JDBC-3g and JDBC-4 connections:
> 
> class AbstractJdbc3gStatement:
> 
> import java.sql.*;
> import java.util.UUID;
> 
> import org.postgresql.core.Oid;
> ...
> public void setObject(int parameterIndex, Object x) throws SQLException
> {
>      if (x instanceof UUID && connection.haveMinimumServerVersion("8.3"))
>      {
>          setString(parameterIndex, x.toString(), Oid.UUID);
>      } else {
>          super.setObject(parameterIndex, x);
>      }
> }
> 
> So your converter may just pass the java.lang.UUID object in and out - 
> no conversion required at all.
> 
> Cheers,
> 
> 	Ancoron
> 
> >
> > On Tue, 2012-08-07 at 09:16 +0200, Ancoron Luciferis wrote:
> >> Hi Edward,
> >>
> >> I think you need to either:
> >>
> >> 1.) provide a custom converter (UUID (java) <-> UUID (PostgreSQL))
> >>
> >> 2.) provide an custom type mapping
> >>
> >>
> >> ...for 1.) there are enough information out there on the EclipseLink
> >> wiki, for 2.) there is almost no information how to code that, as I
> >> still have the same problem with other types.
> >>
> >> Cheers,
> >>
> >> 	Ancoron
> >>
> >>
> >> On 08/06/2012 07:11 PM, Edward Mann wrote:
> >>> I have been trying to figure out how to get the native uuid type in
> >>>           postgresql and eclipselink to work together. I have a simple
> >>> table for
> >>>           testing:
> >>>
> >>>           CREATE TABLE elink
> >>>           (
> >>>             el_id uuid NOT NULL DEFAULT uuid_generate_v4(), -- elink id
> >>>             el_name character varying(255), -- elink name
> >>>             CONSTRAINT el_pkey PRIMARY KEY (el_id )
> >>>             DEFERRABLE INITIALLY IMMEDIATE
> >>>           )
> >>>           WITH (
> >>>             OIDS=FALSE
> >>>           );
> >>>           ALTER TABLE elink
> >>>           OWNER TO emann;
> >>>           COMMENT ON COLUMN elink.el_id IS 'elink id';
> >>>           COMMENT ON COLUMN elink.el_name IS 'elink name';
> >>>
> >>>
> >>>           And the annotation on the field for id is:
> >>>
> >>>           @Id
> >>>                   @Column(name="el_id")
> >>>                   @UuidGenerator(name="uuid")
> >>>                   @GeneratedValue(generator="uuid")
> >>>                   private UUID elId;
> >>>
> >>>           I have also tried the custom UUIDSequence mentioned on the
> >>> website,
> >>>           however that did not work either. I get the following error
> >>> (this is the
> >>>           UUIDSequence native to 2.4.0):
> >>>
> >>>           Exception [EclipseLink-3002] (Eclipse Persistence Services -
> >>>           2.4.0.v20120608-r11652):
> >>>           org.eclipse.persistence.exceptions.ConversionException
> >>>           Exception Description: The object
> >>>           [66FB2FFB-2735-4A8C-AFDE-19246C78CC16], of class [class
> >>>           java.lang.String], from mapping
> >>>
> >>> [org.eclipse.persistence.mappings.DirectToFieldMapping[elId-->ELINK.el_id]] with descriptor [RelationalDescriptor(model.Elink --> [DatabaseTable(ELINK)])], could not be converted to [class [B].
> >>>
> >>>
> >>>           So i am really curious about what is the proper way to get this
> >>> to work
> >>>           using uuid type in postgresql.
> >>>           Any help is welcome, thanks in advance.
> >>>
> >>> _______________________________________________
> >>> eclipselink-users mailing list
> >>> eclipselink-users@xxxxxxxxxxx
> >>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> >>>
> >>
> >
> >
> 

Thank you for the advice Ancoron, i read over your what you wrote and
also looked at the code you posted a link for. This is what i have
implemented based on what i read and know.

I changed my persistent.xml to remove the stringtype=unspecified and
also the <property name="eclipselink.jdbc.bind-parameters"
value="false" /> i removed as well.

And here is the UUIDConverter class i wrote.

package org.ledger.core;

import java.util.UUID;

import org.eclipse.persistence.internal.helper.DatabaseField;
import org.eclipse.persistence.mappings.DatabaseMapping;
import org.eclipse.persistence.mappings.DirectCollectionMapping;
import org.eclipse.persistence.mappings.converters.Converter;
import org.eclipse.persistence.sessions.Session;

/**
 * Supports mapping of <tt>java.util.UUID</tt> inside JPA entities.
 * 
 * <p>
 * Example usage:
 * <pre>
 * //..
 * import java.util.UUID;
 * import org.ledger.core.UUIDConverter;
 *  //..
 *  
 *  &#064;Entity
 *
&#064;Converter(name="uuidConverter",converterCalss=UUIDConverter.class)
 *  public class Elink implements Serializable {
 *  	 &#064;Convert("uuidConverter")
 * 	 &#064;Column(name="elink_id")
 * 	 private UUID elinkId;
 *   //..
 *   }
 *   </pre>
 *   </p>
 *   
 * @author Edward Mann
 * @see
https://github.com/ancoron/pg-inet-maven/blob/master/org.ancoron.postgresql.jpa/src/main/java/org/ancoron/postgresql/jpa/eclipselink/InetAddressConverter.java
 * @see
http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg07359.html
 */
public class UUIDConverter implements Converter {

    /**
     * 
     */
    private static final long serialVersionUID = -8738182233279166782L;

    @Override
    public Object convertObjectValueToDataValue(Object objectValue,
	    Session session) {
	return objectValue;
    }

    @Override
    public UUID convertDataValueToObjectValue(Object dataValue,
	    Session session) {
		return (UUID)dataValue;
    }

    @Override
    public boolean isMutable() {
	return true;
    }

    @Override
    public void initialize(DatabaseMapping mapping, Session session) {
	final DatabaseField field;
        if(mapping instanceof DirectCollectionMapping) {
            // handle @ElementCollection...
            field = ((DirectCollectionMapping)
mapping).getDirectField();
        } else
            field = mapping.getField();

        field.setSqlType(java.sql.Types.OTHER);
        field.setTypeName("uuid");
        field.setColumnDefinition("UUID");
    }
}


I ran it on my little test table and everything is working as expected.
If you see anything i should update let me know, or anyone else for that
matter.

Thanks again for your input and direction, i really appreciate it.

Back to the top