JPA 2.0

JPA 2.0 - 本机查询中的NVARCHAR(JPA 2.0 - NVARCHAR in native query)

我正在进行的项目有以下设置：JPA 2.0（Hibernate 4实现）和SQL Server 2008 R2。

我需要从SQL视图中选择一些数据。 为了做到这一点，我使用本机查询，但我遇到了NVARCHAR字段的一些问题。 基本上，当使用这段代码时：

String sql = "SELECT v.text_field as address FROM SOME_CUSTOM_VIEW v Query q = entityManager.createNativeQuery(sql,"ItemDetailsMapping"); List<Object[]> result = q.getResultList();

ItemDetailsMapping声明为：

@SqlResultSetMapping(name = "ItemDetailsMapping", columns = { @ColumnResult(name = "address") })

我得到一个例外说：

org.springframework.orm.hibernate3.HibernateSystemException: No Dialect mapping for JDBC type: -9; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: -9

Type -9实际上是NVARCHAR类型，我们在整个应用程序中广泛使用它，当我们使用非本机查询时它非常有效。 为什么它不能使用本机查询？ 我甚至使用了一种自定义方言并注册了该类型，但它仍然无效。

非常感谢你的帮助

The project that I'm working on has the following setup: JPA 2.0 (Hibernate 4 implementation) and SQL Server 2008 R2.

I need to select some data from an SQL view. In order to do this I use a native query, but I ran into some problems with the NVARCHAR fields. Basically, when using this piece of code:

String sql = "SELECT v.text_field as address FROM SOME_CUSTOM_VIEW v Query q = entityManager.createNativeQuery(sql,"ItemDetailsMapping"); List<Object[]> result = q.getResultList();

The ItemDetailsMapping is declared like:

@SqlResultSetMapping(name = "ItemDetailsMapping", columns = { @ColumnResult(name = "address") })

I get an exception saying:

org.springframework.orm.hibernate3.HibernateSystemException: No Dialect mapping for JDBC type: -9; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: -9

Type -9 is actually the NVARCHAR type, which we are extensively using throughout the application and it works perfectly when we are using non-native queries. Why is it not working with native queries? I even used a custom dialect and registered the type, but it's still not working.

Thanks a lot for your help

最满意答案

您必须将数据类型NVARCHAR与String关联。当通过Session接口使用Hibernate时，您可以使用addScalar()来代替设置结果类型（也可以通过JPA 2.0中的addScalar()访问）：

所以修改你的代码如下，

String sql = "SELECT v.text_field as address FROM SOME_CUSTOM_VIEW v" Query q = entityManager.createNativeQuery(sql,"ItemDetailsMapping"); q.unwrap(SQLQuery.class).addScalar("address ", StringType.INSTANCE); List<Object[]> result = q.getResultList();

请阅读此处了解更多信息。

（编辑7/1/15 - 为了清晰起见添加了引号）

You have to associate the data type NVARCHAR to String.When using Hibernate via Session interface, you can explcitly set a type of result with addScalar() instead (also accessible via unwrap() in JPA 2.0):

So modify your code as below,

String sql = "SELECT v.text_field as address FROM SOME_CUSTOM_VIEW v" Query q = entityManager.createNativeQuery(sql,"ItemDetailsMapping"); q.unwrap(SQLQuery.class).addScalar("address ", StringType.INSTANCE); List<Object[]> result = q.getResultList();

Read here for more information.

(Edit 7/1/15 -- Added quotation mark for clarity)