我正在尝试使用带有PostgreSQL驱动程序的Hibernate开发喜欢CockroachDB Cluster数据库的Spring MVC应用程序。
与数据库的连接很好，并且创建了所有表。
但是，当我启动服务器时，出现此错误
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaAutoConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1628) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1081) ~[spring-context-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:856) ~[spring-context-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542) ~[spring-context-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122) ~[spring-boot-1.5.3.RELEASE.jar:1.5.3.RELEASE]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:737) [spring-boot-1.5.3.RELEASE.jar:1.5.3.RELEASE]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:370) [spring-boot-1.5.3.RELEASE.jar:1.5.3.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:314) [spring-boot-1.5.3.RELEASE.jar:1.5.3.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1162) [spring-boot-1.5.3.RELEASE.jar:1.5.3.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1151) [spring-boot-1.5.3.RELEASE.jar:1.5.3.RELEASE]
at fr.esper.back.restfull.RestfullBackServer.main(RestfullBackServer.java:9) [classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_144]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_144]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_144]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_144]
at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) [spring-boot-devtools-1.5.3.RELEASE.jar:1.5.3.RELEASE]
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:954) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:882) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60) ~[spring-orm-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:353) ~[spring-orm-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:370) ~[spring-orm-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:359) ~[spring-orm-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624) ~[spring-beans-4.3.8.RELEASE.jar:4.3.8.RELEASE]
... 21 common frames omitted
Caused by: org.hibernate.exception.SQLGrammarException: Error accessing index information: testdb.car
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.convertSQLException(InformationExtractorJdbcDatabaseMetaDataImpl.java:99) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getIndexes(InformationExtractorJdbcDatabaseMetaDataImpl.java:609) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.tool.schema.extract.internal.TableInformationImpl.indexes(TableInformationImpl.java:110) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.tool.schema.extract.internal.TableInformationImpl.getIndex(TableInformationImpl.java:121) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.findMatchingIndex(SchemaMigratorImpl.java:292) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.applyIndexes(SchemaMigratorImpl.java:277) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigrationToTargets(SchemaMigratorImpl.java:184) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigration(SchemaMigratorImpl.java:60) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:134) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:101) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:472) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:444) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:879) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
... 27 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
Détail : source SQL:
SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ct.relname AS TABLE_NAME, NOT i.indisunique AS NON_UNIQUE, NULL AS INDEX_QUALIFIER, ci.relname AS INDEX_NAME, CASE i.indisclustered WHEN true THEN 1 ELSE CASE am.amname WHEN 'hash' THEN 2 ELSE 3 END END AS TYPE, (i.keys).n AS ORDINAL_POSITION, trim(both '"' from pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false)) AS COLUMN_NAME, CASE am.amcanorder WHEN true THEN CASE i.indoption[(i.keys).n - 1] & 1 WHEN 1 THEN 'D' ELSE 'A' END ELSE NULL END AS ASC_OR_DESC, ci.reltuples AS CARDINALITY, ci.relpages AS PAGES, pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION FROM pg_catalog.pg_class ct JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) JOIN (SELECT i.indexrelid, i.indrelid, i.indoption, i.indisunique, i.indisclustered, i.indpred, i.indexprs, information_schema._pg_expandarray(i.indkey) AS keys FROM pg_catalog.pg_index i) i ON (ct.oid = i.indrelid) JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) JOIN pg_catalog.pg_am am ON (ci.relam = am.oid) WHERE true AND n.nspname = 'testdb' AND ct.relname = 'car' ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION
^
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2412) ~[postgresql-42.0.0.jar:42.0.0]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2125) ~[postgresql-42.0.0.jar:42.0.0]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:297) ~[postgresql-42.0.0.jar:42.0.0]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428) ~[postgresql-42.0.0.jar:42.0.0]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354) ~[postgresql-42.0.0.jar:42.0.0]
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:301) ~[postgresql-42.0.0.jar:42.0.0]
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:287) ~[postgresql-42.0.0.jar:42.0.0]
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:264) ~[postgresql-42.0.0.jar:42.0.0]
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:231) ~[postgresql-42.0.0.jar:42.0.0]
at org.postgresql.jdbc.PgDatabaseMetaData.getIndexInfo(PgDatabaseMetaData.java:2325) ~[postgresql-42.0.0.jar:42.0.0]
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getIndexes(InformationExtractorJdbcDatabaseMetaDataImpl.java:567) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
... 38 common frames omitted
Process finished with exit code 0
模式很简单：用户拥有多辆汽车。
班级用户
Entity
@Table(name = "ApplicationUser")
public class User {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@NotNull @Column(unique = true)
private String username;
@NotNull @Size(min = 5, max = 15)
private String password;
@NotNull
private String firstName;
@NotNull
private String lastName;
@NotNull @Column(unique = true)
private String mailAddress;
@NotNull @Column(unique = true)
private String phoneNumber;
...........
}
类车
@Entity
@Table(name = "car",
indexes = @Index(name = "owner_index", columnList = "owner_id"))
public class Car {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
private String name;
@OneToOne
private User owner;
......
}
Application.properties
spring.datasource.url = jdbc:postgresql://~~~~~~/testdb
spring.jpa.show-sql = true
spring.jpa.database = postgresql
spring.datasource.driver-class-name= org.postgresql.Driver
spring.jpa.hibernate.ddl-auto = update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
问题似乎来自Car表中外键“所有者”上的索引，因为未创建外键的索引。
当我尝试显示Car的索引时，我得到：
~~~~~~~~~~/testdb> show indexes from car;
+-------+---------+--------+-----+--------+-----------+---------+----------+
| Table | Name | Unique | Seq | Column | Direction | Storing | Implicit |
+-------+---------+--------+-----+--------+-----------+---------+----------+
| car | primary | true | 1 | id | ASC | false | false |
+-------+---------+--------+-----+--------+-----------+---------+----------+
我已经尝试在owner_id字段上手动创建索引，但是它不起作用。
最佳答案
CockroachDB当前不支持Hibernate使用的所有SQL功能。
查看失败的查询，问题出在访问(i.keys).n，其中键来自information_schema._pg_expandarray(i.indkey)。
我们正在跟踪CockroachDB here中对Hibernate的支持，以及对_pg_expandarray跟踪的here的更具体的支持。