时间戳

首页 > 解决方案 > 在 Hibernate JPA 中连接两个表

hibernate - 在 Hibernate JPA 中连接两个表

问题描述

我知道以前有人问过这个问题，我只是在用我的代码来解决这个问题。

我有两个表，一个电影表和一个 user_movies 表。我想加入他们，电影有一个movie_id 的PK，而user_movies 有一个同名的FK。

我创建了我的存储库，它们工作正常，因为我在各自控制器中的保存工作正常，我的基本 findby 查询也是如此

但是，我的 MoviesController （电影方法）中的 findAll 不起作用，它给了我错误的表和列名。这是我的代码。

堆栈跟踪给了我这个错误

java.sql.SQLSyntaxErrorException：“字段列表”中的未知列“usermovies0_.movi​​es_movie_id”

我无法弄清楚我做错了什么，它正在生成表名和列名，而不是使用我拥有的那些？

用户电影.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;
    }


}

电影.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);
    }


}

标签： hibernatespring-bootjpa

解决方案

您没有为以下@ManyToOne关联提供连接列名称：

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

因此，hibernate 尝试使用默认命名策略来识别它。根据 JPA 规范（参见2.10.3.2 单向多对一关系部分）：

外键列名由以下内容串联构成：实体的关系属性或字段的名称A；"_"; 表中主键列的名称B。外键列与表的主键类型相同B。

因此，此关联的连接列名称将是movies_movie_id. 但它在表中不存在，user_movies因此您会在问题异常中得到提及。

您应该UserMovies通过以下方式更正实体的映射：

@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;

    // ...
}

推荐阅读