Re: BUG #4586: Supporting of Binary instead Bytea for Primary Keys - Mailing list pgsql-bugs

Hi,

What if I convert to/from byte array with (see the attached java number
utility class):
    NumberUtils.toByteArray(UUID uuid)
    NumberUtils.toUUID(byte[] value)


Miro.


Miroslav Nachev wrote:
> Yes, you are right. With the correct code:
>    public static void main(String[] args) {
>        UUID uuid = UUID.randomUUID();
>        TestTable1 testTable = new TestTable1(uuid);
>        persist(testTable);
>    }
>
> the exception is:
> Hibernate: insert into test_db.public.test_table_1 (description,
> my_id) values (?, ?)
> 2008-12-18 0:58:39 org.hibernate.util.JDBCExceptionReporter logExceptions
> WARNING: SQL Error: 0, SQLState: null
> 2008-12-18 0:58:39 org.hibernate.util.JDBCExceptionReporter logExceptions
> SEVERE: Batch entry 0 insert into test_db.public.test_table_1
> (description, my_id) values (NULL, '<stream of 80 bytes>') was
> aborted.  Call getNextException to see the cause.
> 2008-12-18 0:58:39 org.hibernate.util.JDBCExceptionReporter logExceptions
> WARNING: SQL Error: 0, SQLState: 42804
> 2008-12-18 0:58:39 org.hibernate.util.JDBCExceptionReporter logExceptions
> SEVERE: ERROR: column "my_id" is of type uuid but expression is of
> type bytea
>  Hint: You will need to rewrite or cast the expression.
>  Position: 55
> 2008-12-18 0:58:39
> org.hibernate.event.def.AbstractFlushingEventListener performExecutions
> SEVERE: Could not synchronize database state with session
> org.hibernate.exception.SQLGrammarException: Could not execute JDBC
> batch update
>        at
> org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
>
>        at
> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
>
>        at
> org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
>        at
> org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
>        at
> org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
>        at
> org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
>
>        at
> org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
>
>        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
>        at
> org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
>        at
> org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
>
>        at
> org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:54)
>        at psqluuidtest.Main.persist(Main.java:34)
>        at psqluuidtest.Main.main(Main.java:25)
> Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into
> test_db.public.test_table_1 (description, my_id) values (NULL,
> '<stream of 80 bytes>') was aborted.  Call getNextException to see the
> cause.
>        at
> org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2556)
>
>        at
> org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:395)
>
>        at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1348)
>
>        at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:343)
>
>        at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2693)
>
>        at
> org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
>
>        at
> org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
>        ... 10 more
> javax.persistence.RollbackException: Error while commiting the
> transaction
>        at
> org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:71)
>        at psqluuidtest.Main.persist(Main.java:34)
>        at psqluuidtest.Main.main(Main.java:25)
> Caused by: org.hibernate.exception.SQLGrammarException: Could not
> execute JDBC batch update
>        at
> org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
>
>        at
> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
>
>        at
> org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
>        at
> org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
>        at
> org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
>        at
> org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
>
>        at
> org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
>
>        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
>        at
> org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
>        at
> org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
>
>        at
> org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:54)
>        ... 2 more
> Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into
> test_db.public.test_table_1 (description, my_id) values (NULL,
> '<stream of 80 bytes>') was aborted.  Call getNextException to see the
> cause.
>        at
> org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2556)
>
>        at
> org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:395)
>
>        at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1348)
>
>        at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:343)
>
>        at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2693)
>
>        at
> org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
>
>        at
> org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
>        ... 10 more
> Exception in thread "main" java.lang.IllegalStateException:
> Transaction not active
>        at
> org.hibernate.ejb.TransactionImpl.rollback(TransactionImpl.java:82)
>        at psqluuidtest.Main.persist(Main.java:37)
>        at psqluuidtest.Main.main(Main.java:25)
>
>
>
> Miro.
>
>
> Kris Jurka wrote:
>> Miroslav Nachev wrote:
>>> I try to use it but I have the following exception:
>>>
>>> java.lang.IllegalArgumentException: Unknown entity: java.util.UUID
>>>         at
>>> org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:223)
>>>
>>>         at psqluuidtest.Main.persist(Main.java:33)
>>>         at psqluuidtest.Main.main(Main..java:25)
>>>
>>
>> Surely you want to persist a TestTable1 instance, not the uuid itself.
>>
>> Kris Jurka
>>
>
>

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.cosmos.util;

import java.math.BigInteger;
import java.util.UUID;

/**
 *
 * @author Miro
 */
public class NumberUtils
{
    public static byte[] toByteArray(short value)
    {
        return toByteArray(value, 2);
    }

    public static byte[] toByteArray(int value)
    {
        return toByteArray(value, 4);
    }

    public static byte[] toByteArray(long value)
    {
        return toByteArray(value, 8);
    }

    public static byte[] toByteArray(UUID uuid)
    {
        byte[] firstBytes = toByteArray(uuid.getMostSignificantBits());
        byte[] secondBytes = toByteArray(uuid.getLeastSignificantBits());
        return append(firstBytes, secondBytes);
    }

    private static byte[] toByteArray(long value, int length)
    {
        byte[] result = new byte[length];
        for(int i = 0; i < length; i++)
        {
            result[i] = (byte)(value & 0xFF);
            value >>>= 8;
        }

        return result;
    }

    /**
     * Appends two bytes array into one.
     *
     * @param a A byte[].
     * @param b A byte[].
     * @return A byte[].
     */
    public static byte[] append(byte[] a, byte[] b)
    {
        byte[] z = new byte[a.length + b.length];
        System.arraycopy(a, 0, z, 0, a.length);
        System.arraycopy(b, 0, z, a.length, b.length);
        return z;
    }

    public static short toShort(byte[] value)
    {
        return toShort(value, 0);
    }

    public static short toShort(byte[] value, int beginPos)
    {
        return (short)toNumber(value, beginPos, 2);
    }

    public static int toInt(byte[] value)
    {
        return toInt(value, 0);
    }

    public static int toInt(byte[] value, int beginPos)
    {
        return (int)toNumber(value, beginPos, 4);
    }

    public static long toLong(byte[] value)
    {
        return toLong(value, 0);
    }

    public static long toLong(byte[] value, int beginPos)
    {
        return toNumber(value, beginPos, 8);
    }

    private static long toNumber(byte[] value, int beginPos, int length)
    {
        if(value == null || value.length == 0)
            return 0;

        if(length > 8)
            length = 8;

        if((beginPos + length) > value.length)
            length = value.length - beginPos;

        if(length == 0)
            return 0;

        int endPos = beginPos + length;
        long result = 0;
        for(int i = (endPos - 1); i >= beginPos; i--)
        {
            int b = value[i] & 0xFF;
            result |= b;
            if(i > beginPos)
                result <<= 8;
        }

        return result;
    }

    public static UUID toUUID(byte[] value)
    {
        return toUUID(value, 0);
    }

    public static UUID toUUID(byte[] value, int beginPos)
    {
        if(value.length < beginPos + 16)
            throw new IllegalArgumentException("The length (" + value.length +
                    ") of bytes is less than required (" + beginPos + 16 + ").");

        long mostSigBits = toLong(value, beginPos);
        long leastSigBits = toLong(value, beginPos + 8);
        return new UUID(mostSigBits, leastSigBits);
    }

    public static UUID toUUID(BigInteger intValue)
    {
        return toUUID(intValue.toByteArray());
    }

    public static BigInteger toBigInteger(UUID uuid)
    {
        return new BigInteger(toByteArray(uuid));
    }

    public static void main(String[] args)
    {
        try
        {
            long l = 123456;
            System.out.println("l: " + l);
            byte[] ba = toByteArray(l);
            l = toLong(ba);
            System.out.println("l: " + l);

            l = -123456;
            System.out.println("l: " + l);
            ba = toByteArray(l);
            l = toLong(ba);
            System.out.println("l: " + l);

            int i = 123456;
            System.out.println("i: " + i);
            ba = toByteArray(i);
            i = toInt(ba);
            System.out.println("i: " + i);

            i = -123456;
            System.out.println("i: " + i);
            ba = toByteArray(i);
            i = toInt(ba);
            System.out.println("i: " + i);

            short s = 12345;
            System.out.println("s: " + s);
            ba = toByteArray(s);
            s = toShort(ba);
            System.out.println("s: " + s);

            s = -12345;
            System.out.println("s: " + s);
            ba = toByteArray(s);
            s = toShort(ba);
            System.out.println("s: " + s);

            UUID uuid1 = UUID.randomUUID();
            System.out.println("uuid1: " + uuid1);
            ba = toByteArray(uuid1);
            UUID uuid2 = toUUID(ba);
            System.out.println("uuid2: " + uuid2);
            System.out.println("uuid1.equals(uuid2): " + uuid1.equals(uuid2));

            BigInteger intValue1 = toBigInteger(uuid1);
            uuid2 = toUUID(intValue1);
            System.out.println("uuid1.equals(uuid2): " + uuid1.equals(uuid2));
            BigInteger intValue2 = toBigInteger(uuid2);
            System.out.println("intValue1.equals(intValue2): " + intValue1.equals(intValue2));
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }

}