Spring JPA保存父对象时子对象未保存的问题排查与解决

问题背景 #

我有一个包含员工和项目信息的CSV文件（每个员工对应多个项目）。上传该文件后，解析CSV并构建Employee（父）与Project（子）的关联关系，然后保存到数据库，但过程中遇到数据库约束错误。

实体类代码 #

Employee 实体类 #

@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) //尝试过但无效
    @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;
    }
}

Project 实体类 #

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

保存数据的代码 #

@Transactional
public void uploadEmployees(MultipartFile file) {
    try {
//调用CSV工具类完成解析，转换为Employee模型对象格式
        Set<Employee> employees = CSVHelper.csvToEmployees(file.getInputStream(), "2");
        employeeRepository.saveAll(employees);
        System.out.println("已将CSV所有记录转换为Java对象并处理完成");
    } catch (IOException e) {
        throw new RuntimeException(e);
    }catch (Exception e ){
        throw new RuntimeException(e.getMessage());
    }
}

遇到的错误 #

错误1：员工ID非空约束违反 #

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).

错误2：修改ID生成策略后的异常 #

将@GeneratedValue(strategy = GenerationType.IDENTITY)改为@GeneratedValue(strategy = GenerationType.AUTO)后，出现以下异常：

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)

调试观察到的对象状态 #

执行saveAll方法前，员工对象的结构如下：

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)
            ]
    )

当时认为employee=null是合理的，因为对象还未保存到数据库。

更新：CSV解析代码 #

添加解析CSV并构建关联对象的代码：

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;
        //遍历列表，为新的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")); //用于检查唯一emp id
                projects = new ArrayList<>();
            }
            Project project = new Project(csvRecord.get("NAME"),
                    csvRecord.get("CODE"),
                    formatToInstant(csvRecord.get("TIME")) );
            project.setEmployee(employee);//在此处为项目设置关联的员工
            projects.add(project);
            employee.setProjects(projects); //为员工设置项目列表
            employees.add(employee);
        }
        return employees;
    } catch (IOException e) {
        throw new RuntimeException("解析CSV文件失败: " + e.getMessage());
    }
}

问题解决 #

通过在CSV解析过程中为每个Project对象设置关联的Employee，解决了非空约束错误：

project.setEmployee(employee);

内容的提问来源于stack exchange，提问作者nani neelam