SpringBoot(JPA)CrudRepository中自動生成的SQL查詢不正確-有解無憂

我的存盤庫介面：

SongsRepository：

public interface SongRepository extends CrudRepository<Song, Integer> {
    
}

AlbumsRepository

public interface AlbumRepository extends CrudRepository<Album, Integer> {
    
}

我的模型類：

Album.java

@Entity
@Table(name = "albums", schema = "dbo")
public class Album {
    
    @Id
    @GeneratedValue
    @Column(name = "album_id")
    private Integer albumId;
    
    @Column(name = "album_name")
    private String albumName;
    
    @Column(name = "released_by")
    private String releasedBy;
    
    @Column(name = "total_duration")
    private Integer totalDuration;
    
    @OneToMany(targetEntity = Song.class)
    private List<Song> songs;

    public Integer getAlbumId() {
        return albumId;
    }

    public void setAlbumId(Integer albumId) {
        this.albumId = albumId;
    }

    public String getAlbumName() {
        return albumName;
    }

    public void setAlbumName(String albumName) {
        this.albumName = albumName;
    }

    public String getReleasedBy() {
        return releasedBy;
    }

    public void setReleasedBy(String releasedBy) {
        this.releasedBy = releasedBy;
    }

    public Integer getTotalDuration() {
        return totalDuration;
    }

    public void setTotalDuration(Integer totalDuration) {
        this.totalDuration = totalDuration;
    }

    public List<Song> getSongs() {
        return songs;
    }

    public void setSongs(List<Song> songs) {
        this.songs = songs;
    }
}

Song.java

@Entity
@Table(name = "songs", schema = "dbo")
public class Song {
    @Id
    @GeneratedValue
    @Column(name = "song_id")
    private Integer songId;

    @Column(name = "song_name")
    private String songName;
    
    @Column(name = "album_id")
    private Integer songAlbumId;
    
    @Column(name = "song_duration")
    private String songDuration;
    
    @ManyToOne(targetEntity = Album.class)
    private Album album;

    public Integer getSongId() {
        return songId;
    }

    public void setSongId(Integer songId) {
        this.songId = songId;
    }

    public String getSongName() {
        return songName;
    }

    public void setSongName(String songName) {
        this.songName = songName;
    }

    public Integer getSongAlbumId() {
        return songAlbumId;
    }

    public void setSongAlbumId(Integer songAlbumId) {
        this.songAlbumId = songAlbumId;
    }

    public String getSongDuration() {
        return songDuration;
    }

    public void setSongDuration(String songDuration) {
        this.songDuration = songDuration;
    }

    public Album getAlbum() {
        return album;
    }

    public void setAlbum(Album album) {
        this.album = album;
    }
}

我的控制器：

SongsController.java

@RestController
@RequestMapping("/songs")
public class SongsController {
    
    @Autowired
    private SongRepository songs;
    
    @Autowired 
    private AlbumRepository albums;
    
    @Autowired
    private Services services;
    
    @GetMapping("/")
    public List<SongViewModel> getAllSongs() {
        List<SongViewModel> listOfAllSongs = new ArrayList<>();
        songs.findAll().forEach(song -> listOfAllSongs.add(services.translateToViewModel(song)));
        return listOfAllSongs;
    }
}

服務類：

Services.java：

@Service
public class Services {

    @Autowired
    private SongRepository songs;
    
    @Autowired
    private AlbumRepository albums;
    
    public SongViewModel translateToViewModel(Song song) {
        SongViewModel model = new SongViewModel();
        model.setSongId(song.getSongId());
        model.setSongAlbumId(song.getSongAlbumId());
        model.setSongName(song.getSongName());
        model.setSongDuration(song.getSongDuration());
        model.setSongAlbumName(albums.findById(song.getSongAlbumId()).get().getAlbumName());
        return model;
    }
    
    public AlbumViewModel translateToViewModel(Album album) {
        AlbumViewModel model = new AlbumViewModel();
        
        return model;
    }
}

我的視圖模型：

SongViewModel：

public class SongViewModel {
    private String songName;
    private String songAlbumName;
    private String songDuration;
    private Integer songId;
    private Integer songAlbumId;
    public String getSongName() {
        return songName;
    }
    public void setSongName(String songName) {
        this.songName = songName;
    }
    public String getSongAlbumName() {
        return songAlbumName;
    }
    public void setSongAlbumName(String songAlbumName) {
        this.songAlbumName = songAlbumName;
    }
    public String getSongDuration() {
        return songDuration;
    }
    public void setSongDuration(String songDuration) {
        this.songDuration = songDuration;
    }
    public Integer getSongId() {
        return songId;
    }
    public void setSongId(Integer songId) {
        this.songId = songId;
    }
    public Integer getSongAlbumId() {
        return songAlbumId;
    }
    public void setSongAlbumId(Integer songAlbumId) {
        this.songAlbumId = songAlbumId;
    }
}

記錄的 SQL 查詢：
select song0_.song_id as song_id1_1_, song0_.album_album_id as album_al5_1_, song0_.album_id as album_id2_1_, song0_.song_duration as song_dur3_1_, song0_.song_name as song_nam4_1_ from dbo.songs song0_

錯誤：
ERROR: column song0_.album_album_id does not exist

我的資料庫模式(PostgreSQL 12.10.1)

這是我第一次使用 Spring Boot 和 JPA。我在這里做錯了什么？

從錯誤訊息中，我猜測它正在嘗試查找album_album_id我的資料庫中不存在的名為的列，因此無法找到它；但它為什么要搜索那個特定的列？（我的猜測是我搞砸了關系屬性）。

uj5u.com熱心網友回復：

你的映射是錯誤的。Song應該是這樣的：

@Entity
@Table(name = "songs", schema = "dbo")
public class Song {
  @Id
  @GeneratedValue
  @Column(name = "song_id")
  private Integer id;

  @Column(name = "song_name")
  private String name;
    
  @Column(name = "album_id")
  private Integer albumId;
    
  @Column(name = "song_duration")
  private String duration;
    
  @ManyToOne
  @JoinColumn(name = "album_id", insertable = false, updatable = false)
  private Album album;
}

像這樣Album：

@Entity
@Table(name = "albums", schema = "dbo")
public class Album {
  @Id
  @GeneratedValue
  @Column(name = "album_id")
  private Integer id;
  
  @Column(name = "album_name")
  private String name;
  
  @Column(name = "released_by")
  private String releasedBy;
  
  @Column(name = "total_duration")
  private Integer totalDuration;
  
  @OneToMany(mappedBy = "album")
  private List<Song> songs;
}

順便一提，

將歌曲持續時間格式化為字串而專輯持續時間格式化為整數有點奇怪。
如果您愿意從 CrudRepository 轉到 JpaRepository，您可能希望使用Spring 資料投影而不是手動將您的歌曲重鑄到 SongViewModels

這是您問題的界面投影的一個小例子

uj5u.com熱心網友回復：

您的Song物體類應更改如下。

@Entity
@Table(name = "songs", schema = "dbo")
public class Song {
    @Id
    @GeneratedValue
    @Column(name = "song_id")
    private Integer songId;

    @Column(name = "song_name")
    private String songName;
    
    @Column(name = "song_duration")
    private String songDuration;
    
    @JoinColumn(name = "album_id", referencedColumnName = "album_id")
    @ManyToOne
    private Album album;

    public Integer getSongId() {
        return songId;
    }

    public void setSongId(Integer songId) {
        this.songId = songId;
    }

    public String getSongName() {
        return songName;
    }

    public void setSongName(String songName) {
        this.songName = songName;
    }

    public Integer getSongAlbumId() {
        return songAlbumId;
    }

    public void setSongAlbumId(Integer songAlbumId) {
        this.songAlbumId = songAlbumId;
    }

    public String getSongDuration() {
        return songDuration;
    }

    public void setSongDuration(String songDuration) {
        this.songDuration = songDuration;
    }

    public Album getAlbum() {
        return album;
    }

    public void setAlbum(Album album) {
        this.album = album;
    }
}

現在你可以alubm_id通過song物體獲得song.getAlbum().getAlbumId()

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/429140.html

標籤：爪哇 PostgreSQL 弹簧靴 jpa crud 存储库

上一篇：如何在測驗中覆寫單個應用程式屬性

下一篇：為什么spring-boot-starter-logging需要依賴log4-to-slf4j