Writing model class for song table : Spring boot, postgres

Question

Here is my table design

**Songs

---

Movie Id - integer 
[year] - integer
Song name - character
Set Singers(list) - text
Lyrics writer(list) -text
Length - numeric(3,2)**

I am developing a rest service using spring boot . I tried to implement a pojo where I am not getting good result.

here is my developed model class

@Entity
public class SongsInfo {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long id;
    int year;
    @ElementCollection
    List<String> singers;
    @ElementCollection
    List<String> lyricists;
    float length;



    public SongsInfo() {
    }



    public int getYear() {
        return year;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public void setYear(int year) {
        this.year = year;
    }

    public List<String> getSingers() {
        return singers;
    }

    public void setSingers(List<String> singers) {
        this.singers = singers;
    }

    public List<String> getLyricists() {
        return Lyricists;
    }

    public void setLyricists(List<String> lyricists) {
        Lyricists = lyricists;
    }

    public float getLength() {
        return length;
    }

    public void setLength(float length) {
        this.length = length;
    }

   /* public MoviesInfo getMoviesInfo() {
        return moviesInfo;
    }

    @Access(AccessType.PROPERTY)
    @ManyToOne
    @JoinColumn(name = "movieId")
    public void setMoviesInfo(MoviesInfo moviesInfo) {
        this.moviesInfo = moviesInfo;
    }*/
}

Please help me to write a model class where I can implement all the CRUD operations .

My expectaion of data in the table:

MovieId year    Song name        setofsingers        lyricists        length
CA1532  2015    adiga adiga      hemachandra, roopa  ram,sirivennala  5:46

Answer 1

If your expectation is a column you should not be using @ElementCollection as it will create one table each. (songs_info, songs_info_lyricists, songs_info_singers).

You can use a model like this, where you save a simple column but then retrieving a List and removing the get and set for simple singers and liricists:

The model:

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "songs_info")
public class SongsInfo {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long    id;

    private int year;

    @Column
    private String singers;

    @Column
    private String lyricists;

    private float length;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public int getYear() {
        return year;
    }

    public void setYear(int year) {
        this.year = year;
    }

    public List<String> getSingersList() {
        if(this.singers != null){
            return Arrays.asList(this.singers.split(","));          
        }else
            return new ArrayList<String>();
    }

    public void setSingersList(List<String> singersList) {
        this.singers = String.join(",", singersList);
    }

    public List<String> getLyricistsList() {
        if(this.lyricists != null){
            return Arrays.asList(this.lyricists.split(","));            
        }else
            return new ArrayList<String>();
    }

    public void setLyricistsList(List<String> lyricistsList) {
        this.lyricists = String.join(",", lyricistsList);
    }

    public float getLength() {
        return length;
    }

    public void setLength(float length) {
        this.length = length;
    }
}

The repo :

import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;
import org.springframework.web.bind.annotation.CrossOrigin;

    import com.atos.travel.models.SongsInfo;

    @RepositoryRestResource(collectionResourceRel = "songs", path = "songs")
    @CrossOrigin("*")
    public interface SongsRepository extends PagingAndSortingRepository<SongsInfo, Long> {

    }

Then your post should be like that:

{ "length": "5.39", "lyricistsList": ["ram","Hemanth" ], "singersList": [ "hema","roopa" ], "year": 2005} 

Answer 2

You can do it like this:

@SpringBootApplication
public class So45179111Application {

    public static void main(String[] args) {
        SpringApplication.run(So45179111Application.class, args);
    }

    public static class ListToCommaTextConverter implements AttributeConverter<List<String>, String> {

        @Override
        public String convertToDatabaseColumn(List<String> attribute) {
            if (attribute == null || attribute.isEmpty()) {
                return null;
            }

            return attribute.stream().collect(Collectors.joining(","));
        }

        @Override
        public List<String> convertToEntityAttribute(String dbData) {
            if (dbData == null || dbData.isEmpty()) {
                return Collections.emptyList();
            }
            return Stream.of(dbData.split(",")).collect(Collectors.toList());
        }
    }

    @Entity
    @Table(name = "Movies")
    public static class MoviesInfo extends AbstractPersistable<Long> {
        private String name;
        // getters and setters
    }

    @Entity
    @Table(name = "Songs")
    public static class SongsInfo extends AbstractPersistable<Long> {
        @Column(columnDefinition = "text")
        @Convert(converter = ListToCommaTextConverter.class)
        private List<String> singers;

        @Column(columnDefinition = "text")
        @Convert(converter = ListToCommaTextConverter.class)
        private List<String> lyricists;

        @Column(columnDefinition = "numeric(3,2)")
        private float length;

        private int year;

        @ManyToOne
        @JoinColumn(name = "movie_id", foreignKey = @ForeignKey(name = "Songs_Movies_fk1"))
        private MoviesInfo movie;

        // getters and setters
    }

    @RestController
    @RequestMapping("/songs")
    public static class SongsApi {

        private final SongsInfoRepository songsInfoRepository;

        @Autowired
        public SongsApi(SongsInfoRepository songsInfoRepository) {
            this.songsInfoRepository = songsInfoRepository;
        }

        @PostMapping
        public SongsInfo store(@RequestBody SongsInfo songsInfo) {
            return songsInfoRepository.save(songsInfo);
        }
    }
}

interface SongsInfoRepository extends CrudRepository<So45179111Application.SongsInfo, Long> {}

And you can use your request:

$ curl -XPOST -H'Content-Type: application/json' -d '{ "length": 5.39,  "lyricists": [ "ram","Hemanth" ],  "singers": [ "hema","roopa" ],  "year": 2005 }' localhost:8080/songs
{"id":3,"singers":["hema","roopa"],"lyricists":["ram","Hemanth"],"length":5.39,"year":2005,"movie":null,"new":false}%