将Hibernate与QuestDB一起使用失败:缺少架构表

Question

我正在尝试使用Hibernate和Java Micronaut Framework来设置QuestDB。我无法正确映射到Java实体。此外，QuestDB在启动时似乎无法生成模式表。我正在使用postgres驱动程序连接QuestDB和Hibernate进行映射。我们非常感谢您的任何见解，因为我对文档有深入的了解。

所以我试着把它归结为尽可能简单的例子。我将从错误开始。

启动时，我收到来自Micronaut的以下信息：

Caused by: org.hibernate.exception.GenericJDBCException: Unable to build DatabaseInformation
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:163)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:96)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:469)
at io.micronaut.configuration.hibernate.jpa.EntityManagerFactoryBean.hibernateSessionFactory(EntityManagerFactoryBean.java:219)
at io.micronaut.configuration.hibernate.jpa.$EntityManagerFactoryBean$HibernateSessionFactory3Definition.build(Unknown Source)
at io.micronaut.context.BeanDefinitionDelegate.build(BeanDefinitionDelegate.java:153)
at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:1979)
... 73 more
Caused by: org.postgresql.util.PSQLException: ERROR: table does not exist [name=information_schema.sequences]
Position: 15
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:236)
at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111)
at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:42)
at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:65)
at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.<init>(DatabaseInformationImpl.java:59)
at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:155)
... 82 more

在QuestDB日志中，它提供了更多的细节：

2021-06-12T19:49:08.431644Z I i.q.c.p.PGConnectionContext protocol [major=3, minor=0]
2021-06-12T19:49:08.431661Z I i.q.c.p.PGConnectionContext propertry [name=user, value=admin]
2021-06-12T19:49:08.431672Z I i.q.c.p.PGConnectionContext propertry [name=admin, value=database]
2021-06-12T19:49:08.431681Z I i.q.c.p.PGConnectionContext propertry [name=database, value=qdb]
2021-06-12T19:49:08.431698Z I i.q.c.p.PGConnectionContext propertry [name=qdb, value=client_encoding]
2021-06-12T19:49:08.431709Z I i.q.c.p.PGConnectionContext propertry [name=client_encoding, value=UTF8]
2021-06-12T19:49:08.431720Z I i.q.c.p.PGConnectionContext propertry [name=UTF8, value=DateStyle]
2021-06-12T19:49:08.431730Z I i.q.c.p.PGConnectionContext propertry [name=DateStyle, value=ISO]
2021-06-12T19:49:08.431740Z I i.q.c.p.PGConnectionContext propertry [name=ISO, value=TimeZone]
2021-06-12T19:49:08.431750Z I i.q.c.p.PGConnectionContext propertry [name=TimeZone, value=America/Chicago]
2021-06-12T19:49:08.431761Z I i.q.c.p.PGConnectionContext propertry [name=America/Chicago, value=extra_float_digits]
2021-06-12T19:49:08.431771Z I i.q.c.p.PGConnectionContext propertry [name=extra_float_digits, value=2]
2021-06-12T19:49:08.431780Z I i.q.c.p.PGConnectionContext propertry [name=2, value=]
2021-06-12T19:49:08.432279Z I i.q.c.p.PGConnectionContext parse [q=SET extra_float_digits = 3]
2021-06-12T19:49:08.432493Z I i.q.c.p.PGConnectionContext parse [q=SET application_name = 'PostgreSQL JDBC Driver']
2021-06-12T19:49:08.464065Z I i.q.g.CharacterStore resize [capacity=8192]
....
PGConnectionContext error [pos=4525, msg=`too few arguments for '::' [found=1,expected=2]
...

数据库是使用下面非常简单的命令创建的。QuestDB的有趣之处在于没有主键支持。

CREATE TABLE genre(id LONG, name STRING);

下面列出了数据源配置和方言。请注意，我使用的是postgres DIALECT。根据QuestDB的文档，它应该可以处理这个。。。

micronaut:
application:
name: micronautguide
---
application:
max: 50
---
datasources:
default:
url: jdbc:postgresql://172.17.0.3:8812/qdb
username: admin
password: quest
driverClassName: org.postgresql.Driver
sslmode: disable
---
jpa:
default:
properties:
hibernate:
hbm2ddl:
auto: update
show_sql: true
dialect: org.hibernate.dialect.PostgreSQLDialect

这是用于映射的基本存储库和实体。

package example.micronaut;
import example.micronaut.domain.Genre;
import javax.inject.Singleton;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceException;
import javax.persistence.TypedQuery;
import javax.transaction.Transactional;
import io.micronaut.transaction.annotation.ReadOnly;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import java.util.Arrays;
import java.util.List;
import java.util.Optional;
@Singleton 
public class GenreRepositoryImpl implements GenreRepository {
private final EntityManager entityManager; 
private final ApplicationConfiguration applicationConfiguration;
public GenreRepositoryImpl(EntityManager entityManager,
ApplicationConfiguration applicationConfiguration) { // <2>
this.entityManager = entityManager;
this.applicationConfiguration = applicationConfiguration;
}
@Override
@ReadOnly  
public Optional<Genre> findById(@NotNull Long id) {
return Optional.ofNullable(entityManager.find(Genre.class, id));
}
}

这里有我们的存储库和实体映射。

package example.micronaut.domain;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
@Entity
@Table(name = "genre")
public class Genre {
public Genre() {}
public Genre(@NotNull String name) {
this.name = name;
}
@Id
@NotNull
@Column(name = "id", nullable = false)
private long id;
@NotNull
@Column(name = "name", nullable = false)
private String name;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Genre{" +
"id=" + id +
", name='" + name + ''' +
'}';
}
}

Answer 1

QuestDB在传输级别上与Postgres兼容，因此如果运行QuestDB SQL查询，就可以返回数据。查询语言不是100%相同，QuestDB错过了很多模式表/视图Postgres。

使用Postgres驱动程序使任何ORM与QuestDB一起工作远不止是二进制协议的兼容性，因此预计它不会工作。在这种情况下，它缺少information_schema.sequences表/视图。即使QuestDB添加了这个视图(为空，不支持序列(，也可能有更多的模式类型和SQL语法差异的查询，因此通过QuestDB的Postgres驱动程序使ORM工作是非常具有挑战性的。

因此，选择是关闭所有ORM智能功能，如模式查询(如果在Micronaut中可能的话(，或者请求github中的ORM支持。