I am using apache Poi to pull data from an excel sheet that's uploaded to the server. The object is to pull the excel data, which has four columns (clientId, clientName, monthlyMinimum, setUpFee), and update an existing row's monthly minimum & set up fee, based off the clientId, in an existing database table named client. I have this code in a different section of my project to Insert a new client and it works, but am struggling to get it to update an existing one. Can anyone tell me what I am doing wrong with the clientId, where it is not updating these two fields?
@RequestMapping(value="/save/acctDocument")
public String addAcctDoc(Model model, @ModelAttribute(value="newDocument") Document newDocument, @RequestParam("document") MultipartFile file) throws IOException{
// Document doc=documentRepository.save(newDocument);
newDocument.setStorage(storageService.storer(file)); //uploads the doc
//find doc, read doc, save info to db client table
int batchSize = 20;
Connection connection = null;
try {
long start = System.currentTimeMillis();
FileInputStream inputStream = new FileInputStream(appDir+"\\AcctUploadFile.xlsx");
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = firstSheet.iterator();
connection = DriverManager.getConnection(jbdcUrl, dbusername, dbpassword);
connection.setAutoCommit(false);
String sql = "UPDATE client SET (monthlyMinimum, oliSetUpFee) VALUES (?, ?) WHERE clientId = (?)";
PreparedStatement statement = connection.prepareStatement(sql);
int count = 0;
rowIterator.next(); // skip the header row
while (rowIterator.hasNext()) {
Row nextRow = rowIterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();
int columnIndex = nextCell.getColumnIndex();
switch (columnIndex) {
case 1:
long clientId = (long) nextCell.getNumericCellValue();
statement.setLong(3, clientId);
break;
case 2:
float monthlyMinimum = (float) nextCell.getNumericCellValue();
statement.setFloat(1, monthlyMinimum);
break;
case 3:
float setUpFee= (float) nextCell.getNumericCellValue();
statement.setFloat(2, setUpFee);
break;
}
}
statement.addBatch();
if (count % batchSize == 0) {
statement.executeBatch();
}
}
workbook.close();
// execute the remaining queries
statement.executeBatch();
connection.commit();
connection.close();
File acctUploadFile = new File(appDir+"\\AcctUploadFile.xlsx");
acctUploadFile.delete();
long end = System.currentTimeMillis();
System.out.printf("Import done in %d ms\n", (end - start));
} catch (IOException ex1) {
System.out.println("Error reading file");
ex1.printStackTrace();
} catch (SQLException ex2) {
System.out.println("Database error");
ex2.printStackTrace();
}
return "redirect:/reports/";
}
}Update: I updated the code to include the wisdom from one of the comments, but it's still not quite working. I am getting stuck inside the if statement for count modulus batchsize. This is the Database error it is throwing: java.sql.BatchUpdateException: Incorrect syntax near '('.
