Search code examples
javahibernatesql-server-2008-r2jpa-2.0nvarchar

JPA 2.0 - NVARCHAR in native query

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

Solution

  • 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)