Hibernate with Spring JPA along with Spring Boot makes it easy for user to manage their database connection. However there are certain column type that is not mapped by default by Hibernate. For an example column type nvarchar in MSSQL could lead to an exception.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| .... | |
| Exception [org.hibernate.MappingException: No Dialect mapping for JDBC type: -9] org.hibernate.dialect.TypeNames.get(TypeNames.java:77) | |
| org.hibernate.dialect.TypeNames.get(TypeNames.java:100) | |
| org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:378) | |
| org.hibernate.loader.custom.CustomLoader$Metadata.getHibernateType(CustomLoader.java:590) | |
| org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.performDiscovery(CustomLoader.java:516) | |
| org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:532) | |
| org.hibernate.loader.Loader.getResultSet(Loader.java:1962) | |
| org.hibernate.loader.Loader.doQuery(Loader.java:802) | |
| org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) | |
| org.hibernate.loader.Loader.doList(Loader.java:2542) | |
| org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) | |
| org.hibernate.loader.Loader.list(Loader.java:2271) | |
| org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316) | |
| org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842) | |
| org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165) | |
| org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157) | |
| .... |
by This can be easily fix by adding a custom SQL Dialect. Here is an example of a typical application.yml without custom SQL dialect
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| spring: | |
| datasource: | |
| driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver | |
| url: jdbc:sqlserver://localhost\MSSQLSERVER2012;databaseName=boot | |
| username: user | |
| password: password | |
| platform: mssql | |
| jpa: | |
| show-sql: false |
To fix this you will have to create a custom SQL dialect which will map nvarchar to a respective type
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| package my.zin.rashidi.dialect; | |
| import org.hibernate.dialect.SQLServer2012Dialect; | |
| import org.hibernate.type.StringType; | |
| import static java.sql.Types.NVARCHAR; | |
| /** | |
| * @author Rashidi Zin | |
| */ | |
| public class CustomSQLServer2012Dialect extends SQLServer2012Dialect { | |
| public CustomSQLServer2012Dialect() { | |
| super(); | |
| registerHibernateType(NVARCHAR, StringType.INSTANCE.getName()); | |
| } | |
| } |
Finally inform Spring Boot to use this dialect instead of the default org.hibernate.dialect.SQLServer2012Dialect.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| spring: | |
| datasource: | |
| driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver | |
| url: jdbc:sqlserver://localhost\MSSQLSERVER2012;databaseName=boot | |
| username: user | |
| password: password | |
| platform: mssql | |
| jpa: | |
| database_platform: my.zin.rashidi.dialect.CustomSQLServer2012Dialect | |
| show-sql: false |
This should fixed the mapping exception.
