Using Java, update an exisitng SQL table row from Excel Sheet

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 msn", (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 ‘(‘.

Answer

The answer above solved half of my issues. The other half of the issue was the update statement. Should be String sql = "UPDATE client SET monthlyMinimum = ? , oliSetUpFee= ? WHERE clientId = ?";

Advertisement

Answer