Joining two tables in Hibernate JPA

Question

I know this question has been asked before, I'm just struggling with it with my code.

I have two tables a movies table and a user_movies table. I want to join them the movies had a PK of movie_id and the user_movies has a FK of the same name.

I created my Repositories, and they work fine as my saves in my respective controllers work, as do my basic findby queries

However, my findAll in my MoviesController (the movies method) is not working it's giving me the wrong table and column name. Here is my code.

The stack traces gives me this error

java.sql.SQLSyntaxErrorException: Unknown column 'usermovies0_.movies_movie_id' in 'field list'

What I can't figure out is what I am doing wrong that it is generating that table name and column name rather than use the ones I have??

UserMovies.java

package movieweb.movies.models;

import javax.persistence.*;
import java.io.Serializable;
import java.util.List;

@Entity
@Table(name="user_movies")
public class UserMovies implements Serializable {

    private static final long serialVersionUID = 2L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_movie_id")
    private Integer userMovieID;

    @Column(name = "movie_id")
    private Integer movieId;

    @Column(name = "uname")
    private String uname;

    @ManyToOne(fetch = FetchType.LAZY)
    private Movies movies;

    public UserMovies() {
    }

    public UserMovies(Integer userMovieID, Integer movieId, String uname) {
        super();
        this.userMovieID = userMovieID;
        this.movieId = movieId;
        this.uname = uname;
    }

    public Integer getUserMovieID() {
        return userMovieID;
    }

    public void setUserMovieID(Integer userMovieID) {
        this.userMovieID = userMovieID;
    }

    public Integer getMovieId() {
        return movieId;
    }

    public void setMovieId(Integer movieId) {
        this.movieId = movieId;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }


}

Movies.java

    package movieweb.movies.models;

import com.fasterxml.jackson.annotation.JsonIgnore;

import javax.persistence.*;
import java.io.Serializable;
import java.util.List;

@Entity
@Table(name="movies")
public class Movies implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name = "movie_id")
    private Integer movieId;

    @Column(name= "movie_name")
    private String movieName;

    @Column(name="movie_description")
    private String movieDescription;

    @JsonIgnore
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "movies")
    private List<UserMovies> userMovies;

    public Movies() {
    }

    public Movies(Integer movieId, String movieName, String movieDescription) {
        super();
        this.movieId = movieId;
        this.movieName = movieName;
        this.movieDescription = movieDescription;
    }

    public Integer getMovieId() {
        return movieId;
    }

    public void setMovieId(Integer id) {
        this.movieId = id;
    }

    public String getMovieName() {
        return movieName;
    }

    public void setMovieName(String name) {
        this.movieName = name;
    }

    public String getMovieDescription() {
        return movieDescription;
    }

    public void setMovieDescription(String description) {
        this.movieDescription = description;
    }

    public List<UserMovies> getUserMovies() {
        return userMovies;
    }

    public void setUserMovies(List<UserMovies> userMovies) {
        this.userMovies = userMovies;
    }
}

UserMoviesController.java

package movieweb.movies.controllers;

import movieweb.movies.models.UserMovies;
import movieweb.movies.repository.UserMoviesRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.Query;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
public class UserMoviesController {

    @Autowired
    private UserMoviesRepository umRepository;

    @CrossOrigin
    @PostMapping(path = "/newUserMovie")
    public UserMovies addNewUserMovie(@RequestBody UserMovies data){
        return  umRepository.save(data);
    }

    @CrossOrigin
    @GetMapping(path="/getUserMovies")
    public @ResponseBody List<UserMovies> getUserMovies(){
        return (List<UserMovies>) umRepository.findAll();
    }
}

MoviesController.java

package movieweb.movies.controllers;

import movieweb.movies.models.Movies;
import movieweb.movies.models.UserMovies;
import movieweb.movies.models.Users;
import movieweb.movies.repository.MoviesRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.stream.Collectors;

import org.springframework.web.client.RestTemplate;
import org.springframework.web.server.ResponseStatusException;

@RestController
public class MoviesController {

    @Autowired
    private MoviesRepository moviesRepository;
    @Autowired
    private RestTemplate restTemplate;

    @CrossOrigin
    @GetMapping(path = "/movies")
    public @ResponseBody List<Movies> movies(){
        List<Movies> allMovies = (List<Movies>) moviesRepository.findAll();
        if (!allMovies.isEmpty()){
            return allMovies;
        } else {
            throw new ResponseStatusException(
                    HttpStatus.NOT_FOUND, "Movies not found"
            );
        }


    }

//    @CrossOrigin
//    @RequestMapping(path = "movies/user/{id}")
//    public List<Movies> movie(@PathVariable("id") int id){
//        return this.movies().stream().map(movie -> {
//            Users[] user = restTemplate.getForObject("http://127.0.0.1:8082/users/" + id, Users[].class);
//            return new Movies(movie.getMovieId(), movie.getMovieName(), "Description");
//        })
//                .collect(Collectors.toList());
//    }

    @CrossOrigin
    @GetMapping(path="/movie/{id}")
    public @ResponseBody Movies getMovie(@PathVariable Integer id){
        return moviesRepository.findById(id)
                .orElseThrow(() -> new ResponseStatusException(
                HttpStatus.NOT_FOUND, "Movie not found"
        ) );
    }

    @CrossOrigin
    @DeleteMapping("/movie/delete/{id}")
    void deleteMovie(@PathVariable Integer id) {
        moviesRepository.deleteById(id);
    }

    @CrossOrigin
    @PutMapping("/movie/update/{id}")
    Movies updateMovie(@RequestBody Movies updateMovie, @PathVariable Integer id) {

        return moviesRepository.findById(id)
                .map(Movies -> {
                    Movies.setMovieName(updateMovie.getMovieName());
                    Movies.setMovieDescription(updateMovie.getMovieDescription());
                    return moviesRepository.save(Movies);
                })
                .orElseGet(() -> {
                    updateMovie.setMovieId(id);
                    return moviesRepository.save(updateMovie);
                });
    }

    @CrossOrigin
    @PostMapping(path="/newMovie")
    public Movies addNewMovie (@RequestBody Movies data) {
       return moviesRepository.save(data);
    }


}

Answer 1

You do not provide a joining column name for the following @ManyToOne association:

@ManyToOne(fetch = FetchType.LAZY)
private Movies movies;

So, hibernate tries to identify it by using default naming strategy. According to the JPA specification (see 2.10.3.2 Unidirectional ManyToOne Relationships section):

The foreign key column name is formed as the concatenation of the following: the name of the relationship property or field of entity A; "_"; the name of the primary key column in table B. The foreign key column has the same type as the primary key of table B.

So, the joining column name for this association will be movies_movie_id. But it does not exist in the user_movies table hence you get the mentioned in your question exception.

You should correct your mapping for the UserMovies entity in the following way:

@Entity
@Table(name="user_movies")
public class UserMovies implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_movie_id")
    private Integer userMovieID;

    @Column(name = "uname")
    private String uname;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "movie_id")
    private Movies movies;

    // ...
}