Insert Data into MySQL From Excel JDBC - POI Example Program

Question

I would like to insert data into MySQL database table from Excel using JDBC manager with Apache POI. Here is my code:

TestApp.java

package testapp;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;

public class TestApp {

    public static void main(String[] args) throws Exception {

        try {

            Class.forName("com.mysql.jdbc.Driver");
            Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/test","root","root");
            con.setAutoCommit(false);
            PreparedStatement pstm = null ;
            FileInputStream input = new FileInputStream("countrycode.xls");
            POIFSFileSystem fs = new POIFSFileSystem( input );
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            Row row;
            for(int i=1; i<=sheet.getLastRowNum(); i++){
                row = sheet.getRow(i);
                String code = row.getCell(0).getStringCellValue();
                String desc = row.getCell(1).getStringCellValue();
                Date date = row.getCell(2).getDateCellValue();                
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                String Date = sdf.format(date);
                String callcode = row.getCell(3).getStringCellValue();
                Boolean status = row.getCell(4).getBooleanCellValue();
                String currency = row.getCell(5).getStringCellValue();
                String sql = "INSERT INTO Ocountry (OCCODE, OCDESC, OCDT, OCCALlCODE, OCINACTIVE, OCUCODE) VALUES('"+code+"','"+desc+"','"+date+"','"+callcode+"','"+status+"','"+currency+"')";
                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) {
            e.printStackTrace();
        }
    }

}

This is the error given when I run the file:

Exception in thread "main" java.lang.NullPointerException
    at testapp.TestApp.main(TestApp.java:36)
Java Result: 1

Is there anyone able to help me solve my problem?

Answer 1

It looks like it doesn't like this line:

String Date = sdf.format(date);

Are you sure that the variable date has a non-null value at that point?

Answer 2

I used that code. It definitely works and I modified it as follows:

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
//import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.*;

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://localhost/tables", "root", "root");
        con.setAutoCommit(false);
        PreparedStatement pstm = null;
        FileInputStream input = new FileInputStream("C:\\Users\\Desktop\\a1.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 add = row.getCell(1).getStringCellValue();

            int  contact = (int) row.getCell(2).getNumericCellValue();

            String email = row.getCell(3).getStringCellValue();

            String sql = "INSERT INTO employee (name, address, contactNo, email) VALUES('" + name + "','" + add + "'," + contact + ",'" + 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) {
     }
   }
}