No dialect mapping for JDBC type: -9 with Hibernate 4 and SQL Server 2012
I am using latest Hibernate 4.2.7.SP1 along with entity manager and validator etc. I am using it Microsoft SQL Server 2012.
The code I'm 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 NVARCHAR (120)
The problem is this:
org.springframework.orm.hibernate4.HibernateSystemException: No dialect mapping for JDBC type: -9; Nested Exception - org.hibernate.MappingException: No dialect mapping for JDBC type: -9
on org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException (SessionFactoryUtils.java:185)
As my search goes I get it, the dialect can't figure out the datatypes, so I need to add a Scalar mapping ... however I tried it by adding:
q.addScalar("account");
but it didn't work.
Several solutions show that I have to create a class, for example:
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";
}
}
}
Then I modify my hibernate.properties file:
From: hibernate.dialect=org.hibernate.dialect.SQLServerDialect
To: hibernate.dialect=com.myapp.test.utils.SQLServerNativeDialect
And it still doesn't work. Any help that refer to Hibernate.STRING these constants don't exist.
Any help with this would be much appreciated. Thank!
source to share
It doesn't take too long to fix. First, hibernate.dialect is still:
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
And ".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 the default for adding parameters:
q.setParameter("age", age);
q.setParameter("customerId", customerId);
And finally, I still need this transformer:
q.setResultTransformer(Transformers.aliasToBean(MyDTO.class));
List<MyDTO> results = q.list();
Hope this helps someone else.
source to share