SqlServer 2008 with Hibernate 3.6.4

The current stable version if Hibernate is 3.6.4.FINAL. In this version the Dialect for the SqlServer 2008 from Microsoft looks like this:

public class SQLServer2008Dialect extends SQLServerDialect {
  public SQLServer2008Dialect(){
    registerColumnType( Types.DATE, "date" );
    registerColumnType( Types.TIME, "time" );
    registerColumnType( Types.TIMESTAMP, "datetime2" );

    registerFunction( "current_timestamp", new NoArgSQLFunction("current_timestamp", Hibernate.TIMESTAMP,false) );
  }
}

A litle bit short. If you have some problems with this Dialect you should write your own Dialect. This class here is a customized SqlServerDialect from the Hibernate 4 Project. For me it works fine together with SqlServer 2008 R2.

import org.hibernate.Hibernate;
import org.hibernate.cfg.Environment;
import org.hibernate.dialect.SQLServer2005Dialect;
import org.hibernate.dialect.function.*;
import org.hibernate.type.StandardBasicTypes;

import java.sql.Types;

public class SqlServer2008Dialect extends SQLServer2005Dialect {

 public SqlServer2008Dialect(){
 super();

 registerColumnType( Types.DATE, "date" );
 registerColumnType( Types.TIME, "time" );
 registerColumnType( Types.TIMESTAMP, "datetime2" );

 registerFunction( "current_timestamp", new NoArgSQLFunction("current_timestamp", Hibernate.TIMESTAMP,false) );

 registerColumnType( Types.BIT, "tinyint" ); //Sybase BIT type does not support null values
 registerColumnType( Types.BIGINT, "bigint" );     //changed
 registerColumnType( Types.SMALLINT, "smallint" );
 registerColumnType( Types.TINYINT, "tinyint" );
 registerColumnType( Types.INTEGER, "int" );
 registerColumnType( Types.CHAR, "char(1)" );
 registerColumnType( Types.VARCHAR, "varchar($l)" );
 registerColumnType( Types.FLOAT, "float" );
 registerColumnType( Types.DOUBLE, "double precision" );

 registerColumnType( Types.VARBINARY, "varbinary($l)" );
 registerColumnType( Types.NUMERIC, "numeric($p,$s)" );
 registerColumnType( Types.BLOB, "image" );
 registerColumnType( Types.CLOB, "text" );
 registerColumnType( Types.ROWID, "bigint");

 registerFunction( "ascii", new StandardSQLFunction("ascii", StandardBasicTypes.INTEGER) );
 registerFunction( "char", new StandardSQLFunction("char", StandardBasicTypes.CHARACTER) );
 registerFunction( "len", new StandardSQLFunction("len", StandardBasicTypes.LONG) );
 registerFunction( "lower", new StandardSQLFunction("lower") );
 registerFunction( "upper", new StandardSQLFunction("upper") );
 registerFunction( "str", new StandardSQLFunction("str", StandardBasicTypes.STRING) );
 registerFunction( "ltrim", new StandardSQLFunction("ltrim") );
 registerFunction( "rtrim", new StandardSQLFunction("rtrim") );
 registerFunction( "reverse", new StandardSQLFunction("reverse") );
 registerFunction( "space", new StandardSQLFunction("space", StandardBasicTypes.STRING) );

 registerFunction( "user", new NoArgSQLFunction("user", StandardBasicTypes.STRING) );

 registerFunction( "current_timestamp", new NoArgSQLFunction("getdate", StandardBasicTypes.TIMESTAMP) );
 registerFunction( "current_time", new NoArgSQLFunction("getdate", StandardBasicTypes.TIME) );
 registerFunction( "current_date", new NoArgSQLFunction("getdate", StandardBasicTypes.DATE) );

 registerFunction( "getdate", new NoArgSQLFunction("getdate", StandardBasicTypes.TIMESTAMP) );
 registerFunction( "getutcdate", new NoArgSQLFunction("getutcdate", StandardBasicTypes.TIMESTAMP) );
 registerFunction( "day", new StandardSQLFunction("day", StandardBasicTypes.INTEGER) );
 registerFunction( "month", new StandardSQLFunction("month", StandardBasicTypes.INTEGER) );
 registerFunction( "year", new StandardSQLFunction("year", StandardBasicTypes.INTEGER) );
 registerFunction( "datename", new StandardSQLFunction("datename", StandardBasicTypes.STRING) );

 registerFunction( "abs", new StandardSQLFunction("abs") );
 registerFunction( "sign", new StandardSQLFunction("sign", StandardBasicTypes.INTEGER) );

 registerFunction( "acos", new StandardSQLFunction("acos", StandardBasicTypes.DOUBLE) );
 registerFunction( "asin", new StandardSQLFunction("asin", StandardBasicTypes.DOUBLE) );
 registerFunction( "atan", new StandardSQLFunction("atan", StandardBasicTypes.DOUBLE) );
 registerFunction( "cos", new StandardSQLFunction("cos", StandardBasicTypes.DOUBLE) );
 registerFunction( "cot", new StandardSQLFunction("cot", StandardBasicTypes.DOUBLE) );
 registerFunction( "exp", new StandardSQLFunction("exp", StandardBasicTypes.DOUBLE) );
 registerFunction( "log", new StandardSQLFunction( "log", StandardBasicTypes.DOUBLE) );
 registerFunction( "log10", new StandardSQLFunction("log10", StandardBasicTypes.DOUBLE) );
 registerFunction( "sin", new StandardSQLFunction("sin", StandardBasicTypes.DOUBLE) );
 registerFunction( "sqrt", new StandardSQLFunction("sqrt", StandardBasicTypes.DOUBLE) );
 registerFunction( "tan", new StandardSQLFunction("tan", StandardBasicTypes.DOUBLE) );
 registerFunction( "pi", new NoArgSQLFunction("pi", StandardBasicTypes.DOUBLE) );
 registerFunction( "square", new StandardSQLFunction("square") );
 registerFunction( "rand", new StandardSQLFunction("rand", StandardBasicTypes.FLOAT) );

 registerFunction("radians", new StandardSQLFunction("radians", StandardBasicTypes.DOUBLE) );
 registerFunction("degrees", new StandardSQLFunction("degrees", StandardBasicTypes.DOUBLE) );

 registerFunction( "round", new StandardSQLFunction("round") );
 registerFunction( "ceiling", new StandardSQLFunction("ceiling") );
 registerFunction( "floor", new StandardSQLFunction("floor") );

 registerFunction( "isnull", new StandardSQLFunction("isnull") );

 registerFunction( "concat", new VarArgsSQLFunction( StandardBasicTypes.STRING, "(","+",")" ) );

 registerFunction( "length", new StandardSQLFunction( "len", StandardBasicTypes.INTEGER ) );
 registerFunction( "trim", new SQLFunctionTemplate( StandardBasicTypes.STRING, "ltrim(rtrim(?1))") );
 registerFunction( "locate", new CharIndexFunction() );

 getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, NO_BATCH);
 }

}
 

You can just copy & pate it to your own project. If you want to use it, just add the dialect to your hibernate.properties.

hibernate.dialect=org.<your_package_name>.SqlServer2008Dialect

5 thoughts on “SqlServer 2008 with Hibernate 3.6.4

  1. Platon George says:

    hy, what about the connection url for sql server 2008 r2 . how should it looks like? thanks in advance

  2. Charles Ouimet says:

    Hello,

    I’m looking for a dialect for SQL Server 2012: I need support for “sequences” (one of MS SQL 2012 new features).

    Right now, my best option seemed to use SQLServer2008Dialect.

    However, as one might guess, org.hibernate.dialect.SQLServer2008Dialect does not support sequences.

    Are you aware somebody already looked into this ?

    Thanks

    -C

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s