java 对于 nvarchar 字段，使用 Sql Server 进行休眠失败，并显示“无方言映射...”

Hibernate with Sql Server fail for nvarchar field with "No Dialect mapping..."

提问by slartidan

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

我正在使用 Hibernate 的 JPA-Implementation 来访问我们的 SQL Server 2012 数据库。

When trying to select a nvarcharfield in a native query, I get an exception "No Dialect mapping for JDBC type: -9".

尝试nvarchar在本机查询中选择字段时，出现异常“JDBC 类型无方言映射：-9”。

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

它看起来很像JDBC 类型的 No Dialect mapping: -9 with Hibernate 4 and SQL Server 2012or No Dialect mapping for JDBC type: -9但我在那里找不到适合我的解决方案（两者都没有使用 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

<?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

启用 sql 日志记录后，我会在控制台中获得此输出

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

• 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 varcharinstead of nvarcharmakes 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.jarin 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>

• 使用 avarchar而不是nvarchar使它工作，但我需要nvarchar
• 使用 jpql 而不是 sql 工作（见我的示例代码），但我需要一个本机查询
• 我sqljdbc4.jar在 4.0 和 4.1 版中尝试过sqljdbc41.jar
• 我开始对 SQL Server Dialect 类进行子类化，但没有成功
• 我添加<property name="dialect" value="org.hibernate.dialect.SQLServerDialect" />到我的persistence.xml（就在密码属性后面）
• 我添加<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect" />到我的persistence.xml
• 我将持久性提供程序更改为 <provider>org.hibernate.ejb.HibernatePersistence</provider>

采纳答案by slartidan

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

我能够通过继承 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:

并在 my 中引用它persistence.xml：

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

PS: I found an issue report at https://hibernate.atlassian.net/browse/HHH-9750, describing similar behaviour and solution (for Oracle databases).

PS：我在https://hibernate.atlassian.net/browse/HHH-9750找到了一份问题报告，描述了类似的行为和解决方案（针对 Oracle 数据库）。

回答by art

Using @Nationalizedattribute helped me to map Stringto nvarchar for MS SQL 2012 without dialect subclassing.

使用@Nationalized属性帮助我在String没有方言子类化的情况下映射到 MS SQL 2012 的 nvarchar。

At the same time setting the hibernate.use_nationalized_character_dataproperty to true did not worked for me.

同时将hibernate.use_nationalized_character_data属性设置为 true 对我不起作用。

For futher information watch docs National Character Types.

有关更多信息，请观看文档National Character Types。