I have a csv file that has a list of employees and projects information (each employee having multiple projects). I upload the file, parse the csv and build a parent-child relationship -> Employee and Projects respectively and save it to Database.
Below is my Employee Entity class (I tried the commented part but no luck)
@Entity
@Table(name = "employees")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@NotNull
private String name;
@NotNull
private String empId;
@NotNull
private String department;
//@OneToMany(fetch = FetchType.LAZY, mappedBy = "transcript", cascade = CascadeType.ALL) //tried didn't work
@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, mappedBy = "employee", orphanRemoval = true)
private List<Project> projects;
public Employee(String name, String empId, String department) {
this.name = name;
this.empId = empId;
this.department = department;
}
}
Below is my Project Entity class
@Entity
@Table(name = "projects")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Project {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
private String projectName;
private String projectCode;
private Instant projectStartDate;
@ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.PERSIST)
@JoinColumn(name = "employee_id", nullable = false)
@JsonBackReference
private Employee employee;
public Project(String name, String code, Instant time) {
this.projectName = name;
this.projectCode = code;
this.projectStartDate = time;
}
}
This is where I am trying to save my Employee object:
@Transactional
public void uploadEmployees(MultipartFile file) {
try {
//calls csv helper that does all the parsing and brings into Employee model object format
Set<Employess> employees = CSVHelper.csvToEmployess(file.getInputStream(), "2");
employeeRepository.saveAll(employees);
System.out.println("I processed all the records of cdv into java");
} catch (IOException e) {
throw new RuntimeException(e);
}catch (Exception e ){
throw new RuntimeException(e.getMessage());
}
}
This above method throws :
2022-09-11T22:30:08.438-05:00 ERROR 63321 --- [nio-8086-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: null value in column "employee_id" of relation "projects" violates not-null constraint
Detail: Failing row contains (6, p1, 123, 0184-06-08T01:07:47Z, null).
If I change the id generation type to @GeneratedValue(strategy = GenerationType.AUTO) : I get the below exception:
2022-09-12T00:55:09.465-05:00 WARN 73014 --- [nio-8086-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42P01
2022-09-12T00:55:09.465-05:00 ERROR 73014 --- [nio-8086-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: relation "hibernate_sequence" does not exist
Position: 17
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:174)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
at jdk.proxy2/jdk.proxy2.$Proxy133.saveAll(Unknown Source)
Just before executing saveAll method,I tried looking how my employees list looks like using debugger and it looks as below :
Employee(id=0, name="test", empId="4511504557911789", department="JJ967W"
projects=[
Project(id=0, projectName="p1", projectCode="123", projectStartDate="0184-06-08T01:07:47Z", employee=null),
Project(id=0, projectName="p2", projectCode="345", projectStartDate="0184-06-08T01:07:47Z", employee=null)
]
)
I am thinking employee=null makes sense because it was not saved to DB yet.
Please help me out in what I am missing here that is causing this to break. Thank you very much for taking time in providng help.
**** UPDATED ****
Adding the part where I am adding parsing CSV and building Employee and Project objects
public static Set<Employee> csvToTranscripts(InputStream is) throws IOException {
try (BufferedReader fileReader = new BufferedReader(new InputStreamReader(is, "UTF-8"));
CSVParser csvParser = new CSVParser(fileReader,
CSVFormat.DEFAULT.withFirstRecordAsHeader().withIgnoreHeaderCase().withTrim());) {
Set<Employee> employees = new HashSet<>();
List<CSVRecord> csvRecordsList = csvParser.getRecords();
Employee employee = null;
List<String> empIds = new ArrayList<>();
List<Project> projects = null;
//iterate through list and create project list for new emp_id
for(CSVRecord csvRecord : csvRecordsList){
if(employees.isEmpty() || !empIds.contains(csvRecord.get("EMP_ID"))){
employee = new Employee(
csvRecord.get("NAME"),
csvRecord.get("EMP_ID"),
csvRecord.get("DEPARTMENT")
);
empIds.add(csvRecord.get("EMP_ID")); //this is mainly to check for unique emp id
projects = new ArrayList<>();
}
Project project = new Project(csvRecord.get("NAME"),
csvRecord.get("CODE"),
formatToInstant(csvRecord.get("TIME")) );
project.setEmployee(employee);//setting employee to project here
projects.add(project);
employee.setProjects(projects); //setting projects list to employee here
employees.add(employee);
}
return employees;
} catch (IOException e) {
throw new RuntimeException("fail to parse CSV file: " e.getMessage());
}
}
************ RESOLVED ****************** Thanks everyone for help. Setting employee to project resolved the issue. what a dumb I am !!!
project.setEmployee(employee);
CodePudding user response：
Further to comment by @M.Deinum - declare a method like this in your Employee class and use it to add a project to an employee ensuring the bidirectional relationship is kept in sync
public void addProject(Project project) {
if (projects == null) {
projects = new ArrayList<>();
}
project.setEmployee(this);
projects.add(project);
}
CodePudding user response：
I am thinking employee=null makes sense because it was not saved to DB yet.
This is wrong. If you save an item with bi-directional relationship then you cannot have a case when an orphan Project exists without an Employee. So before calling any CrudRepository.save*() you must make sure that all projects reference some Employee.
Often in such cases when you add projects to employee you do it like:
// method of Employee class
public void addAll(Projects projects) {
for (Project : projects) {
project.setEmployee(this);
}
this.projects.addAll(projects);
}