JPA SQL Server No Dialect mapping for JDBC type: -9

Question

Iam writing a native query like

Query query = 
  entityManagerUtil.getEntityManager().createNativeQuery("SELECT c.NodeID,c.Code,c.Name FROM COM_Location c");    
query.getResultList();

but it is not working for me....

Iam using JPA , MSSQL Server 2008 with Spring.It is working fine when i try to write JPA queries with pojo classes but it is failing to execute native queries.

My configurations in persistance.xml goes like this

<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>

Any one suggest me to fix the issuee

My stack trace says...

org.springframework.orm.jpa.JpaSystemException: org.hibernate.MappingException: No Dialect mapping for JDBC type: -9; nested exception is javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: -9
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:311)
    at org.springframework.orm.jpa.aspectj.JpaExceptionTranslatorAspect.ajc$afterThrowing$org_springframework_orm_jpa_aspectj_JpaExceptionTranslatorAspect$1$18a1ac9(JpaExceptionTranslatorAspect.aj:15)
    at com.iconma.carz.daoimpl.SecurityDaoImpl.getAllLocations(SecurityDaoImpl.java:40)
    at com.iconma.carz.serviceimpl.SecurityServiceImpl.getAllLocations(SecurityServiceImpl.java:31)
    at com.iconma.carz.controllers.SecurityController.getLocations(SecurityController.java:51)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
    at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)
    at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:549)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:83)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:164)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:562)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:395)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:250)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:188)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:166)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:302)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:662)
Caused by: javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: -9
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1214)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1147)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)
    ... 37 more
Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: -9
    at org.hibernate.dialect.TypeNames.get(TypeNames.java:77)
    at org.hibernate.dialect.TypeNames.get(TypeNames.java:100)
    at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:375)
    at org.hibernate.loader.custom.CustomLoader$Metadata.getHibernateType(CustomLoader.java:590)
    at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.performDiscovery(CustomLoader.java:516)
    at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:532)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1962)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2533)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
    at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
    ... 37 more

Solution 1

First you have to define what is your dialect class name that you want to use: in hibernate.cfg.xml add your own class address

<property name="hibernate.dialect">com.nhl.dao.SQlServerDBDialect</property>

then create new class same below

package com.nhl.dao;
import java.sql.Types;
import org.hibernate.dialect.SQLServerDialect;
import org.hibernate.type.StandardBasicTypes;

public class SQlServerDBDialect extends SQLServerDialect {

    public SQlServerDBDialect() {
        super();
        registerHibernateType(Types.NCHAR, StandardBasicTypes.CHARACTER.getName()); 
        registerHibernateType(Types.NCHAR, 1, StandardBasicTypes.CHARACTER.getName());
        registerHibernateType(Types.NCHAR, 255, StandardBasicTypes.STRING.getName());
        registerHibernateType(Types.NVARCHAR, StandardBasicTypes.STRING.getName());
        registerHibernateType(Types.LONGNVARCHAR, StandardBasicTypes.TEXT.getName());
        registerHibernateType(Types.NCLOB, StandardBasicTypes.CLOB.getName());

    }
}

Solution 2

The type -9 is java.sql.Types.NVARCHAR. Looking at the sources of the SQLServerDialect variants on https://github.com/hibernate/hibernate-orm/tree/master/hibernate-core/src/main/java/org/hibernate/dialect there is no mapping for nvarchar columns.

You might want to try to define your own dialect that also registers various NVARCHAR-like definitions:

public class SQLServer2008DialectWithNvarchar extends SQLServer2008Dialect {
    public SQLServer2008DialectWithNvarchar () {
        registerColumnType( Types.NCLOB, "nvarchar(MAX)" );
        registerColumnType( Types.LONGNVARCHAR, "nvarchar(MAX)" );
        registerColumnType( Types.NVARCHAR, "nvarchar(MAX)" );
        registerColumnType( Types.NVARCHAR, 4000, "nvarchar($1)" );
    }
}

I based this on the definition for VARCHAR in the SQLServer2005Dialect. You may need to put this class in the org.hibernate.dialect package (or at least I seem to remember there are issues if you don't).

NOTE: I haven't actually tested this!

Solution 3

I also face the same issue, So i do R&D and got solution as Do explicit cast like cast(t2.name as varchar), It works for me.

cast(t2.name as varchar)

Check this url. http://www.coderanch.com/t/565413/ORM/databases/Dialect-mapping-JDBC-type

Solution 4

I had a similar issue, my sql is this

Query nmspQuery = em.createNativeQuery("select aster from MyTable where my_column = 1001")

The datatype for PRODUCT_NODE_ONIX_TYPE in the Oracle 11g is nvarachar2

I fixed it by casting the value astr to to_char. This worked fine

Query nmspQuery = em.createNativeQuery("select TO_CHAR(aster) from MyTable where my_column = 1001")

Solution 5

You have to extend the dialect, and register the appropriate Hibernate types for the N* JDBC types, in the constructor:

public class MyDialect extends SomeOfTheProvidedDialects {

    public MyDialect() {
        registerHibernateType(Types.NCHAR, StandardBasicTypes.CHARACTER.getName());
        registerHibernateType(Types.NCHAR, 1, StandardBasicTypes.CHARACTER.getName());
        registerHibernateType(Types.NCHAR, 255, StandardBasicTypes.STRING.getName());
        registerHibernateType(Types.NVARCHAR, StandardBasicTypes.STRING.getName());
        registerHibernateType(Types.LONGNVARCHAR, StandardBasicTypes.TEXT.getName());
        registerHibernateType(Types.NCLOB, StandardBasicTypes.CLOB.getName());
    }
}

Tested, works fine.

They should have added these to the Hibernate source code already (in the org.hibernate.dialect.Dialect class)...

View more solutions

Comments

[user1903224]

thanks for your quick response...i tried in this way also by putting this class as a Dialect but it doesn't worked out still

[Mark Rotteveel]

@user1903224 You are sure you are actually using this new dialect?

[user1903224]

yes iam using that class only here is my sample public class SQLServerDialectOverrider extends SQLServer2008Dialect { public SQLServerDialectOverrider() { super(); registerColumnType( Types.NCLOB, "nvarchar(MAX)" ); registerColumnType( Types.LONGNVARCHAR, "nvarchar(MAX)" ); registerColumnType( Types.NVARCHAR, "nvarchar(MAX)" ); registerColumnType( Types.NVARCHAR, 4000, "nvarchar($1)" ); } In my persistance.xml i kept this <property name="hibernate.dialect" value="com.utils.SQLServerDialectOverrider"/>

[Daniel G.]

This is the actual FIX for this issue all over your code without having to cast your query columns data type. By extending the Hibernate dialect to map the apropiate data types will no longer require cast-based workarounds. +1

[Vijender Kumar]

Worked for me too. Really nice solution. @user1903224 This answer should be marked as accepted.

[Andrew Grothe]

In hibernate 3, I already had a custom dialect with registerColumnType but needed the registerHibernateType in order to select nvarchar columns via native query. Great answer.

[Radhesh Khanna]

This is also a Solution but it does not seem to work always. I had even casted my ids as varchar but the issue did not go away until I didn't use @Ebrahim Amini Sharifi Solution