title: How to compare excel file table and mysql table and insert data into mysql column wise using jsp?
body: <p>I am new in JSP and Apache POI library, i have one excel file with data:<a href="http://i.stack.imgur.com/g5lO1.png" rel="nofollow">My Excel Sheet</a></p>
  
  <p>And i have one mysql table "data" with column name "address" and "name". I am using following code for insertion excel data into mysql table. Data insertion is successfully complete.    </p>
  
  <pre><code>&lt;%@ page language="java" import="java.sql.*" contentType="text/html charset=ISO-8859-1" pageEncoding="ISO-8859-1"%&gt;
    &lt;!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"&gt;
      &lt;%@ page import ="java.util.Date" %&gt;
      &lt;%@ page import ="java.io.*" %&gt;
      &lt;%@ page import ="java.io.FileNotFoundException" %&gt;
      &lt;%@ page import ="java.io.IOException" %&gt;
      &lt;%@ page import ="java.util.Iterator" %&gt;
      &lt;%@ page import ="java.util.ArrayList" %&gt;
      &lt;%@ page import ="javax.servlet.http.HttpServletRequest"%&gt;
      &lt;%@ page import ="org.apache.poi.hssf.usermodel.HSSFCell" %&gt;
      &lt;%@ page import ="org.apache.poi.hssf.usermodel.HSSFRow" %&gt;
      &lt;%@ page import ="org.apache.poi.hssf.usermodel.HSSFSheet" %&gt;
      &lt;%@ page import ="org.apache.poi.hssf.usermodel.HSSFWorkbook" %&gt;
      &lt;%@ page import ="org.apache.poi.poifs.filesystem.POIFSFileSystem" %&gt;
      &lt;html&gt;
      &lt;head&gt;
      &lt;meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"&gt;
      &lt;/head&gt;
      &lt;body&gt;
        &lt;%!     
          Connection con;
          PreparedStatement ps=null;
          Statement stmt= null;
          public static ArrayList readExcelFile(String fileName)
          {
             ArrayList cellArrayLisstHolder = new ArrayList();
              try{
                     FileInputStream myInput = new FileInputStream(fileName);
                     POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
                     HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
                     HSSFSheet mySheet = myWorkBook.getSheetAt(0);
                     Iterator rowIter = mySheet.rowIterator();
                     while(rowIter.hasNext()){
                      HSSFRow myRow = (HSSFRow) rowIter.next();
                      Iterator cellIter = myRow.cellIterator();
                      ArrayList cellStoreArrayList=new ArrayList();
                      while(cellIter.hasNext()){
                        HSSFCell myCell = (HSSFCell) cellIter.next();
                        cellStoreArrayList.add(myCell);
                      }
                     cellArrayLisstHolder.add(cellStoreArrayList);
                    }
                 }catch (Exception e){e.printStackTrace(); }
                 return cellArrayLisstHolder;
                }%&gt;
                &lt;%
                  String file = request.getParameter("file");
                  String fileName="/home/data.xls";
                  ArrayList dataHolder=readExcelFile(fileName);
                  try
                     {
                     Class.forName("com.mysql.jdbc.Driver");
                      con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql","root","");
                      stmt =con.createStatement();
                     String query="insert into data(address, name) values(?,?)";
                     ps=con.prepareStatement(query);
                     int count=0;
                     ArrayList cellStoreArrayList=null;
                      for(int i=1;i&lt;dataHolder.size();i++) {
                       cellStoreArrayList=(ArrayList)dataHolder.get(i);
                       ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());
                       ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());
                       count= ps.executeUpdate();
                     }
                    if(count&gt;0)
                    { 
                       %&gt; Following deatils from Excel file have been inserted in student table of database
                       &lt;table&gt;
                       &lt;tr&gt;
                         &lt;th&gt;Address&lt;/th&gt;
                         &lt;th&gt;Name&lt;/th&gt;
                       &lt;/tr&gt;
                       &lt;% 
                          for (int j=1;j &lt; dataHolder.size(); j++) {
                           cellStoreArrayList=(ArrayList)dataHolder.get(j);%&gt;
                          &lt;tr&gt;
                             &lt;td&gt;&lt;%=((HSSFCell)cellStoreArrayList.get(0)).toString() %&gt;&lt;/td&gt;
                             &lt;td&gt;&lt;%=((HSSFCell)cellStoreArrayList.get(1)).toString() %&gt;&lt;/td&gt;
  
                     &lt;/tr&gt;
                 &lt;%}
                    }
                   else
                      {%&gt;
                   &lt;center&gt; Details have not been inserted!!!!!!!!!&lt;/center&gt;
  
                             &lt;%    }   
                          }catch(Exception e)
                       {}%&gt;
                      &lt;/table&gt;
  
                       &lt;/body&gt;
                        &lt;/html&gt;
  </code></pre>
  
  <p>Here my excel sheet have only two columns, but in another case my excel sheet have more than two columns. Column quantity in excel sheet and MySQL table is same, but column sequence is not same. Means suppose in excel sheet have four column like: "address", "name", "age", "class", and mysql table also have four column but in another sequence like: "address", "age", "name", "class". In this case data insertion is not done successfully. Means "age" and "name" column data insertion is exchange with each other. So What is solution for check excel sheet column name and mysql table column name, and insert data into particular column. Position of column may vary but data insertion is done true. Please give me some solution for this. Correct my code if i have made some mistake. Thank you in advance.</p>