0

I read a CSV file with university courses (11000 rows) and want to insert this using hibernate into a MySQL database. The problem is that hibernate inserts each row twice. There is a bug regarding this issue. Solution should be to flush each child after persisting. See here: Hibernate inserts duplicates into a @OneToMany collection and https://hibernate.atlassian.net/browse/HHH-6776 Here is the code from my Spring applications controller:

@RequestMapping(value = "/readcsv")
public String readCSVFile() {

    entityManager.setFlushMode(FlushModeType.COMMIT);

    //Load all courses and universities and add to hashmap for fast lookup.
    ArrayList<Course> allCourses = (ArrayList) entityManager.createQuery("FROM Course").getResultList();

    MultiValueMap courseMap = MultiValueMap.decorate(new HashMap<String, Course>());

    for (Course c : allCourses) {
        courseMap.put(c.getName(), c);
    }

    ArrayList<University> allUniversities = (ArrayList) entityManager.createQuery("FROM University").getResultList();

    HashMap<String, University> universityMap = new HashMap<String, University>();

    for (University u : allUniversities) {
        universityMap.put(u.getName(), u);
    }

    Country sweden = (Country) entityManager.createQuery("SELECT c FROM Country c WHERE c.name = 'Sweden'").getSingleResult();

    ArrayList<Course> coursesToAdd = new ArrayList<Course>();

    //Set fixed parameters for this specific dataset.
    String filename = "/Volumes/320gb/macbookpro/Documents/AntagningsstatistikVT2015.csv";
    String semester = "Spring2015";
    String type = "Classroom";
    int pace  = 100;
    University u;

    try {

        //Read file
        File fileDir = new File(filename);
        BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(fileDir), "UTF-8"));

        String line;
        //Loop lines in file
        while ((line = in.readLine()) != null) {

            // use comma as separator
            String[] courseLine = line.split(",");

            String name = courseLine[1];
            String university = courseLine[2];

            //See if the course is already in the database
            Collection<Course> coursesWithSameName = courseMap.getCollection(name);
            boolean found = false;
            if (coursesWithSameName != null) {
                for (Course course: coursesWithSameName) {
                    if (course.getUniversity().getName().equals(university)) {
                        System.out.println("COURSE ALREADY IN DB : " + name + " " + university);
                        found = true;
                    }
                }
            }

            //If not, insert it
            if (found == false) {
                if (!universityMap.containsKey(university)) {
                    u = new University();
                    u.setName(university);
                    u.setCountry(sweden);
                    u.setUserAdded(false);
                    universityMap.put(u.getName(), u);
                    entityManager.persist(u);
                    entityManager.flush();
                } else {
                    u = universityMap.get(university);
                }

                Course course = new Course();
                course.setName(name);
                course.setUniversity(u);
                course.setType(type);
                course.setPace(pace);
                course.setSemester(semester);

                courseMap.put(course.getName(), course);
                coursesToAdd.add(course);
            }
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (UnsupportedEncodingException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    System.out.println("NUMBER OF ROWS IN coursesToAdd : " + coursesToAdd.size()); //Correctly prints 11000

    for (Course course : coursesToAdd) { //inserts 22000 rows to db.
        entityManager.persist(course);
        entityManager.flush();
    }

    return "redirect:/";
}

Even though I flush after each persist double rows gets inserted. I have the @Transactional annotation on the controller class, is this the reason flush doesn't solve the problem? It however seems much slower when having flush there, which seems like it runs correctly compared to when it does a bulk insert on the end without flushing each insert.

Someone have a clue what is wrong in my code?

Thank you!

Edit:

Added University and Course entities:

Course:

import com.courseportal.project.account.Account;
import com.courseportal.project.utils.AbstractTimestampEntity;
import org.hibernate.annotations.Cascade;

import java.io.Serializable;
import java.util.List;

import javax.persistence.*;


@Entity
@Table(name="courses")
    public class Course extends AbstractTimestampEntity implements Serializable {

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

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

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

    @OneToMany(cascade = CascadeType.ALL)
    @OrderBy("date desc")
    private List<CourseRank> ranks;

    @OneToMany(cascade = CascadeType.ALL)
    @OrderBy("date desc")
    private List<Comment> comments;

    @OneToMany(cascade = CascadeType.ALL)
    @OrderBy("date desc")
    private List<UserBook> userBooks;

    @ElementCollection
    private List<CourseBook> courseBooks;

    @ManyToMany
    @JoinTable(name="courses_enrolledStudents")
    private List<Account> enrolledStudents;

    @OneToMany(cascade = CascadeType.ALL)
    private List<Timeslot> times;

    @ManyToOne
    private University university;

    /*
     * fall
     * spring
     * summer
     */
    private String semester;

    private String teacherName;

    private String courseLink;

    private String requirementsLink;

    /**
     * Undergraduate
     * Graduate
     */
    private String level;

    /**
     * Online (MOOC - Massive Open Online Courses)
     * Classroom (Traditional)
     * Mixed (Lab)
     */
    private String type;

    /**
     * How fast the course going in percentage.
     */
    private int pace;

    private int numberOfAssignments;

    private int numberOfProjects;

    private int numberOfExams;

    private double credits;

    public long getId() {
        return id;
    }

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

    public University getUniversity() {
        return university;
    }

    public void setUniversity(University university) {
        this.university = university;
    }

    public String getCourseLink() {
        return courseLink;
    }

    public void setCourseLink(String courseLink) {
        this.courseLink = courseLink;
    }

    public String getRequirementsLink() {
        return requirementsLink;
    }

    public void setRequirementsLink(String requirementsLink) {
        this.requirementsLink = requirementsLink;
    }

    public String getLevel() {
        return level;
    }

    public void setLevel(String level) {
        this.level = level;
    }

    public List<Timeslot> getTimes() {
        return times;
    }

    public void setTimes(List<Timeslot> times) {
        this.times = times;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public int getPace() {
        return pace;
    }

    public void setPace(int pace) {
        this.pace = pace;
    }

    public String getTeacherName() {
        return teacherName;
    }

    public void setTeacherName(String teacherName) {
        this.teacherName = teacherName;
    }

    public List<Comment> getComments() {
        return comments;
    }

    public void setComments(List<Comment> comments) {
        this.comments = comments;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<CourseRank> getRanks() {
        return ranks;
    }

    public void setRanks(List<CourseRank> ranks) {
        this.ranks = ranks;
    }

    public void addRank(CourseRank rank) {
        this.ranks.add(rank);
    }

    public void removeRank(CourseRank rank) {
        this.ranks.remove(rank);
    }

    public void addComment(Comment comment) {
        comments.add(comment);
    }

    //User books, books that are for sale getters and setters

    public void removeUserBook(UserBook book) {
        this.userBooks.remove(book);
    }

    public void addUserBook(UserBook book) {
        this.userBooks.add(book);
    }

    public List<UserBook> getUserBooks() {
        return userBooks;
    }

    public void setUserBooks(List<UserBook> userBooks) {
        this.userBooks = userBooks;
    }

    public List<CourseBook> getCourseBooks() {
        return courseBooks;
    }

    public void setCourseBooks(List<CourseBook> courseBooks) {
        this.courseBooks = courseBooks;
    }

    public void removeCourseBook(CourseBook book) {
        this.courseBooks.remove(book);
    }

    public void addCourseBook(CourseBook book) {
        this.courseBooks.add(book);
    }

    public int getNumberOfAssignments() {
        return numberOfAssignments;
    }

    public void setNumberOfAssignments(int numberOfassignments) {
        this.numberOfAssignments = numberOfassignments;
    }

    public int getNumberOfProjects() {
        return numberOfProjects;
    }

    public void setNumberOfProjects(int numberOfprojects) {
        this.numberOfProjects = numberOfprojects;
    }

    public int getNumberOfExams() {
        return numberOfExams;
    }

    public void setNumberOfExams(int numberOfexams) {
        this.numberOfExams = numberOfexams;
    }

    public double getCredits() {
        return credits;
    }

    public void setCredits(double credits) {
        this.credits = credits;
    }

    public List<Account> getEnrolledStudents() {
        return enrolledStudents;
    }

    public void setEnrolledStudents(List<Account> enrolledStudents) {
        this.enrolledStudents = enrolledStudents;
    }

    public void addEnrolledStudent(Account student) {
        this.enrolledStudents.add(student);
    }

    public void removeEnrolledStudent(Account student) {
        this.enrolledStudents.remove(student);
    }

    public String getSemester() {
        return semester;
    }

    public void setSemester(String semester) {
        this.semester = semester;
    }
}

And University:

import com.courseportal.project.utils.AbstractTimestampEntity;

import java.io.Serializable;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name="universities")
public class University extends AbstractTimestampEntity implements Serializable {

    @Id
    @GeneratedValue
    private int id;

    private String name;

    @ManyToOne
    private Country country;

    private boolean userAdded;

    public University() {}

    public University(String university) {
        name = university;
    }

    public int getId() {
        return id;
    }

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

    public Country getCountry() {
        return country;
    }

    public void setCountry(Country country) {
        this.country = country;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public boolean isUserAdded() {
        return userAdded;
    }

    public void setUserAdded(boolean userAdded) {
        this.userAdded = userAdded;
    }

}
Community
  • 1
  • 1
nilsi
  • 10,351
  • 10
  • 67
  • 79
  • Having this code in a controller which is transactional is a bad thing imho, the web should call a service layer method, itself should be a thin layer. But that isn't related to your problem. Make sure your objects have proper equals and hashcode methods and make sure you are managing the relationships correctly. Please add your entities to your question. – M. Deinum Dec 19 '14 at 06:22
  • Yes I know this is bad practise, I will change it in the future. But right know I have no clue what causes this problem which is really frustrating. I updated the question with the entities. Tell me if you need more code, thank you. – nilsi Dec 19 '14 at 06:40
  • Sometimes it inserts the right amount of rows but cant really figure out when. When using a smaller dataset it happens more often. When starting the application with smaller dataset it always inserts duplicates fist time. But after removing all courses from database and reading the file again without restarting the app it inserts the right amount. hum, this is strange. – nilsi Dec 19 '14 at 07:24
  • Is there a reason you collect them and then persist them? You shouldn't flush as that will happen at the end of the transaction. You are also meddling with the flush mode of the `EntityManager` also something you shouldn't need to do. You are reading a cvs file and write to a database you might want to take a look at Spring Batch which can help you streamline this process. – M. Deinum Dec 19 '14 at 09:03
  • Ah thank you. Collecting and then persisting is just because I wanted to test how many I actually have and how many it persists. I fiddling with the entity manager just to see if it can solve the problem, which it didn't (I have removed all this now). I also removed the flushing. Spring Batch seems very interesting, didn't know about that but will take a look. – nilsi Dec 19 '14 at 09:09

0 Answers0