Hibernate 自定义dialect（SQLServer）

   

参考：

1.http://dingren.blog.51cto.com/4586729/1093242

2.http://tech.ccidnet.com/art/3539/20070717/1146833_1.html

 

     hibernate 在查询一些大数据类型如：varchar(max)时，会报“ No Dialect mapping for JDBC type ”错误，此时需要自定义dialect

1.继承Dialect

其中SQLServerDialect是指SQLServer2000，SQLServer2008Dialect是指SQLServer2008，

SQLServer2005Dialect是指SQLServer2500，SQLServer2005Dialect继承SQLServerDialect，SQLServer2008Dialect继承SQLServer2005Dialect，在2005时，引入了ROW_NUMBER可用于分页，不然hibernate是假分页（先将top数据放到内存后，再截取）

public class MySQLServerDialect extends SQLServer2008Dialect {

 

     public MySQLServerDialect() {

           super();

          registerHibernateType( Types.NVARCHAR, Hibernate.STRING.getName());

          registerHibernateType( Types.DECIMAL, Hibernate.BIG_INTEGER.getName());

          registerHibernateType( Types.LONGVARBINARY, Hibernate.BLOB.getName());

          registerHibernateType( Types.REAL, Hibernate.FLOAT.getName());

          registerHibernateType( Types.DECIMAL, Hibernate.BIG_DECIMAL.getName());

          registerHibernateType( Types.LONGVARCHAR, Hibernate.TEXT.getName());

     }

}

registerHibernateType函数用来注册类型

2.在配置hibernate属性时加入

 <property name="hibernate.dialect" value="xxx.xxx.MySQLServerDialect"/>  

3.分页调整

@Override

      public String getLimitString(String query, int offset, int limit) {

           // StringBuilder(querySqlString.trim().toLowerCase());

          StringBuilder sb = new StringBuilder(query.trim());//取消小写化

           int orderByIndex = sb.indexOf("order by" );

          CharSequence orderby = ((CharSequence) (orderByIndex <= 0 ? "ORDER BY CURRENT_TIMESTAMP"

                   : sb.subSequence(orderByIndex, sb.length())));

           if (orderByIndex > 0)

              sb.delete(orderByIndex, orderByIndex + orderby.length());

//        replaceDistinctWithGroupBy(sb);

           insertRowNumberFunction(sb, orderby);

          sb.insert(0, "WITH query AS (" ).append(") SELECT * FROM query ");

          sb.append( "WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?");

           return sb.toString();

     }