如何为呈现的实体编写H2的模式sql

Question

我想使用H2运行一个内部数据库。我有一条新闻，附有评论。如果数据库是在PostgreSQL中自动创建的，那么这一切都会起作用。

我的实体：

public class Comment {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private long id;
@Column(name = "date")
private Date date;
@Column(name = "text")
private String text;
@Column(name = "username")
private String username;
@Column(name = "id_news")
private long id_news;
}
public class News {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private long id;
@Column(name = "date")
private Date date;
@Column(name = "title")
private String title;
@Column(name = "text")
private String text;
@OneToMany(cascade = CascadeType.ALL, targetEntity = Comment.class)
@JoinColumn(name = "id_news")
private List<Comment> comments;
}

我试图从postgresql中获取sql代码，但它给出了一个错误

CREATE TABLE public.news
(
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
date timestamp without time zone,
text character varying(255) COLLATE pg_catalog."default",
title character varying(255) COLLATE pg_catalog."default",
CONSTRAINT news_pkey PRIMARY KEY (id)
);
CREATE TABLE public.comment
(
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
date timestamp without time zone,
id_news bigint,
text character varying(255) COLLATE pg_catalog."default",
username character varying(255) COLLATE pg_catalog."default",
CONSTRAINT comment_pkey PRIMARY KEY (id),
CONSTRAINT fkbptqmm7bn3alb6htitgfrlt95 FOREIGN KEY (id_news)
REFERENCES public.news (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)

我收到这个错误

Syntax error in SQL statement "CREATE TABLE PUBLIC.NEWS ( ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ), DATE TIMESTAMP WITHOUT TIME ZONE, TEXT CHARACTER VARYING(255) COLLATE[*] PG_CATALOG.""default"", TITLE CHARACTER VARYING(255) COLLATE PG_CATALOG.""default"", CONSTRAINT NEWS_PKEY PRIMARY KEY (ID) )"; expected "FOR, UNSIGNED, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, COMMENT, CHECK, REFERENCES, ,, )"; SQL statement:
CREATE TABLE public.news ( id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ), date timestamp without time zone, text character varying(255) COLLATE pg_catalog."default", title character varying(255) COLLATE pg_catalog."default", CONSTRAINT news_pkey PRIMARY KEY (id) ) [42001-200]

我有一种Postgresql方言，

Answer 1

关系数据库支持通用SQL语句，但它们也添加了额外的扩展。您在PostgreSQL中看到的扩展在H2中不起作用。

话虽如此，您的DDL将在H2中进行一些小的修改。例如：

create table news (
id bigint not null generated by default as identity,
date timestamp without time zone,
text character varying(255),
title character varying(255),
constraint news_pkey primary key (id)
);
create table comment (
id bigint not null generated by default as identity,
date timestamp without time zone,
id_news bigint,
text character varying(255),
username character varying(255),
constraint comment_pkey primary key (id),
constraint fkbptqmm7bn3alb6htitgfrlt95 foreign key (id_news)
references news (id)
on update no action
on delete no action
);

Answer 2

尝试以下任何步骤

1.H2的某些语法不同。您可以相应地修改现有的创建查询，或者尝试在PostgreSQL兼容模式中运行，此处给出

2.启用create-in属性，并在应用程序启动时启用SQL查询以在日志中打印。它将打印创建查询。

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create