Spring Boot+H2导入Excel后外键填充及重复空行问题

背景与问题 #

我搭建了基于Spring Boot、Java 17、H2数据库的测试环境，需要将两个Excel表导入数据库并建立一对一关联，实现外键自动填充。

• 初始问题：通过Postman导入数据后，外键字段显示为null
• 更新后问题：修改Service代码后外键成功填充，但数据库中出现与导入行数一致的空值重复行

相关代码 #

实体类 #

SocialProfile.java

@Entity
public class SocialProfile {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String profilename;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "SOCIAL_USER_ID", referencedColumnName = "id")
    private SocialUser user;

    public SocialProfile() {
    }

    public Long getId() {
        return id;
    }

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

    public String getProfilename() {
        return profilename;
    }

    public void setProfilename(String profilename) {
        this.profilename = profilename;
    }

    public SocialUser getUser() {
        return user;
    }

    public void setUser(SocialUser user) {
        this.user = user;

    }

}

SocialUser.java

@Entity
public class SocialUser {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String username;

    @OneToOne(mappedBy = "user", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private SocialProfile socialProfile;

    public SocialUser(Long id, String username, SocialProfile socialProfile) {
        super();
        this.id = id;
        this.username = username;
        this.socialProfile = socialProfile;
    }

    public SocialUser() {
    }

    public Long getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public SocialProfile getSocialProfile() {
        return socialProfile;
    }

    public void setSocialProfile(SocialProfile socialProfile) {
        socialProfile.setUser(this);
        this.socialProfile = socialProfile;
    }
}

Excel解析类 Excel.java #

public class Excel {
    
    public static boolean isValidExcelFile(MultipartFile file) {
        return Objects.equals(file.getContentType(),
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    }

    public static List<SocialProfile> getCustomersDataFromExcel(InputStream inputStream) {
        List<SocialProfile> customers = new ArrayList<>();
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheet("socialprofile");
            int rowIndex = 0;
            for (Row row : sheet) {
                if (rowIndex == 0) {
                    rowIndex++;
                    continue;
                }
                Iterator<Cell> cellIterator = row.iterator();
                int cellIndex = 0;
                SocialProfile customer = new SocialProfile();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    switch (cellIndex) {
                    case 0 -> customer.setProfilename(cell.getStringCellValue());   
                    default -> {
                    }
                    }
                    cellIndex++;
                }
                customers.add(customer);
            }
        } catch (IOException e) {
            e.getStackTrace();
        }
        return customers;
    }
    
    
    public static List<SocialUser> getUserDataFromExcel(InputStream inputStream) {
        List<SocialUser> customers = new ArrayList<>();
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheet("socialuser");
            int rowIndex = 0;
            for (Row row : sheet) {
                if (rowIndex == 0) {
                    rowIndex++;
                    continue;
                }
                Iterator<Cell> cellIterator = row.iterator();
                int cellIndex = 0;
                SocialUser customer = new SocialUser();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    switch (cellIndex) {
                    case 0 -> customer.setUsername(cell.getStringCellValue());

                    default -> {
                    }
                    }
                    cellIndex++;
                }
                customers.add(customer);
            }
        } catch (IOException e) {
            e.getStackTrace();
        }
        return customers;
    }

}

Repository接口 #

SocialProfileRepo.java

@Repository
public interface SocialProfileRepo extends JpaRepository<SocialProfile, Integer> {

}

SocialUserRepo.java

@Repository
public interface SocialUserRepo extends JpaRepository<SocialUser, Integer>{

}

初始Service代码 #

SocialProfileService.java

@Service
public class SocialProfileService {

    @Autowired
    private SocialProfileRepo customerRepo;

    public void importCustomerToDatabase(MultipartFile file) {
        try {
            List<SocialProfile> custList = Excel.getCustomersDataFromExcel(file.getInputStream());
            customerRepo.saveAll(custList);
        } catch (IOException ex) {
            throw new RuntimeException("Data is not stored successfully: " + ex.getMessage());
        }

    }

}

SocialUserService.java

@Service
public class SocialUserService {

    @Autowired
    private SocialUserRepo customerRepo;

    public void importUserToDatabase(MultipartFile file) {
        try {
            List<SocialUser> custList = Excel.getUserDataFromExcel(file.getInputStream());
            customerRepo.saveAll(custList);
        } catch (IOException ex) {
            throw new RuntimeException("Data is not stored successfully: " + ex.getMessage());
        }

    }

}

Controller代码 #

SocialProfileController.java

@RestController
public class SocialProfileController {
    
    @Autowired
    SocialProfileService customerService;

    @PostMapping("/customer/excel/upload")
    public ResponseEntity<String> uploadFile(@RequestParam("file") MultipartFile file) {
        String message = "";
        if (Excel.isValidExcelFile(file)) {
            try {
                customerService.importCustomerToDatabase(file);
                message = "The Excel file is uploaded: " + file.getOriginalFilename();
                return ResponseEntity.status(HttpStatus.OK).body(message);
            } catch (Exception exp) {
                message = "The Excel file is not uploaded: " + file.getOriginalFilename();
                return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(message);
            }
        }
        message = "Please upload an excel file!";
        return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(message);
    }

}

SocialUserController.java

@RestController
public class SocialUserController {

    @Autowired
    SocialUserService customerService;

    @PostMapping("/user/excel/upload")
    public ResponseEntity<String> uploadFile(@RequestParam("file") MultipartFile file) {
        String message = "";
        if (Excel.isValidExcelFile(file)) {
            try {
                customerService.importUserToDatabase(file);
                message = "The Excel file is uploaded: " + file.getOriginalFilename();
                return ResponseEntity.status(HttpStatus.OK).body(message);
            } catch (Exception exp) {
                message = "The Excel file is not uploaded: " + file.getOriginalFilename();
                return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(message);
            }
        }
        message = "Please upload an excel file!";
        return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(message);
    }
}

更新后的Service代码（出现重复空行问题） #

@Service
public class SocialService {

@Autowired
SocialUserRepo socialUserRepo;

@Autowired
SocialProfileRepo socialProfileRepo;

public void importUserToDatabase(MultipartFile file) {
    try {
        List<SocialUser> custList = Excel.getUserDataFromExcel(file.getInputStream());

        for (SocialUser user : custList) {
            SocialProfile scProfile = new SocialProfile();
            user.setSocialProfile(scProfile);
        }

        socialUserRepo.saveAll(custList);
    } catch (IOException ex) {
        throw new RuntimeException("Data is not stored successfully: " + ex.getMessage());
    }

}

public void importCustomerToDatabase(MultipartFile file) {
    try {
        List<SocialProfile> custList = Excel.getCustomersDataFromExcel(file.getInputStream());

        for (SocialProfile profile : custList) {
            SocialUser scUser = new SocialUser();
            profile.setUser(scUser);
        }

        socialProfileRepo.saveAll(custList);

    } catch (IOException ex) {
        throw new RuntimeException("Data is not stored successfully: " + ex.getMessage());
    }

}

数据库现象 #

• 初始状态：导入后外键字段为null
• 更新后状态：外键成功填充，但数据库中新增了与导入行数一致的空值重复记录

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