We are struggling with the same issue when integrating with an 10+ yr old Sybase db using numeric for identity columns.

Tried making changes to the IdentifierGeneratorFactory and extended the two if..else-blocks to support BigDecimal (which is the native mapping type for numeric) with success. But - obviously we would like this issue handled correctly, and are anxiously waiting for someone in the core team to comment on this issue.

To break the original issue down a bit for clarity:

• Sybase requires that all auto-increment identity columns be of type numeric:
  e.g. create table Foo ( foo_id numeric(9,0) identity )

• Sybase's JConnect drvier maps this to BigInteger (well BigDecimal really) in Java code [*] see below

• Hibernate won't allow BigInteger as a primary key column for an object, due, I think, to code in IdentityGeneratorFactory which checks explicitly

Result - we're caught between the proverbial rock and hard place.

So to partially get around this, we do this:

<id name="fooNum" column="foo_id" type="com.example.hibernate.usertype.NumericType">
<generator class="identity" />
</id>

And NumericType is then a bit ghastly....

public class NumericType extends LongType {
public void set(PreparedStatement st, Object value, int index) throws HibernateException, SQLException { st.setBigDecimal( index, new BigDecimal( BigInteger.valueOf(((Long)value).longValue()))); }

public int sqlType() { return Types.NUMERIC; }
}

Basically the field in our class is then a Long and we fake out the mapping from Long to Numeric here.

There's a side issue where every time we want to use HQL or criteria queries, we have to be very careful to always remember to call Query.setBigDecimal() and convert the Long to an BigInteger manually. If we don't do that (i.e. we call setLong or let Hibernate figure it out) then the Sybase JConnect driver emits SQL code which causes the database to ignore the index (because the types don't match) and we table scan the table.

What we want to do is to change our class to use BigInteger as the id column, because that's really what is needed with Sybase, but the checks in Hibernate won't allow that.

I tried extending IdentityGenerator to get around all this, and then came up against HBX-910.

This issue is still open for both 3.2 and 3.3 branches.

We patched it by simply adding BigDecimal as an allowed type:

public static Serializable get(ResultSet rs, Type type) throws SQLException, IdentifierGenerationException {
Class clazz = type.getReturnedClass();
if ( clazz == Long.class ) { return new Long( rs.getLong( 1 ) ); }
else if ( clazz == Integer.class ) { return new Integer( rs.getInt( 1 ) ); }
else if ( clazz == Short.class ) { return new Short( rs.getShort( 1 ) ); }
else if ( clazz == String.class ) { return rs.getString( 1 ); }
else if ( clazz == java.math.BigDecimal.class ) { return rs.getBigDecimal( 1 ); }
else { throw new IdentifierGenerationException( "this id generator generates long, integer, short, bigdecimal or string" ); }

}

and it works, we see no side effects. It would be nice if this could find it's way into the trunk.

I'd like to add BigInteger as an allowed type:

...
else if ( clazz == java.math.BigDecimal.class ) { return rs.getBigDecimal( 1 ); }
else if (clazz == BigInteger.class) { return rs.getBigDecimal(1).toBigIntegerExact(); }
else { throw new IdentifierGenerationException( "this id generator generates long, integer, short, biginteger, bigdecimal or string" ); }

Likewise, this is working for us...

Anyone know how to use IdentifierGenerator is an extension point as suggested here?:

http://opensource.atlassian.com/projects/hibernate/browse/HHH-3125

Would you please consider removing Sybase 12.05 from the Environment field?

We are using MySQL 5 and just switched to Hibernate JPA from Toplink JPA.

We are affected by this bug because most of our auto-generated Ids are of type BigInteger.

We couldn't find a way to change that field.

Leave the field a Long and use the @Type hibernate annotation like this:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Type(type = "com.foo.util.SybaseIdentityType")
@Column(name = "iIdCon")
private Long id;

Here is the src for com.foo.util.SybaseIdentityType:

package com.foo.util;

import java.math.*;
import java.sql.*;

import org.hibernate.HibernateException;
import org.hibernate.type.LongType;

public class SybaseIdentityType extends LongType {

private static final long serialVersionUID = 1L;

public void set(PreparedStatement st, Object value, int index)
throws HibernateException, SQLException { st.setBigDecimal(index, new BigDecimal(BigInteger .valueOf(((Long) value).longValue()))); }

public int sqlType() { return Types.NUMERIC; }
}

File replaced in the current GA release and it works for me.

The proposed file IdentifierGeneratorFactory.java (# 14444 ) would not work for very high value because of this code snippet:

else if ( clazz==BigDecimal.class ) { return new BigDecimal( rs.getLong(1) ); }

If the database table primary key is a DECIMAL(21) or longer, (silent) overflow will occur since the max value is 10^21-1 for DECIMAL(21) and Long has a max value of 2^63-1, which is lower than 10^21-1.
You would use rs.getBigDecimal(1) instead of new BigDecimal( rs.getLong(1) )

The same would probably occur for the following code snippet:

else if ( clazz==BigInteger.class ) { return new BigInteger( "" + rs.getInt(1) ); }

Maybe nobody noticed it yet but this issue is nearly 3 years old !

It needs to be tackled down in an elegant and quick way for it appears in a very common use case : when using DBMS native ID systems
E.g: using Oracle, one creates a table whose ID is a value generated from a sequence -> the bug occurs
E.g: using Sybase, one creates a table whose ID is an auto-incremented value -> the bug occurs
E.g: using SQL Server, one creates a table whose ID is an auto-incremented value -> the bug occurs
...
the complete list is quite long.

I think 3 years for this simple thing is quite long... don't you think ?

Instead of adding the "not so JDBC standard compliant" implementation in the main IdentifierGeneratorFactory ( which is bad and I understand it), there should at least be an optional not fully compliant (but USEFUL) implementation providing the following seamless mappings:
Oracle's NUMBER( p, 0 ) -> BigInteger
Sybase-SqlServer's auto-increment -> BigInteger

Thanks by advance,

Michel Nolard