Hibernate with Sql Server fail for nvarchar field with “No Dialect mapping…”

I’m using Hibernate’s JPA-Implementation to access our SQL Server 2012 database.

When trying to select a nvarchar field in a native query, I get an exception “No Dialect mapping for JDBC type: -9”.

It looks much like No Dialect mapping for JDBC type: -9 with Hibernate 4 and SQL Server 2012 or No Dialect mapping for JDBC type: -9 but I couldn’t find a solution for me there (both are not using JPA).

My database setup:

CREATE TABLE NvarcharExample(
    exampleField nvarchar(20) PRIMARY KEY
)

INSERT INTO NvarcharExample(exampleField) VALUES ('hello')

My code:

import java.io.IOException;
import javax.persistence.*;

@Entity
class NvarcharExample {

    @Id
    public String exampleField;
}

public class NvarcharTest {

    public static void main(String[] args) throws IOException, InterruptedException {

        String queryString = "SELECT e.exampleField FROM NvarcharExample e";

        // establish connection
        EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("persistenceUnit");
        try {
            EntityManager entityManager = entityManagerFactory.createEntityManager();

            // access data using JPQL
            entityManager.createQuery(queryString).getResultList(); // works

            // access data using SQL (native query)
            entityManager.createNativeQuery(queryString).getResultList(); // fails
        } finally {
            entityManagerFactory.close();
        }
    }
}

My persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
    xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

    <persistence-unit name="persistenceUnit">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <properties>

            <!-- database connection settings -->
            <property name="javax.persistence.jdbc.driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:sqlserver://<servername>:<port>;databaseName=<databasename>" />
            <property name="javax.persistence.jdbc.user" value="<user>" />
            <property name="javax.persistence.jdbc.password" value="<password>" />
        </properties>
    </persistence-unit>
</persistence>

With sql logging enable, I get this output in my console

select nvarcharex0_.exampleField as col_0_0_ from NvarcharExample nvarcharex0_
SELECT e.exampleField FROM NvarcharExample e

I’m using

hibernate-core-4.3.10.Final.jar
hibernate-entitymanager-4.3.10.Final.jar
hibernate-jpa-2.1-api-1.0.0.Final.jar
hibernate-commons-annotations-4.0.5.Final.jar
sqljdbc41.jar

What I’ve tried:

using a varchar instead of nvarchar makes it work, but I need nvarchar
using jpql instead of sql works (see my example code), but I need a native query
I tried sqljdbc4.jar in Version 4.0 and 4.1 and I tried sqljdbc41.jar
I head about subclassing the SQL Server Dialect class, but did not have any success with that
I added <property name="dialect" value="org.hibernate.dialect.SQLServerDialect" /> to my persistence.xml (right behind the password property)
I added <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect" /> to my persistence.xml
I changed the persistence provider to <provider>org.hibernate.ejb.HibernatePersistence</provider>

Answer

I was able to resolve that issue by subclassing the SQLServerDialect:

package packagename;

import java.sql.Types;

public class SqlServerDialectWithNvarchar extends org.hibernate.dialect.SQLServerDialect {

    public SqlServerDialectWithNvarchar() {
        registerHibernateType(Types.NVARCHAR, 4000, "string");
    }

}

and referencing it in my persistence.xml:

        <property name="hibernate.dialect" value="packagename.SqlServerDialectWithNvarchar" />

PS: It seems to be fixed with hibernate 5.1 according to this ticket: https://hibernate.atlassian.net/browse/HHH-10183

Advertisement

Answer