JPA 2.0 - NVARCHAR in native query

Question

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

score 6 · Accepted Answer · edited May 23 '17 at 11:52

6

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)

edited May 23 '17 at 11:52

Community

1
1

answered Mar 16 '12 at 14:17

ManuPK

11,623
10
57
76

Thank you. I'll give a try and see how it goes. – Daniel Platon Mar 19 '12 at 10:26
It seems the method `addScalar` do not exist anymore – pouyan021 Jul 13 '17 at 07:00

score 3 · Answer 2 · edited May 23 '13 at 17:32

You can do it like this:

String myquery = "select cast(t2.name as varchar) column_name from sys.objects t1 inner join sys.columns t2 on t2.object_id = t1.object_id"+ 
" left join sys.indexes t3 on t3.object_id = t1.object_id and t3.is_unique = 1 left join sys.index_columns t4 on t4.object_id = t1.object_id and t4.index_id = t3.index_id and t4.column_id = t2.column_id where (upper(t1.type) = 'U' or upper(t1.type) = 'V') and upper(schema_name(t1.schema_id)) = 'dbo' and upper(t1.name) = 'TEST'";

JPA 2.0 - NVARCHAR in native query

2 Answers2