How to import a CSV file to MSSQL using Springboot-CodePudding

I am working on a project that has a function that allows users to import a file (Excel or CSV) to DB (MsSQL). I have read tutorials on the internet and followed them, but the problem is one of my entities contains an object.

@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@Entity
@Table(name = "question_bank")
public class QuestionBank implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;

@Column
private String content;

@Column
private String explanation;

@Column
private String option1;

@Column
private String option2;

@Column
private String option3;

@Column
private String option4;

@Column
private String answer;

@ManyToOne(fetch = FetchType.LAZY, cascade=CascadeType.ALL)
@JoinColumn(name = "status_id")
private Status status;

@ManyToOne(fetch = FetchType.LAZY, cascade=CascadeType.ALL)
@JoinColumn(name = "levelId")
private QuizLevel quizLevel;

The status_id and levelId are from joining other columns of other tables. And this is the code that I use to set the data from Excel file

        questionBank.setAnswer(excelData.get(i));
        questionBank.setContent(excelData.get(i   1));
        questionBank.setExplanation(excelData.get(i   2));
        questionBank.setOption1(excelData.get(i   3));
        questionBank.setOption2(excelData.get(i   4));
        questionBank.setOption3(excelData.get(i   5));
        questionBank.setOption4(excelData.get(i   6));


        questionBank.setStatus(Integer.valueOf(excelData.get(i   8)));
        questionBank.setCourse(Integer.valueOf(excelData.get(i   9)));
        questionBank.setQuizLevel(Integer.valueOf(excelData.get(i   10)));

The IDE said the last 3 lines, setStatus, setCourse and setQuizLevel are errors because there are no functions like that in Entity QuestionBank.

How can I do this import, thank you if you are reading and have a solution for me?

CodePudding user response：

For the last object I guess you will have to construct an instance of QuizLevel, fill it with values then pass it to your setter.

I do not know how your csv is structured, but if you isolate the values related to QuizLevel then pass it to your QuestionBank instance;

QuizLevel quizLevel= new QuizLevel();
quizLevel.setValue(myValueFromCsv)
quizLevel.setOtherValue(myOtherValueFromCSV)
questionBank.setQuizLevel(quizLevel);

Same goes for setCourse and setStatus.

CodePudding user response：

You have to make the instance of your Status and Quizlevel object, and after that, you can access or set the values accordingly of these objects. You can't simplily set the value to a object. First create instance of object then the set or get the values associated with that object. This example might help you properly:

 Post post = new Post();
        User user = userServiceImpl.getCurrentUser();

        post.setTitle(title);
        post.setContent(content);
        post.setCreatedAt(time);
        post.setAuthor(user.getName());
        post.setPublishedAt(time);
        post.setUpdatedAt(time);
        post.setExcerpt(content);
        post.setIsPublished(true);
        post.setAuthorId(user.getId());

        String[] tagsArray = tags.split(" ");
        List<Tag> tagList = new ArrayList<Tag>();
        for (String tag : tagsArray) {
            Tag tagObject = new Tag();

            if (tagService.checkTagWithName(tag)) {
                tagList.add(tagService.getTagByName(tag));
            } else {
                tagObject.setName(tag);
                tagObject.setCreatedAt(time);
                tagObject.setUpdatedAt(time);
                tagList.add(tagObject);
            }
        }
        post.setTags(tagList);
        postService.savePost(post);

This is my model class for post with a tag object in last:

@Table(name = "posts")
public class Post {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

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

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

    @Column(name = "content",columnDefinition="TEXT")
    private String content;

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

    @Column(name = "published_at")
    private Timestamp publishedAt;

    @Column(name = "is_published")
    private Boolean isPublished;

    @Column(name = "created_at")
    private Timestamp createdAt;

    @Column(name = "updated_at")
    private Timestamp updatedAt;

    @Column(name = "authorId")
    private Long authorId;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "post_tags",
            joinColumns = {@JoinColumn(name = "post_id" , referencedColumnName = "id")},
            inverseJoinColumns = {@JoinColumn(name = "tag_id", referencedColumnName = "id")})
    private List<Tag> tags;