You might have seen similar kind of exception occurred during performing native queries without entity class and this kind of issue may have been occurred in different databases such as MySQL, Oracle, SQL Server, PostgreSQL etc. Depending upon the database vendor you will get error for JDBC types -4, -9, 7 or 2003 etc. It means that Hibernate is unable to implement a type mapping for database type(for example, table column type float) to java type (float) though float type is listed in the JDBC reference.

Here I will create an example how to query Oracle database without entity class using Hibernate JPA and in what situation it throws the exception “org.hibernate.MappingException: No Dialect mapping for JDBC type: -9”. In this example I will also show you how to resolve the issue.

I assume that you are using Hibernate JPA with Spring Boot application.

Example

Oracle database configurations

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.jeejava.jparepo")
public class OracleDbConfig {
	@Bean
	public DataSource dataSource() throws SQLException {
		OracleDataSource dataSource = new OracleDataSource();
		dataSource.setUser("");
		dataSource.setPassword("");
		dataSource.setURL(
										"jdbc:Oracle:thin:@//:/");
		dataSource.setImplicitCachingEnabled(true);
		dataSource.setFastConnectionFailoverEnabled(true);
		return dataSource;
	}
	@Bean
	public EntityManagerFactory entityManagerFactory() throws SQLException {
		HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
		vendorAdapter.setDatabase(Database.ORACLE);
		vendorAdapter.setShowSql(true);
		LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
		factory.setJpaVendorAdapter(vendorAdapter);
		factory.setPackagesToScan("com.jeejava.model");
		factory.setDataSource(dataSource());
		factory.afterPropertiesSet();
		return factory.getObject();
	}
	@Bean
	public PlatformTransactionManager transactionManager() throws SQLException {
		JpaTransactionManager txManager = new JpaTransactionManager();
		txManager.setEntityManagerFactory(entityManagerFactory());
		return txManager;
	}
}

Create below DAO class in order to perform database query to fetch something. Notice here we don’t have any Entity class or Spring JpaRepository interface API is used.

@Repository
public class UserGroupRepo {
	@Autowired
	private EntityManagerFactory entityManagerFactory;
	public String getUserGroup() {
		Query groups = entityManagerFactory.createEntityManager().createNativeQuery(
										"SELECT GROUP_NAME FROM USER_GROUP WHERE EMAIL='someemail@email.com'");
		return (String) groups.getSingleResult();
	}
}

When you call the above getUserGroup() method from other class you will get below exception

SEVERE: Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: -9] with root cause
org.hibernate.MappingException: No Dialect mapping for JDBC type: -9
                at org.hibernate.dialect.TypeNames.get(TypeNames.java:70)
                at org.hibernate.dialect.TypeNames.get(TypeNames.java:101)
                at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:666)
                at org.hibernate.loader.custom.JdbcResultMetadata.getHibernateType(JdbcResultMetadata.java:77)
                at org.hibernate.loader.custom.ScalarResultColumnProcessor.performDiscovery(ScalarResultColumnProcessor.java:45)
                at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:487)
                at org.hibernate.loader.Loader.getResultSet(Loader.java:2125)
                at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)
                at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)
                at org.hibernate.loader.Loader.doQuery(Loader.java:919)
                at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
                at org.hibernate.loader.Loader.doList(Loader.java:2617)
                at org.hibernate.loader.Loader.doList(Loader.java:2600)
                at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429)
                at org.hibernate.loader.Loader.list(Loader.java:2424)
                at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336)
                at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1967)
                at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322)
                at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125)
                at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
                at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:529)
                at com.jeejava.dao.UserGroupRepo.getUserGroup(UserGroupRepo.java:18)
                ...more

So what went wrong? In the above example you see we don’t have any entity class for our USER_GROUP table but we are querying database table directly without using entity class and Spring’s JpaRepository interface. Therefore hibernate is not able to map the database column data type to java type. That’s is why we are getting such exception. Even if we create our custom query, then also we need to use entity class and JpaRepository to query database table in order to get expected results. But here we are neither using entity class nor JpaRepository interface to query database. The next step is, how we will resolve this issue. The answer is writing custom dialect and set it to vendorAdapter object using method setDatabasePlatform().

Writing Hibernate custom dialect

package com.jeejava.dialect;
import java.sql.Types;
import org.hibernate.dialect.Oracle12cDialect;
import org.hibernate.type.StandardBasicTypes;
public class CustomDialectOracle extends Oracle12cDialect {
	public CustomDialectOracle() {
		super();
		registerHibernateType(Types.NCHAR, StandardBasicTypes.CHARACTER.getName());
		registerHibernateType(Types.NCHAR, 1, StandardBasicTypes.CHARACTER.getName());
		registerHibernateType(Types.NCHAR, 255, StandardBasicTypes.STRING.getName());
		registerHibernateType(Types.NVARCHAR, StandardBasicTypes.STRING.getName());
		registerHibernateType(Types.LONGNVARCHAR, StandardBasicTypes.TEXT.getName());
		registerHibernateType(Types.NCLOB, StandardBasicTypes.CLOB.getName());
	}
                
}

In the above code you see T have extended the class Oracle12cDialect but here you have to extend the class according to your database vendor and version.
Now update the below code snippets

replace

HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
vendorAdapter.setDatabase(Database.ORACLE);
vendorAdapter.setShowSql(true);

by

HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
vendorAdapter.setDatabase(Database.ORACLE);
vendorAdapter.setDatabasePlatform("com.jeejava.dialect.CustomDialectOracle");
vendorAdapter.setShowSql(true);

Now execute the query again, you will get the expected result.

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

Your email address will not be published. Required fields are marked *