How do I write schema sql for H2 for the presented entities?

Admin 1
Viewed 889

I want to run an internal database using H2. I have a news item to which comments are attached. This all works if the database is created automatically in PostgreSQL.

My entities:

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;

}

i tried to take the sql code from postgresql but it gives an error

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
)

I get this error

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]

I have a Postgresql dialect,

spring-data-jpa spring-boot h2 spring sql
2 Answers

Relational databases support general SQL statements, but they also add extra extensions to it. The extensions you see in PostgreSQL won't work in H2.

Having said that, your DDL will work with minor modifications in H2. For example:

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

Try any of these steps

1.Some syntaxes for H2 are different. You can modify the existing create query accordingly or try running in PostgreSQL Compatibility Mode given here

2.Enable create in property and enable SQL queries to print in the log while the app starts. It will print create queries.

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