Java Long to MSSQL BIGINT

datageek · **Posted:** Thu Mar 15, 2007 6:06 pm

I am using Java Long data type for the id field for all my java classes. I intend to map it to MS SQL Server BIGINT data type. But Hibernate maps it to Numeric(19,0). I am a bit concerned of the performance on the primary key column of Numeric(19,0) as opposed to BIGINT.

Could someone comment on Numeric(19,0) v.s. BIGINT?

Is it possible for me to modify the Hibernate source code to map Long to BIGINT?
Thank you!

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:3.2

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:MS Sql Server 2005

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html

designpattern · **Posted:** Tue Feb 26, 2008 1:12 pm

I ran into the same problem and tracked it down in Hibernate 3.2.5.GA. Here I want to share the straightforward solution with you.

From my understanding, this is a historical issue from the days where MS SQL Server did not understand SQL BIGINTS. SQL Server 2000 and later do!

The problem is in the SQLServerDialect class (inside the Hibernate jar) where the SQL Server Dialect is an extension of the Sybase Dialect, which in turn defines BIGINT as follows:

Code:

registerColumnType( Types.BIGINT, "numeric(19,0)" );

So there is no doubt, that a numeric column will be created for every Long value you use in your persisten classes (Long will be mapped to bigint, bigint for SQL Server or Sybase becomes numeric).

To correct this I implemented a custom Dialect that fixes it:

Code:

package it.infocube.draft.persistence;

import java.sql.Types;

import org.hibernate.dialect.SQLServerDialect;

/**
 * This class implements our custom Hibernate Dialect that fixes the mapping of
 * Hibernate Type BIGINT (default is NUMERIC(19,0)) to a SQL BIGINT
 * 
 * @author Dominik Enkelmann, 25/february/2008
 */
public class InfocubeMSSQLDialect extends SQLServerDialect {
   
   public InfocubeMSSQLDialect() {
      super();
      registerColumnType( Types.BIGINT, "bigint" );   // Overwrite SQL Server datatype BIGINT
   }
}

In your hibernate.cfg.xml file you'll have to use the new Dialect, in my case

Code:

        <!property name="hibernate.dialect">it.infocube.draft.persistence.InfocubeMSSQLDialect</property>

The same approach could be taken for the BIT type (add a line like registerColumnType( Types.BIT, "bit" );

I will submit this also as a bug since I belive that pre-SQL Server 2000 compatibility should be handled differently.[/code][/i]

michael112 · **Joined:** Sun Jan 20, 2008 10:26 pm **Posts:** 4

designpattern wrote:

I ran into the same problem and tracked it down in Hibernate 3.2.5.GA. Here I want to share the straightforward solution with you.

Thanks for the idea, it worked like a charm, including FK fields in related tables.

designpattern · **Posted:** Thu Mar 27, 2008 12:27 pm

There is one point to be considered if you have MS Access users running ad hoc queries on your schema: Access up to version 2007 does not upport bigints. (sureley not a hibernate-related problem)