how to store data from excel sheet into mysql database using jdbc connection

Question

I have written the code for access data from Excel sheet but i am not able to store into database using JDBC . my code for access data from excel sheet

   try{
    List list=new ArrayList();
    String fileName="d:\\menu.xls";
    File file=new File(fileName);
    InputStream input = new BufferedInputStream(new FileInputStream(file));
    POIFSFileSystem fs = new POIFSFileSystem( input );
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
  //int i=0;
 Iterator rows=sheet.rowIterator();
 while(rows.hasNext()){
     HSSFRow row=(HSSFRow)rows.next();
     System.out.println("\n");
     Iterator cells=row.cellIterator();
     while( cells.hasNext() ) {
         HSSFCell cell = (HSSFCell) cells.next();
         if(HSSFCell.CELL_TYPE_NUMERIC==cell.getCellType()){
         System.out.print(cell.getNumericCellValue()+" " );
        // list.add(cell.getNumericCellValue());

         }
         else if (HSSFCell.CELL_TYPE_STRING==cell.getCellType()){
             System.out.print(cell.getStringCellValue()+" " );
             //list.add(cell.getStringCellValue());

         }
         else
             if (HSSFCell.CELL_TYPE_BOOLEAN==cell.getCellType()){
             System.out.print(cell.getBooleanCellValue()+" " );
             //list.add(cell.getBooleanCellValue());

             }
             else
                 if(HSSFCell.CELL_TYPE_BLANK==cell.getCellType()){
                     System.out.print( "BLANK     " );}
                     else
                 System.out.print("Unknown cell type");

     }

 }
System.out.println(list);
}catch(Exception e){
    e.printStackTrace();
}

I got all the data into myeclipse console bt i have no idea how to store in DB

please tell me how to store data into mysql database using JDBC

Thanx in advance

Answer 1

As you have an ArrayList in which you're adding cell values, you can proceed as follows:

public void insertRowInDB(List cellValues){
    Connection con = null;
    PreparedStatement preparedStatement = null;
    try{            
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost/databaseName?"
                                      + "user=sqlUser&password=sqlPassword");
         String sql = "INSERT INTO tableName(field1, field2, ..., fieldN) VALUES (?,?,...,?)";
         preparedStatement = con.prepareStatement(sql);
         int paramIndex = 1;
         for(Object cell : cellValues){
             preparedStatement.setObject(paramIndex, cell);
             paramIndex++;
         }
         int status = preparedStatement.executeUpdate();
         //DO something with the status if needed
    } catch(SQLException ex) { 
          /* log the exception */
    } finally {
        try{
            preparedStatemnt.close();
            con.close();
        } catch(SQLException ignored) {}
    }
}

You'll need make this little change:

while(rows.hasNext()){
    List list = new ArrayList(); // initialize the list here

    HSSFRow row=(HSSFRow)rows.next();
    System.out.println("\n");
    Iterator cells=row.cellIterator();
    while( cells.hasNext() ) {
        /* all your code seems fine here, just uncomment list.add(something) lines */
    }

    insertRowInDB(list); // insert the cells in your database
}

I also suggest you check out this MySQL and Java JDBC - Tutorial