postgresql - 获取休眠异常：org.hibernate.exception.SQLGrammarException：在 HBM2DDL_AUTO 设置为更新的情况下访问表元数据时出错
问题描述
尝试使用 C3P0 休眠时出现以下异常。
[main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - [Amazon](500310) Invalid operation: subquery in FROM must have an alias
Position: 15;
Exception in thread "main" org.hibernate.exception.SQLGrammarException: Error accessing table metadata
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.convertSQLException(InformationExtractorJdbcDatabaseMetaDataImpl.java:99)
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.locateTableInNamespace(InformationExtractorJdbcDatabaseMetaDataImpl.java:354)
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getTable(InformationExtractorJdbcDatabaseMetaDataImpl.java:241)
at org.hibernate.tool.schema.internal.exec.ImprovedDatabaseInformationImpl.getTableInformation(ImprovedDatabaseInformationImpl.java:109)
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.performMigration(SchemaMigratorImpl.java:252)
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigration(SchemaMigratorImpl.java:137)
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigration(SchemaMigratorImpl.java:110)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:176)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:64)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:458)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:465)
at com.xxxxx.validation.execute.Executor.main(Executor.java:62)
Caused by: java.sql.SQLException: [Amazon](500310) Invalid operation: subquery in FROM must have an alias
Position: 15;
at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source)
at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(Unknown Source)
at com.amazon.redshift.client.PGMessagingContext.handleMessage(Unknown Source)
at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(Unknown Source)
at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(Unknown Source)
at com.amazon.redshift.client.PGMessagingContext.getBindComplete(Unknown Source)
at com.amazon.redshift.client.PGClient.handleErrorsScenario1(Unknown Source)
at com.amazon.redshift.client.PGClient.handleErrors(Unknown Source)
at com.amazon.redshift.client.PGClient.directExecute(Unknown Source)
at com.amazon.redshift.client.PGClient.directExecute(Unknown Source)
at com.amazon.redshift.dataengine.PGDataEngine.makeNewMetadataSource(Unknown Source)
at com.amazon.dsi.dataengine.impl.DSIDataEngine.makeNewMetadataResult(Unknown Source)
at com.amazon.redshift.dataengine.PGDataEngine.makeNewMetadataResult(Unknown Source)
at com.amazon.jdbc.jdbc41.S41DatabaseMetaData.createMetaDataResult(Unknown Source)
at com.amazon.jdbc.common.SDatabaseMetaData.getTables(Unknown Source)
at com.mchange.v2.c3p0.impl.NewProxyDatabaseMetaData.getTables(NewProxyDatabaseMetaData.java:2962)
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.locateTableInNamespace(InformationExtractorJdbcDatabaseMetaDataImpl.java:339)
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getTable(InformationExtractorJdbcDatabaseMetaDataImpl.java:241)
at org.hibernate.tool.schema.internal.exec.ImprovedDatabaseInformationImpl.getTableInformation(ImprovedDatabaseInformationImpl.java:109)
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.performMigration(SchemaMigratorImpl.java:252)
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigration(SchemaMigratorImpl.java:137)
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigration(SchemaMigratorImpl.java:110)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:176)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:64)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:458)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:465)
Caused by: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: subquery in FROM must have an alias
Position: 15;
... 26 more
如果我更改 Environment.HBM2DDL_AUTO，“无”它正在工作。或者如果我从 DB_URL 中删除 C3P0 属性和“?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&tcpKeepAlive=true”，它就可以工作。
如果 Environment.HBM2DDL_AUTO 设置为更新或创建，我无法理解为什么它不起作用。
配置代码：
Configuration configuration = new Configuration();
configuration.setProperty("hibernate.current_session_context_class", "thread");
configuration.setProperty(Environment.DRIVER, "org.postgresql.Driver");
configuration.setProperty(Environment.URL,
DB_URL +
"?ssl=true&sslfactory=org.postgresql.ssl" +
".NonValidatingFactory&tcpKeepAlive=true");
configuration.setProperty(Environment.USER, getUsername());
configuration.setProperty(Environment.PASS, getPassword());
configuration.setProperty("hibernate.connection.release_mode", "auto");
configuration.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
configuration.setProperty("hibernate.show_sql", "true");
configuration.setProperty(Environment.HBM2DDL_AUTO, "update");
configuration.setProperty(Environment.AUTOCOMMIT, "true");
configuration.setProperty("hibernate.c3p0.min_size", "1");
configuration.setProperty("hibernate.c3p0.max_size", "1");
configuration.setProperty("hibernate.c3p0.timeout", "300");
configuration.setProperty("hibernate.c3p0.max_statements", "5");
configuration.setProperty("hibernate.c3p0.idle_test_period", "300");
configuration.addAnnotatedClass(SourceTable.class);
ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
.applySettings(configuration.getProperties())
.build();
SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);
Session session = sessionFactory.getCurrentSession();
Transaction transaction = session.beginTransaction();
SourceTable sourceTable = new SourceTable();
sourceTable.setStringID("1");
sourceTable.setStringValue("somevalue");
session.save(sourceTable);
transaction.commit();
源表代码：
import lombok.Getter;
import lombok.Setter;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Getter
@Setter
@Entity
@Table(name = "sourcetable")
public class SourceTable {
@Id
@Column(name = "stringid")
private String stringID;
@Column(name = "stringvalue")
private String stringValue;
}
添加日志：
[main] INFO org.hibernate.annotations.common.Version - HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
[main] INFO org.hibernate.c3p0.internal.C3P0ConnectionProvider - HHH010002: C3P0 using driver: org.postgresql.Driver at URL: jdbc:postgresql://xxxxx:port/dbname?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&tcpKeepAlive=true
[main] INFO org.hibernate.c3p0.internal.C3P0ConnectionProvider - HHH10001001: Connection properties: {user=*****, password=****, autocommit=true, release_mode=auto}
[main] INFO org.hibernate.c3p0.internal.C3P0ConnectionProvider - HHH10001003: Autocommit mode: true
[MLog-Init-Reporter] INFO com.mchange.v2.log.MLog - MLog clients using slf4j logging.
[main] INFO com.mchange.v2.c3p0.C3P0Registry - Initializing c3p0-0.9.5.1 [built 16-June-2015 00:06:36 -0700; debug? true; trace: 10]
[main] INFO org.hibernate.c3p0.internal.C3P0ConnectionProvider - HHH10001007: JDBC isolation level: <unknown>
[main] INFO com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource - Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@56e8076 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@894303c4 [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, identityToken -> 30f3c7a7juegyz7iq99d|56aaaecd, idleConnectionTestPeriod -> 300, initialPoolSize -> 1, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 300, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 1, maxStatements -> 5, maxStatementsPerConnection -> 0, minPoolSize -> 1, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@6247eb23 [ description -> null, driverClass -> null, factoryClassLocation -> null, forceUseNamedDriverClass -> false, identityToken -> 30f3c7a7juegyz7iq99d|302a07d, jdbcUrl -> jdbc:postgresql://XXXXX:port/dbname?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&tcpKeepAlive=true, properties -> {user=******, password=******, autocommit=true, release_mode=auto} ], preferredTestQuery -> null, privilegeSpawnedThreads -> false, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, extensions -> {}, factoryClassLocation -> null, identityToken -> 30f3c7a7juegyz7iq99d|395b56bb, numHelperThreads -> 3 ]
[main] INFO org.hibernate.dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect
[main] INFO org.hibernate.engine.jdbc.env.internal.LobCreatorBuilderImpl - HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
[main] INFO org.hibernate.type.BasicTypeRegistry - HHH000270: Type registration [java.util.UUID] overrides previous : org.hibernate.type.UUIDBinaryType@21d8bcbe
[main] INFO org.hibernate.envers.boot.internal.EnversServiceImpl - Envers integration enabled? : true
[main] INFO org.hibernate.tuple.PojoInstantiator - HHH000182: No default (no-argument) constructor for class: com.validation.tables.SourceTable (class must be instantiated by Interceptor)
[main] WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 500310, SQLState: 42601
解决方案
如果您使用的是 Hibernate 5，请确保您的方言与相应版本的 Postgres 匹配。
见这里：https ://docs.jboss.org/hibernate/orm/5.2/javadocs/org/hibernate/dialect/package-summary.html
由于您使用的是 Hibernate 5.1，因此您应该尝试将方言设置为：
org.hibernate.dialect.PostgreSQL94Dialect
其他方言可以在这里找到：
https://docs.jboss.org/hibernate/orm/5.1/javadocs/
在 Hibernate 5 中，不推荐使用 PostgreSQLDialect。
推荐阅读
- android - 无法运行离子科尔多瓦平台android
- ios - 带图片的圆形加载条
- linux - 如何计算任何文件中第二列的空格
- jquery - 浏览数字数据数组并在循环期间使用 $.each 进行加减运算
- python - PyInstaller 不包含 kivy.garden.mapview 包。解决方法是什么？
- android - 在Android Dialog中将RelativeLayout背景设置为完全透明
- javascript - React Native 更改 let 值不会在子组件中更改为道具
- swift - 防止 Observabe.error 完成并迅速处理
- scala - Scala 错误：发现类型不匹配 :(Int, Int) => Int required: Ordering[Int]
- javascript - 根据统计汇总数据绘制箱线图