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;
}
}