Creating One to Many relation in JPA Spring

Question

I would like to link a comment table and a film table with a user table. I wish to allow a user to have many comments, and a film have many comments. I then want to display a list of comments in a details page for each film, giving the option for the user who created the comment to delete or update it.

I altered my code in an attempt to create a one to many relation between comment and film, but I get the error:

Caused by: org.h2.jdbc.JdbcSQLException: NULL not allowed for column "FILM_ID"; SQL statement: alter table film add column film_id bigint not null [23502-196]

It makes me think two things:

1) Set to allow null or figure out why there is a null field. I attempted allow null by adding @Column(name = "film_id", nullable = true) but it said parameter is redundant.

2) Film table has auto incrementing ID already, so by adding @Column(name = "film_id") am I duplicating an ID? As with the error message saying "add column" it made me think so?

My attempt currently stands at:

Film.java

package com.demo.spring.domain;

import org.springframework.format.annotation.DateTimeFormat;

import javax.persistence.*;
import java.util.Date;
import java.util.List;

@Entity
public class Film {

    @Id @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "film_id", nullable = true)
    Long id;

    String title;
    String director;
    String description;
    @DateTimeFormat(pattern="yyyy-MM-dd")
    Date date;

    @OneToMany(cascade = CascadeType.ALL)
    @JoinColumn(name = "film_id", referencedColumnName = "film_id")
    List<Comment> comments;


    public List<Comment> getComments() {
        return comments;
    }

    public void setComments(List<Comment> comments) {
        this.comments = comments;
    }


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
//rest of getter and setters below//

Comment.java

package com.demo.spring.domain;

import javax.persistence.*;

@Entity
public class Comment {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "comment_id")
    Long id;

    String body;

    @Column(name = "film_id")
    Long filmId;

    public Long getFilmId() {
        return filmId;
    }

    public void setFilmId(Long filmId) {
        this.filmId = filmId;
    }

    public Comment(){

    }

    public Long getId() {
        return id;
    }

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

    public String getBody() {
        return body;
    }

    public void setBody(String body) {
        this.body = body;
    }

}

UPDATE

I have changed Film.java..

From:

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "film_id", referencedColumnName = "film_id")
List<Comment> comments;

To:

@OneToMany(cascade = CascadeType.ALL)
List<Comment> comments;

And if I add in Comment.java:

@OneToMany(cascade=CascadeType.ALL)

@JoinTable(name="film", joinColumns=@JoinColumn(name = "film_id_fk", referencedColumnName = "film_id"))

private Set<Comment> comment = new HashSet<Comment>();
Film film;

I get:

MappingException: Foreign key (FK5vk85sy54a8be115ye9ra1lyu:film_comments [film_film_id])) must have same number of columns as the referenced primary key (film [film_id_fk,comment_comment_id])

If I change private Set<Comment> comment = new HashSet<Comment>(); to List<Comment> comments = new ArrayList<Comment>(); I get:

NULL not allowed for column "FILM_ID"; SQL statement: alter table film add column film_id bigint not null

And if instead I add:

@OneToMany(cascade=CascadeType.ALL)

@JoinColumn(name = "film_id_fk", referencedColumnName = "film_id")

private Set<Comment> comment = new HashSet<Comment>();
Film film;

I get:

MappingException: Could not determine type for: com.demo.spring.domain.Film, at table: comment, for columns: [org.hibernate.mapping.Column(film)]

If I change private Set<Comment> comment = new HashSet<Comment>(); to List<Comment> comments = new ArrayList<Comment>(); I get:

NULL not allowed for column "FILM_ID"; SQL statement: alter table film add column film_id bigint not null

Answer 1

A primary key can't be null, so you can't make "film_id" nullable. And your @JoinColumn annotation is wrong, that goes on the @ManyToOne side. The name parameter should be the name of the foreign key column in the Comments table (so it can't be the same name as the primary key) and referencedColumnName should be the name of the column that you're referencing in the other table

@Entity
public class Film {

    @Id @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "film_id")
    Long id;

    String title;
    String director;
    String description;
    @DateTimeFormat(pattern="yyyy-MM-dd")
    Date date;

    @OneToMany(cascade = CascadeType.ALL)
    List<Comment> comments;

    //...

}

@Entity
public class Comment {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "comment_id")
    Long id;

    String body;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "film_id_fk", referencedColumnName = "film_id")
    Film film;

    //...

}