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

dic19 · Accepted Answer · 2013-09-11 14:26:15Z

3

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

edited Sep 11, 2013 at 14:26

answered Sep 11, 2013 at 11:53

dic19

18k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Avinash Singh Over a year ago

thanx a lot for your answer finally i got it !!

dic19 Over a year ago

@AvinashSingh You're wellcome! :) If this worked for you please don't forget to accept the answer, so people looking for similar problems can identify this as useful. See How does accepting an answer work?

Razvan N Over a year ago

Hi. I wanted to implement the same class and I've had an error: Invalid column index. The error indicates this part of code:

for(Object cell : cellValues){              preparedStatement.setObject(paramIndex, cell);              paramIndex++;          }

Can you help me, please?

dic19 Over a year ago

Hi there. This error suggests the number of cells is != of the number of columns in your statement. Be aware that generally speaking indexes in Java start at 0 BUT when you work with preparedStatement.setObject(index, object) index starts at 1. If you get stuck please post your new code and I'll try to help you. @RazvanN

Razvan N Over a year ago

Okay. I see what you're telling me. I wrote a sql query like this:

String sqlQuery = "INSERT INTO STORE_LOCATIONS (ID, COUNTY, LOCALITY, STORE, STORE_NAME, ADDRESS, SCHEDULE, LATITUDE, LONGITUDE) VALUES (?,?,?,?,?,?,?,?,?)";

how can we talk on the chat? I can't seem to access it right...

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related