kumuda
kumuda

Reputation: 509

How to perform inner join 3 classes in hibernate

Album,Genre,User classes as below. user class has a userId. I want to list all the songs belonging to a particular user. For this i used this query:

String hql = "select song.title,song.artist,song.rating,song.composer,album.albumName,genre.genreName from model.Song song"+
                     "inner join song.Album as album"+
                     "inner join song.Genre as genre"+
                     "inner join model.Login login"+
                     "where login.userId= :userId";

Album and Genre are mapped to Song class.I am not clear on how to relate Song class and Login class.

 @Entity
@Table(name = "songs")
public class Song implements Serializable {


@Id
@GeneratedValue
@Column(name = "song_id")
private int songId;

@OneToOne(cascade=CascadeType.ALL)
@JoinTable(name = "albums_songs_mapping", joinColumns = @JoinColumn(name = "song_id", referencedColumnName = "song_id"), inverseJoinColumns = @JoinColumn(name = "album_id", referencedColumnName = "album_id"))
private Album album;

@Column(name = "title", nullable = false)
private String title;

@Column(name = "rating")
private int rating;

@Column(name = "artists")
private String artist;

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

@OneToOne(cascade=CascadeType.ALL)
@JoinTable(name = "genre_songs_mapping", joinColumns = @JoinColumn(name = "song_id",      
referencedColumnName = "song_id"), inverseJoinColumns = @JoinColumn(name = "genre_id", 
referencedColumnName = "genre_id"))
private Genre genre;
//getters and setters
}

@Entity
@Table(name = "albums")
public class Album {

@Id
@GeneratedValue
@Column(name = "album_id")
private int albumId;

@Column(name = "album_name", length = 250)
private String albumName;

@OneToMany(cascade=CascadeType.ALL) 
@JoinTable(name = "albums_songs_mapping", joinColumns = @JoinColumn(name = "album_id",   
referencedColumnName = "album_id"), inverseJoinColumns = @JoinColumn(name = "song_id", 
referencedColumnName = "song_id"))
private List<Song> songs;

//getters and setters
}


@Entity
@Table(name = "genre")
public class Genre {

@Id
@GeneratedValue
@Column(name = "genre_id")
private int genreId;

@Column(name = "genre_name", length = 250)
private String genreName;

@OneToMany(cascade=CascadeType.ALL) 
@JoinTable(name = "genre_songs_mapping", joinColumns = @JoinColumn(name = "genre_id", 
referencedColumnName = "genre_id"), inverseJoinColumns = @JoinColumn(name = "song_id", 
referencedColumnName = "song_id"))
private List<Song> songs;
//getters and setters
}

@Entity
@Table(name = "login", uniqueConstraints = {@UniqueConstraint(columnNames = 
{"user_name"})})
public class Login {

@Id
@GeneratedValue
@Column(name = "user_id")
private int userId;

@Column(name = "user_name", nullable=false )
private String userName;

@Column(name = "password", nullable=false )
private String password;

@ManyToMany
@JoinTable(name = "user_songs_mapping", joinColumns = @JoinColumn(name = " user_id", 
referencedColumnName = "user_id"), inverseJoinColumns = @JoinColumn(name = "song_id", 
referencedColumnName = "song_id"))
private List<Song> songs;
//getters and setters
}

Upvotes: 0

Views: 228

Answers (1)

Predrag Maric
Predrag Maric

Reputation: 24433

It's easier if you start from Login. Try this:

String hql = "select song.title, song.artist, song.rating, song.composer, album.albumName, genre.genreName " 
    + "from Login l join l.songs song "
    + "join song.genre genre " 
    + "join song.album album " 
    + "where l.userId= :userId";

Upvotes: 3

Related Questions