Spring Boot & Postgres: relation "sub_comment" does not exist

Question

I have two entities: Comment, and SubComment. A Comment can have multiple SubComments. I'm trying to establish a one to many/many to one bidirectional relationship with Hibernate.

I do not know what is wrong. Both of the tables seem to have been created correctly in PSQL.

Comment.java

import javax.persistence.*;
import java.util.Set;

@Entity
public class Comment {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column
    private String text;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "comment")
    private Set<SubComment> subComment;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }
}

SubComment.java

import javax.persistence.*;

@Entity
public class SubComment {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String text;

    @ManyToOne
    private Comment comment;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }

    public Comment getComment() {
        return comment;
    }

    public void setComment(Comment comment) {
        this.comment = comment;
    }
}

I'm getting this error:

Error executing DDL via JDBC StatementCaused by: org.postgresql.util.PSQLException: ERROR: relation "sub_comment" does not exist

Hibernate: create table "user" (id  bigserial not null, email varchar(255), name varchar(255), username varchar(255), primary key (id))
Hibernate: create table comment (comment_id  bigserial not null, text varchar(255), primary key (comment_id))
Hibernate: create table sub_comment (sub_comment_id  bigserial not null, text varchar(255), comment_comment_id int8, primary key (sub_comment_id))
Hibernate: alter table sub_comment add constraint FK87789n34vmns9eeyw6jgc5ghp foreign key (comment_comment_id) references comment

application.properties

spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.url=jdbc:postgresql://localhost:5432/dbname
spring.datasource.data-username=username
spring.datasource.driver-class-name=org.postgresql.Driver

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL9Dialect
spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults = false

Solution 1

You missed @JoinColumn. You will get another error due to field based access. Use Property based access instead:

import javax.persistence.*;

@Entity
@Table(name = "subcomment")
public class SubComment implements Serializable {

    private static final long serialVersionUID = -3009157732242241606L;

    private long id;
    private String text;
    private Comment comment;

    @Id
    @Column(name = "sub_id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    @Basic
    @Column(name = "sub_text")
    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }

    @ManyToOne
    @JoinColumn(name = "sub_fk_c_id", referencedColumnName = "c_id") // here the exact field name of your comment id in your DB
    public Comment getComment() {
        return comment;
    }

    public void setComment(Comment comment) {
        this.comment = comment;
    }
}

Also make changes here too:

import javax.persistence.*;

@Entity
@Table(name = "comment")
public class Comment implements Serializable {

    private static final long serialVersionUID = -3009157732242241606L;    

    private long id;
    private String text;
    private Set<SubComment> subComment = new HashSet<>();

    @OneToMany(mappedBy = "comment", targetEntity = SubComment.class)
    public Set<SubComment> getSubComment() {
        return subComment;
    }

    public void setSubComment(Set<SubComment> subComment) {
        this.subComment = subComment;
    }

    @Id
    @Column(name = "c_id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    @Basic
    @Column(name = "c_text")
    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }
}

Paste the following in your application.properties file:

spring.jpa.properties.hibernate.id.new_generator_mappings = false
spring.jpa.properties.hibernate.format_sql = true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
spring.jpa.hibernate.ddl-auto=create

In your pom.xml file paste these:

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>

For further reference see this stackoverflow post.

Solution 2

I know that this answer is late but maybe it will help someone, their problem is that they have not configured the schema, which is why they are throwing that error.

Here is how to configure the schema in postgresql:

spring.jpa.properties.hibernate.default_schema = "his schema"

Comments

[Jasmine Rain]

Tried your fixes (copy and pasted), same result... org.postgresql.util.PSQLException: ERROR: relation "sub_comment" does not exist

[sam]

Added another edit. It should surely work by drop your existing tables and create new ones.

[sam]

If the solution works, then do accept the answer as solution and upvote it.

[sam]

i would ask you to see this stackoverflow solution stackoverflow.com/a/44055520/7538821

[Dawid Kubicki]

What have your link in common to question? If you mean that I miss getter/setter nowadays Lombok deals with this and I present only a general solution, not whole implementation.

[sam]

If you do a field based property access with many to one annotation then the project won't even compile and this bug is still in hibernate 5. Property based access is the only solution.

[sam]

You can refer here thoughts-on-java.org/…

[Dawid Kubicki]

Unfortunately, you are wrong. I do not know what Hibernate magic does under the hood but join column name can have a name which exists in DB. This is the piece of my code which works on real env.

[sam]

read the question. the user said that the POJO need to be in a one to many relationship in a bidirectional manner, which is why you need to mention @OneToMany annotation on the other side too.

[sam]

Oh sorry my bad i mentioned @ManyToOne in my previous comment which is shouldn't have.

[Jasmine Rain]

Same result, sub_comment does not exist. Why would I get an error due to field based access. When do you use field based vs property based?

[sam]

add your application.properties file contents to your question.

[sam]

ok now add the contents that i have suggested in my answer. Paste the entire Entity class codes(Comment,Subcomment) like i have mentioned and for sometime comment your entire entity class codes.