No Dialect mapping for JDBC type: -9 with Hibernate 4 and SQL Server 2012

Question

I am using the latest Hibernate 4.2.7.SP1 along with the entity-manager, and validator, etc. I am using he Microsoft SQL Server 2012.

The code I am trying to use is:

StringBuffer sb = new StringBuffer();
sb.append("SELECT vr.account_name as account FROM MY_VIEW_RECEIVABLES vr;");
String sql = sb.toString();
System.out.println("MyInvoiceDAO: getInvoices: SQL=" + sql);
SQLQuery q = this.sessionFactory.getCurrentSession().createSQLQuery(sql);
q.addScalar("account");
q.setResultTransformer(Transformers.aliasToBean(InvoiceDTO.class));
List results = q.list();

FYI: MY_VIEW_RECEIVABLES is a view, and the "account_name" field is a NVARCHAR(120)

The issue I get is:

org.springframework.orm.hibernate4.HibernateSystemException: No Dialect mapping for JDBC type: -9; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: -9
at org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:185)

As my search goes, I get it, the dialect can't determine the data types and so I have to add the Scalar mapping ... however I tried that just by adding:

q.addScalar("account");

but that didn't work.

Several solutions show that I should create a class such as:

public class SQLServerNativeDialect extends SQLServerDialect
{
    public SQLServerNativeDialect()
    {
        super();
        registerColumnType(Types.VARCHAR, "nvarchar($l)");
        registerColumnType(Types.CLOB, "nvarchar(max)");
    }

    public String getTypeName(int code, int length, int precision, int scale) throws HibernateException
    {
        if (code != 2005)
        {
            return super.getTypeName(code, length, precision, scale);
        }
        else
        {
            return "ntext";
        }
    }
}

I then change my hibernate.properties file:

From:  hibernate.dialect=org.hibernate.dialect.SQLServerDialect
  To:  hibernate.dialect=com.myapp.test.utils.SQLServerNativeDialect

And this still isn't working. Any help which refers me to Hibernate.STRING, these constants do not exist.

Any help with this would be much appreciated. Thanks!

Answer 1

This didn't take too much to fix. First, the hibernate.dialect is still:

hibernate.dialect=org.hibernate.dialect.SQLServerDialect

And the ".addScalar" worked, but for Hibernate 4, I had to use the "StandardBasicTypes" class as described below.

    q.addScalar("currency", StandardBasicTypes.STRING);
    q.addScalar("amount", StandardBasicTypes.BIG_DECIMAL);
    q.addScalar("age", StandardBasicTypes.INTEGER);

This was standard to add the parameters:

    q.setParameter("age", age);
    q.setParameter("customerId", customerId);

And finally, I still needed this transformer:

    q.setResultTransformer(Transformers.aliasToBean(MyDTO.class));
    List<MyDTO> results = q.list();

I hope this helps someone else out.