I am having a spring boot application which reads values from the Microsoft access file and then checks if the value exists in the database then gets updated or else inserted as the new record however I'm only getting these values present in the database and I'm not able to get the values which are missing from the database I don't know where I'm doing it wrong but here is the code I have
What I have tried:
Model Class
@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "tbl_tests")
public class Test {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;
private String customId;
private String testTypeId;
private LocalDateTime testDate;
private LocalDateTime resultDate;
private Float resultNumeric;
}
Repository
@Repository
public interface TestRepository extends JpaRepository<Test, Long> {
@Query(value = "select t from Test t where t.customId =:customId and t.testDate =:testDate and t.testTypeId =:testType")
Test findByTestParams(String customId, LocalDateTime testDate, String testType);
}
service class
@Service
public class TestProcessingService {
@Autowired
private TestRepository testRepository;
@Async
public void processData(Path filepath) throws IOException, SQLException {
List<Test> testList = new ArrayList<>();
Table table = DatabaseBuilder.open(new File(filepath.toString())).getTable("tblTests");
for(Row row : table) {
Test dbData = testRepository.findByTestParams(row.get("CustomID"), (LocalDateTime) row.get("TestDate"), (String) row.get("TestTypeID"));
if(dbData == null) {
Test test = new Test();
test.setCustomId(row.get("CustomID"));
test.setTestTypeId((String) row.get("TestTypeID"));
test.setTestDate((LocalDateTime) row.get("TestDate"));
test.setResultNumeric((Float) row.get("ResultNumeric"));
testList.add(test);
} else {
dbData.setCustomId(row.get("CustomID"));
dbData.setTestTypeId((String) row.get("TestTypeID"));
dbData.setTestDate((LocalDateTime) row.get("TestDate"));
dbData.setResultNumeric((Float) row.get("ResultNumeric"));
testList.add(dbData);
}
}
testRepository.saveAll(testList);
}
}
Api Class
@RestController
@RequestMapping("/data_api")
public class UploadApi {
@Autowired
private DataUploadService dataUploadService;
@PostMapping("/upload_file")
protected UploadResponse uploadFile(@RequestParam MultipartFile file) throws IOException,SQLException {
return dataUploadService.uploadFile(file);
}
}
I'm not able to get the null values so that I can insert them as a new record, I'm only getting the values that are present inside the database. Any suggestions will be much appreciated.
Also there is more than one customId present in table test.