JPA 2.0 - NVARCHAR in main request

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

I need to select some data from a SQL view. I use my own query for this, but I ran into some problems with 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();


ItemDetailsMapping is declared as:

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


I am getting an exception:

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


The -9 type is actually the NVARCHAR type that we use extensively throughout the application, and it works great when we use non-native queries. Why isn't it working with native queries? I even used a custom dialect and registered the type, but it still doesn't work.

Many thanks for your help


source to share

2 answers

You need to bind the datatype NVARCHAR

to String

. When using Hibernate through the session interface, you can selectively set the result type with addScalar()

(also available via unwrap()

in JPA 2.0):

So change your code like 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 more here .

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



You can do it like this:

String myquery = "select cast( 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( = 'TEST'"; 




All Articles