如何修复Hibernate org.hibernate.MappingException:没有方言映射的JDBC类型:2

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;
}

试图访问数据库，它有一个双精度数组，我为它写了用户类型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 };
}
}

但是给出错误:::

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

这里是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>

属性

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不支持database arrays(例如java.sql.Array)

Hibernate提供的array和primitive-array类型用于将Java数组映射到后备表-它们基本上是一对多/元素集合映射的一种变体。

这里，是一个Postgresql的例子，其中@Alex自己用UserType for integer arrays解决了这个问题。此外，这个示例涉及到将postgres数组映射到hibernate。
希望这对你有帮助。

Answer 2

虽然Hibernate本身不支持ARRAY类型，但是您可以使用Hibernate Types项目来实现。

现在，即使您使用Hibernate类型提供的StringArrayType或IntArrayType，只要您使用JPQL，数组类型将被正确解析。

只有在使用本机SQL时才会出现No Dialect mapping for JDBC type问题。要解决这个问题，您必须选择:

1. 您可以在Dialect级别注册缺失的JDBC类型:

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

   通过hibernate.dialect配置属性注册PostgreSQL95ArrayDialect。

2. 您可以通过addScalar方法在查询级别注册类型:

   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();