How to fix the Hibernate org.hibernate.MappingException: No Dialect mapping for JDBC type: 2003

Question

public void GetLevel(int id) 
    {
        Type doubleArrayType=new TypeLocatorImpl(new TypeResolver())
                .custom(DoubleArrayUserType.class);
        Query query =  sf.getCurrentSession().
                createSQLQuery("select waterLevel from FtpData").
                addScalar("waterLevel", doubleArrayType);


        @SuppressWarnings("unchecked")
        List<Double[]> results=(List<Double[]>) query.list();
        System.out.println("In dao layer data:"+results);
        return;
    }

trying to access db which is having array of double for which i hav written user type class:::

package com.app.usertype;

import org.apache.commons.lang3.ArrayUtils;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;

import java.io.Serializable;
import java.sql.*;

public class DoubleArrayUserType implements UserType {
    protected static final int  SQLTYPE = java.sql.Types.ARRAY;

    @Override
    public Object nullSafeGet(final ResultSet rs, final String[] names, final SessionImplementor sessionImplementor, final Object owner) throws HibernateException, SQLException {
        Array array = rs.getArray(names[0]);
        Double[] javaArray = (Double[]) array.getArray();
        return ArrayUtils.toPrimitive(javaArray);
    }

    @Override
    public void nullSafeSet(final PreparedStatement statement, final Object object, final int i, final SessionImplementor sessionImplementor) throws HibernateException, SQLException {
        Connection connection = statement.getConnection();

        double[] castObject = (double[]) object;
        Double[] doubles = ArrayUtils.toObject(castObject);
        Array array = connection.createArrayOf("DOUBLE", doubles);

        statement.setArray(i, array);
    }

    @Override
    public Object assemble(final Serializable cached, final Object owner) throws HibernateException {
        return cached;
    }

    @Override
    public Object deepCopy(final Object o) throws HibernateException {
        return o == null ? null : ((double[]) o).clone();
    }

    @Override
    public Serializable disassemble(final Object o) throws HibernateException {
        return (Serializable) o;
    }

    @Override
    public boolean equals(final Object x, final Object y) throws HibernateException {
        return x == null ? y == null : x.equals(y);
    }

    @Override
    public int hashCode(final Object o) throws HibernateException {
        return o == null ? 0 : o.hashCode();
    }

    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public Object replace(final Object original, final Object target, final Object owner) throws HibernateException {
        return original;
    }

    @Override
    public Class<double[]> returnedClass() {
        return double[].class;
    }

    @Override
    public int[] sqlTypes() {
        return new int[] { SQLTYPE };
    }
}

but it is giving error as:::

org.hibernate.MappingException: No Dialect mapping for JDBC type: 2003 at org.hibernate.dialect.TypeNames.get(TypeNames.java:76) at org.hibernate.dialect.TypeNames.get(TypeNames.java:99) at org.hibernate.dialect.Dialect.getTypeName(Dialect.java:297) at org.hibernate.mapping.Column.getSqlType(Column.java:227) at org.hibernate.mapping.Table.validateColumns(Table.java:373) at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1275) at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:155) at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:506) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1750) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1788) at org.springframework.orm.hibernate4.LocalSessionFactoryBuilder.buildSessionFactory(LocalSessionFactoryBuilder.java:343) at org.springframework.orm.hibernate4.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:431) at org.springframework.orm.hibernate4.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:416) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1612) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1549) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:302) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:298) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1017) at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:960) at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:858) at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:480) at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:87) at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:289) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1185) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:537) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:302) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:298) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1017) at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:960) at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:858) at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:480) at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:87) at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:289) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1185) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:537) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:302) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:298) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1017) at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:960) at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:858) at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:480) at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:87) at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:289) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1185) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:537) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:302) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:298) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193) at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:703) at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:760) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:482) at org.springframework.web.servlet.FrameworkServlet.configureAndRefreshWebApplicationContext(FrameworkServlet.java:658) at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:624) at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:672) at org.springframework.web.servlet.FrameworkServlet.initWebApplicationContext(FrameworkServlet.java:543) at org.springframework.web.servlet.FrameworkServlet.initServletBean(FrameworkServlet.java:484) at org.springframework.web.servlet.HttpServletBean.init(HttpServletBean.java:136) at javax.servlet.GenericServlet.init(GenericServlet.java:158) at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1269) at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1182) at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1072) at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:5368) at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5660) at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:145) at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1571) at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1561) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)

here is ma hibernate-persistent.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
    xmlns:p="http://www.springframework.org/schema/p" xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">

    <!-- supply loc -->
    <context:property-placeholder 
    location="classpath:/database.properties" />
    <!-- DS bean -->
    <bean id="dataSource" 
    class="org.apache.commons.dbcp.BasicDataSource"
        p:driverClassName="${jdbc.driver}" p:url="${jdbc.url}" 
        p:username="${jdbc.username}"
        p:password="${jdbc.password}" 
        p:initialSize="1" p:maxActive="2"
        destroy-method="close">
    </bean>
    <!-- configure Hibernate 4 specific local session factory bean -->
    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate4.LocalSessionFactoryBean"
        p:packagesToScan="com.app.dto" p:dataSource-ref="dataSource">
        <property name="hibernateProperties">
            <value>
                hibernate.dialect= org.hibernate.dialect.PostgreSQLDialect
                hibernate.format_sql=true
                hibernate.show_sql=true
                hibernate.hbm2ddl.auto=validate

            </value>

        </property>
            </bean>
    <!-- configure tx mgr bean -->

    <bean id="transactionManager"
        class="org.springframework.orm.hibernate4.HibernateTransactionManager"
        p:sessionFactory-ref="sessionFactory">
    </bean> <!-- enable tx annotation suuport -->
    <tx:annotation-driven />

</beans>

properties

jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://127.0.0.1:5433/glofdata
jdbc.username=postgres
jdbc.password=postgres
jdbc.dialect=org.hibernate.dialect.PostgreSQLDialect

Answer 1

Hibernate does not supports database arrays (e.g. java.sql.Array)

array and primitive-array types provided by Hibernate are for mapping Java arrays into backing table - they're basically a variation of one-to-many / collection-of-elements mappings.

Here, is an example for Postgresql in which @Alex have sorted out this problem by himself using the UserType for integer arrays. Also, this example is related to mapping a postgres array with hibernate.
Hope this helps.

Answer 2

Although Hibernate does not support ARRAY types natively, you can use the Hibernate Types project for that.

Now, even if you are using the StringArrayType or IntArrayType supplied by Hibernate Types, as long as you are using JPQL, the array types will be resolved properly.

It is only when you are using native SQL that the No Dialect mapping for JDBC type issue can occur. To solve it, you have to option:

1. You can register the missing JDBC type at the Dialect level:

   public class PostgreSQL95ArrayDialect extends PostgreSQL95Dialect {
       public PostgreSQL95ArrayDialect() {
           super();
           this.registerColumnType(Types.ARRAY, "array");
       }
   }
   

   And register the PostgreSQL95ArrayDialect via the hibernate.dialect configuration property.

2. You can register the type at the query level via the addScalar method:

   String[] array = (String[]) entityManager.createNativeQuery("""
       SELECT properties
       FROM book
       WHERE isbn = :isbn
       """)
   .setParameter("isbn", "978-9730228236")
   .unwrap(org.hibernate.query.NativeQuery.class)
   .addScalar("properties", StringArrayType.INSTANCE)
   .getSingleResult();