In one of our Grails projects we had to use Microsoft SQL Server as a database. Hibernate has support for it and works good enough, however the schema generated by it is not ideal, at least from our point of view. In particular, properties of boolean type were stored in columns of type INT, while both SQL Server 2000 and 2005 support special type just for booleans — BIT; all Ids, which usually have type long, had to be squeezed into columns of type INT (again this stupid integer!); such SQL types as TEXT and IMAGE were used, instead of (preferred for SQL Server) VARCHAR(max) and VARBINARY(max) respectively; and finally (for me — the most annoying thing) strings were stored in non-Unicode sequences. The very last point is also very unhandy, since strings in Java are stored in Unicode, and having to deal with encodings explicitly when storing or reading strings or entire texts from the database is a bit against the simplicity we expect from Hibernate when we use it.

So I decided to develop customized Hibernate dialect for MS SQL Server. To do this, I started digging the source code of Hibernate (the dialect classes to be precise), and soon I found out that Hibernate’s SQLServerDialect extends from SybaseDialect, which is responsible for most of the “sins” outlined in the paragraph above. Don’t know why they haven’t overridden any of that in a subclass, probably they had a good reason not to do so, but for our company this reasoning was certainly not working out. Just extending the standard implementation was fairly enough to solve the problem, actually it was sufficient just to register appropriate column types in the constructor. The end result looks like this:


public class SQLServerDialect extends org.hibernate.dialect.SQLServerDialect {
 
   /**
    * Initializes a new instance of the {@link SQLServerDialect} class.
    */
    public SQLServerDialect() {
        registerColumnType(Types.BIGINT, "bigint");
        registerColumnType(Types.BIT, "bit");
        registerColumnType(Types.CHAR, "nchar(1)");
        registerColumnType(Types.VARCHAR, 4000, "nvarchar($l)");
        registerColumnType(Types.VARCHAR, "nvarchar(max)");
        registerColumnType(Types.VARBINARY, 4000, "varbinary($1)");
        registerColumnType(Types.VARBINARY, "varbinary(max)");
        registerColumnType(Types.BLOB, "varbinary(max)");
        registerColumnType(Types.CLOB, "nvarchar(max)");
    }
}

Obviously, similar technique can be used with any custom Hibernate dialect, not just for MS SQL. Feel free to use it with any other DB engine, etc. If you find it useful, you can fork our code snippet at Github: http://gist.github.com/225543