Parsing excel sheet to database in java - passing arguments issue

Question

I am reading data from excel table and using java trying to set those values into database. My excel table has 6 columns and 170 rows. I have made class ExcelDataModel and mathod in which I go through excell table, but now I am stuck on how to pass arguments to BatchPreparedStatement. This is my code:

@Override
public void getExcell2() {
     final String PATH = "C:\\Workspace\\zaposlenici.xlsx";         
     try {
        Workbook workbook = WorkbookFactory.create(new File(PATH));             
        Sheet sh = workbook.getSheetAt(0);                                              
        Iterator<Row> rowIterator = sh.iterator();              
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();                                               
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                //how to pass arguments from one row to preparedStatement?

                jdbcTemplate.batchUpdate("INSERT INTO INS_RAZNO.ZAPOSLENICI_TEST VALUES (?, ?, ?, ?, ?, ?)", new BatchPreparedStatementSetter() {
                    @Override                                   
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        ExcelDataModel ex = new ExcelDataModel();
                        ps.setString(1, ex.getIme());
                        ps.setString(2, ex.getPrezime());           
                        ps.setString(3, ex.getOdjel());     
                        ps.setString(4, ex.getSlozenostPosla());            
                        ps.setString(5, ex.getStarost());       
                        ps.setString(6, ex.getMjesecniOD());    
                    }
                    @Override
                    public int getBatchSize() {
                        return 0;       
                    }
                });

             }}


        } catch (EncryptedDocumentException | InvalidFormatException | IOException e) {
        e.printStackTrace();
        }
     }

And here is my ExcelDataModel code:

public class ExcelDataModel {

private String ime;
private String prezime;
private String odjel;
private String slozenostPosla;
private String starost;
private String mjesecniOD;

public String getIme() {
    return ime;
}

public void setIme(String ime) {
    this.ime = ime;
}

public String getPrezime() {
    return prezime;
}

public void setPrezime(String prezime) {
    this.prezime = prezime;
}

public String getOdjel() {
    return odjel;
}

public void setOdjel(String odjel) {
    this.odjel = odjel;
}

public String getSlozenostPosla() {
    return slozenostPosla;
}

public void setSlozenostPosla(String slozenostPosla) {
    this.slozenostPosla = slozenostPosla;
}

public String getStarost() {
    return starost;
}

public void setStarost(String starost) {
    this.starost = starost;
}

public String getMjesecniOD() {
    return mjesecniOD;
}

public void setMjesecniOD(String mjesecniOD) {
    this.mjesecniOD = mjesecniOD;
}

}

I dont know how to pass those arguments into preparedStatement. Basicaly, how to form data to become arguments? Any ideas? Thank you!

Community · Accepted Answer · 2020-06-20 09:12:55Z

First of all lets understand the functioning of the BatchPreparedStatementSetter, it contains two methods:

getBatchSize(): Will define the quantity of records will be iterating over in the setValues method, the index will be passed as the second argument.

setValues(java.sql.PreparedStatement ps, int i): Will iterate the number of times defined in the getBatchSize() method.

So what is the point of this, you can access the elements of your collection using the index, like the example below:

jdbcTemplate.batchUpdate("INSERT INTO INS_RAZNO.ZAPOSLENICI_TEST VALUES (?, ?, ?, ?, ?, ?)", new BatchPreparedStatementSetter() {
     @Override                                   
     public void setValues(PreparedStatement ps, int i) throws SQLException {
         ps.setString(1, sh.getCell(i,1)); // Iterating over row, getting the first cell
     }
     @Override
     public int getBatchSize() {
         return sheet.getPhysicalNumberOfRows(); // Total rows in the Excel for example
     }
 });

PS: I used the Apache POI Documentation to suggest the methods to use

BatchPreparedStatementSetter Documentation

Арсен Мирзаян · Accepted Answer · 2018-12-24 13:35:03Z

1

You have to choose between two options:

Call update in cell iterate loop and use JdbcTemplate#update function instead of batchUpdate.
If you want batchUpdate then first you iterate Excel cells, collect data to List. And then outside of loop you can use JdbcTemplate#batchUpdate.

but not both.

answered Dec 24, 2018 at 13:35

Арсен Мирзаян

17710 bronze badges

Collectives™ on Stack Overflow

Parsing excel sheet to database in java - passing arguments issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related