Hibernate native sql query

Question

Perfectly fine ms-sql statement

SELECT distinct ProductLineCode ,
(SELECT CAST(ItemName + ', ' AS VARCHAR(MAX)) FROM Product spt 
where spt.ProductLineCode = pt.ProductLineCode FOR XML PATH ('')) 
as ItemNames FROM Product pt where ProductLineCode is not null

cause error when executed as native query in hibernate.

org.hibernate.MappingException: No Dialect mapping for JDBC type: -16

I guess that JDBC type: -16 is boolean but that says me nothing. Product table has xml mapping and works well for months. Could you please guide me what to try next?

Lokks like it can'rt add proper Java type for some DB type. (may be the VARCHAR( MAX)). Try to play with result types of the query's columns — StanislavL
– StanislavL, Commented May 26, 2014 at 13:20
Removing AS VARCHAR(MAX) cause org.hibernate.util.JDBCExceptionReporter -17190 [main] - SQL Error: org.hibernate.util.JDBCExceptionReporter -17191 [main] - Incorrect syntax near 'CAST', expected 'AS'. — AlexeiP
– AlexeiP, Commented May 26, 2014 at 13:32

Don Roby · Accepted Answer · 2014-05-26 14:06:12Z

2

You might as advised in comments be able to get something similar to work by changing the types in the sql.

But if that doesn't bring success, you might also be able to tell hibernate how to deal with this type by changing the Dialect class used by your application, possibly even extending the one currently used and adding a registration for the missing type.

It appears that your missing type mapping is for the type java.sql.Types.LONGNVARCHAR (by code grep, the value is -16), so a Dialect extension with a call something like

registerColumnType( Types.LONGNVARCHAR, "text" );

in the constructor may convince hibernate to treat this field as text.

If you do this you'll have to change the configuration to use your dialect by modifying the line

<property name="dialect">org.hibernate.dialect.SQLServer2012Dialect</property>

to use your own dialect class. (Note: the class in there is just a guess at what you might have there now.)

It's also possible that you just have hibernate using the wrong dialect for your DB, in which case just changing the configuration to the appropriate one would be better.

It's rarely necessary to use a custom Dialect, but this may be one of those times.

answered May 26, 2014 at 14:06

Don Roby

41.2k6 gold badges96 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

AlexeiP Over a year ago

I can't find any alternative for ms SQL server dialect <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>

AlexeiP Over a year ago

Thanks Don. Where exactly I may register Column type?

Don Roby Over a year ago

Which dialects are available will depend on your version of hibernate, and which is appropriate depends of course on the version of your DB.

Don Roby Over a year ago

Just add the registerColumnType call to your dialog extension's constructor.

Don Roby Over a year ago

I've just noticed this related question which may also be useful.

|

Collectives™ on Stack Overflow

Hibernate native sql query

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related