My repository interfaces:
SongsRepository:
public interface SongRepository extends CrudRepository<Song, Integer> {
}
AlbumsRepository
public interface AlbumRepository extends CrudRepository<Album, Integer> {
}
My Model classes:
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;
}
}
My Controller:
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 class:
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;
}
}
My View Models:
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;
}
}
Logged SQL query:
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:
ERROR: column song0_.album_album_id does not exist
My Database Schema (PostgreSQL 12.10.1)
This is my first time using Spring Boot and JPA. What have I done wrong here?
From the error message, I am guessing that it is trying to find a column called album_album_id which does not exist in my database and as a result is unable to find it; but why is it searching for that particular column? (My guess is I've messed up the relationship attributes).
CodePudding user response：
Your Song entity class should be changed like follows.
@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 = "BANK_CODE", 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;
}
}
now you can get alubm_id by song enntity using song.getAlbum().getAlbumId()
CodePudding user response：
Your mapping is wrong. Song should look like this:
@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;
}
and Album like this:
@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;
}
By the way,
- it's a little strange to have song duration formatted as String and album duration as Integer.
- if you're willing to go from CrudRepository to JpaRepository, you may want to use Spring data projection instead of manually recasting your song to SongViewModels
Here's a little example of interface projection for your question