0

I am trying to insert excel sheet data into mysql db.Everything works if column has data.But if column is null then the program stops execution and data is not inserted.

Here is how i am inserting excel data

TestApp.java

    public class TestApp {
    public static void main(String[] args) throws Exception {
    try {
    Class forName = Class.forName("com.mysql.jdbc.Driver");
    Connection con = null;
    con = 
    DriverManager.getConnection("jdbc:mysql://192.168.1.13:3307/db1",
    "sachin-LH", "s123");
    con.setAutoCommit(false);
    PreparedStatement pstm = null;
    FileInputStream input = new 
    FileInputStream("C:\\Users\\lh\\Downloads\\Student Details(2009-
    13).xls");
    POIFSFileSystem fs = new POIFSFileSystem(input);
    Workbook workbook;
    workbook = WorkbookFactory.create(fs);
    Sheet sheet = workbook.getSheetAt(0);
    Row row;
    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        row = (Row) sheet.getRow(i);
        String name = row.getCell(0).getStringCellValue();
        String email = row.getCell(1).getStringCellValue();
        String sql = "INSERT INTO user
        (firstName,email) VALUES('" + name + 
      "','"+email+"')";
        pstm = (PreparedStatement) con.prepareStatement(sql);
        pstm.execute();
        System.out.println("Import rows " + i);
    }
    con.commit();
    pstm.close();
    con.close();
    input.close();
    System.out.println("Success import excel to mysql table");
    } catch (IOException e) {
    }
    }
    }

My Excel Sheet

    Name  email
    test  test@gmail.com
    john  ----
    mike  mike@gmail.com

Here for 2nd row there is no email thats y i am getting Null Pointer exception.Can anyone tell how to insert null value if value is not present for particular column.?

Sachin HR
  • 450
  • 11
  • 28
  • NPE at this line `String email = row.getCell(1).getStringCellValue();`? – XtremeBaumer Mar 22 '18 at 09:29
  • yeah for email.Because for 2nd record there is no email – Sachin HR Mar 22 '18 at 09:30
  • 2
    Two tips. Do not publish real passwords on internet. Define placeholders in your PreparedStaments and set their values, do not use String concatenation. That way you avoid SQL injections. – RubioRic Mar 22 '18 at 09:32
  • Also, there is no need to initialize objects to null just before instantiating. You can declare and instantiate in the same line. `Connection con = null; con = DriverManager....` It's not the only example in your code. Less lines to read better for getting help sooner. – RubioRic Mar 22 '18 at 09:37
  • How to check if a cell is empty https://stackoverflow.com/a/33732213/2553194 – RubioRic Mar 22 '18 at 09:42

1 Answers1

1

There may be few possibilities for this.

  1. You may have NOT NULL constraints in your DB column or
  2. You are trying to access null object on this line String email = row.getCell(1).getStringCellValue();

Small Work Around: Do a null check before accessing this value like

if(null != row.getCell(1)) {
   row.getCell(1).getStringCellValue();
}
Satz
  • 307
  • 3
  • 19