Thread: Error by export Data from Excel File to MySql database

Hi,
since yesterday i tried hard to write code to export the elements from Excel file and store them in Database(Mysql)

I used POI Library to read from Excel and then store the attributes of Excel cells in Arraylist to add it later to data base

i read to much about this topic and saw a lot of examples but still get error

code to create Table:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
/**
 *
 * @author Mirage
 */
public class CreateTable {
public static void main(String args[]) {
   String url = "jdbc:mysql://localhost:3306/mkyongdb";
   Connection con;
   String createTableBooks = "CREATE TABLE  movies.filme " 
          + "(CD_ID double UNSIGNED  NOT NULL," 
            +"Albumtitel VARCHAR(45) NOT NULL," 
            +"Interpret VARCHAR(25) NOT NULL,"
            + "CREATED_DATE  double NOT NULL,"
            + "Track  double NOT NULL,"
            + "Titel VARCHAR(255) NOT NULL)";
 
    Statement stmt;
   try {
          Class.forName("com.mysql.jdbc.Driver");
   } catch(java.lang.ClassNotFoundException e){
    System.err.print("ClassNotFoundException: ");
          System.err.println(e.getMessage());
   }
    try {
          con = DriverManager.getConnection(url, "root","root");
          stmt = con.createStatement();
          stmt.executeUpdate(createTableBooks);
          stmt.close();
          con.close();
    } catch(SQLException ex) {
          System.err.println("SQLException: " + 
ex.getMessage());
    }
 }
}

code to read from excel and insert in database

package Mysql;
 
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
 
public class test {
 
 
	//static ArrayList cellArrayLisstHolder = new ArrayList();
 
	public static void main(String[] args) throws Exception{
 
		ArrayList dataHolder = readExcelFile(); 
		saveToDatabase(dataHolder);
	}
 
		public static ArrayList readExcelFile(){
 
		ArrayList medium = new ArrayList();
 
	try {
 
	    FileInputStream file = new FileInputStream(new File("d:\\Filme.xls"));
 
	    //Get the workbook instance for XLS file 
	    HSSFWorkbook workbook = new HSSFWorkbook(file);
 
	    //Get first sheet from the workbook
	    HSSFSheet sheet = workbook.getSheetAt(0);
 
	    //Iterate through each rows from first sheet
	    Iterator<Row> rowIterator = sheet.iterator();
	    while(rowIterator.hasNext()) {
	        Row row = rowIterator.next();
 
	        //display from the third row until 5th
	        if(row.getRowNum() > 0)
	        {
 
	        //For each row, iterate through each columns
	        Iterator<Cell> cellIterator = row.cellIterator();
 
	        ArrayList small = new ArrayList();
 
	        while(cellIterator.hasNext()) {
 
	            //Getting the cell contents
	            Cell cell = cellIterator.next();
 
	          small.add(cell);
 
	          medium.add(small);
		      }
	        }
	        }
		    }catch (Exception e){e.printStackTrace(); 
		    }
		    return medium;
		    }
 
		private static void saveToDatabase(ArrayList dataHolder) {
	    	   String url = "jdbc:mysql://localhost:3306/movies";
	    	   String username = "root";
	    	   String password = "root";
	       Connection con;
	       String query = "insert into filme values(?,?,?,?,?,?)";
	       PreparedStatement ps = null;
	       try {
	////////////////////////make connection withthe database ///////////////////////////////
	    	   Class.forName("com.mysql.jdbc.Driver");
	           con = DriverManager.getConnection(url, username, password);
 
	////////////////////////////////// Excute SQL statment:  ///////////////////////////////////////
 
	           ps = con.prepareStatement(query);
 
	           ArrayList cellStoreArrayList = null;
 
	       	//For inserting into database
	       	for (int i = 0; i < dataHolder.size(); i++) {
 
	       	    cellStoreArrayList = (ArrayList)dataHolder.get(i);
 
	       	       ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());
	       	       ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());
	       	       ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());
	       	       ps.setString(4,((HSSFCell)cellStoreArrayList.get(3)).toString());
	       	       ps.setString(5,((HSSFCell)cellStoreArrayList.get(4)).toString());
	       	       ps.setString(6,((HSSFCell)cellStoreArrayList.get(5)).toString());
 
	       	      ps.executeUpdate();
 
	       	       }
 
	           ResultSet rs = ps.executeQuery(query);
	           System.out.println(" Filme :");
	           System.out.println(" ============== ");
 
	/////////////////////////////handle the results: ///////////////////////////////////
 
	               while (rs.next()) {
	           		double s = rs.getDouble("CD_ID");
	           		String f = rs.getString("Albumtitel");
	           		String i = rs.getString("Interpret");
	           		double d = rs.getDouble("CREATED_DATE");
	           		double n = rs.getDouble("Track");
	           		String t = rs.getString("Titel"); 
	           		System.out.println(s + "   " + f + "             " + i + "        " + d + "      " + n + "   " + t);
	           }
	           ps.close();
	           con.close();
 
	           } catch(Exception ex) {
	                   System.err.print("Exception: ");
	                   System.err.println(ex.getMessage());
	           }
	}
}

The error message is :

HTML Code:

Exception: Index: 5, Size: 5

and when i check out my data base it will show that it inserted the attributes more 5 times in the data base not just one and couldn't continue to insert it more the i get the error message

I don't know why i got this error, and i don't know why insert the attributes more that once!!!

my excel file

HTML Code:

CD_ID	Albumtitel	  Interpret   created Track   Titel
--------------------------------------------------------------------------
4711	Not That Kind	Anastacia	1999	1	Not That Kind
4710	Not That Kind	Anastacia	1999	2	I’m Outta Love
4712	Not That Kind	Anastacia	1999	3	Cowboys & Kisses
4722	Wish You Her    Pink Floyd	1964	1	Shine On You Crazy Diamond
4713	Freak of Nature	Anastacia	1999	1	Paid my Dues

ps: the first row will not be inserted in the database so you can ignore it