Thread: Store data from Excel to Database without duplicate values

Hi,



i have a code to read from Excel and stroe in Database, the Attributes in the Excel file:

HTML Code:

CD_ID	Albumtitel	   Interpret	         Year                 Track	       Titel
----------------------------------------------------------------------------------------------
4711	Not That Kind	   Anastacia	         1999	             1	           Not That Kind
4710	Not That Kind	   Anastacia	         1999	             2	           I’m Outta Love
4713	Not That Kind	   Anastacia	         1999	             3	           Cowboys & Kisses
4722	Wish You Her      Pink Floyd	         1964	             1	           Shine On You Crazy Diamond
4713	Not That Kind	  Anastacia	           1999	             3	           Cowboys & Kisses
4711	Not That Kind	  Anastacia	           1999	             1	           Not That Kind
4712	Love me	          Sp.Girls	           1998	             1	           Viva for ever
4710	Not That Kind	  Anastacia	           1999	             2	           I’m Outta Love
4722	Wish You  Her     Pink Floyd	         1964	             1	           Shine On You Crazy Diamond

you can notice that, the Table have a duplicate values, what i want it is to eliminate any duplicate value.

In my code i used two Arraylist, the first to store cells, the second to store rows, so i thought instead Arraylist i can use Set (with Set no duplicate values)
but somehow i failed to make it correct, or my idea was not good enough

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 Excel2DB3 {
 
 
	//static ArrayList cellArrayLisstHolder = new ArrayList();
 
	public static void main(String[] args) throws Exception{
 
		ArrayList dataHolder = readExcelFile(); 
		saveToDatabase(dataHolder);
	}
 
		public static ArrayList readExcelFile(){
 
		ArrayList dataSheet = new ArrayList();
 
	try {
 
	    FileInputStream file = new FileInputStream(new File("d:\\Songs.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 first row 
	        if(row.getRowNum() > 0)
	        {
 
	        //For each row, iterate through each columns
	        Iterator<Cell> cellIterator = row.cellIterator();
 
	        ArrayList data = new ArrayList();
 
	        while(cellIterator.hasNext()) {
 
	            //Getting the cell contents
	            Cell cell = cellIterator.next();
 
	        data.add(cell);
	          }
 
	        dataSheet.add(data);
 
	        }
	        }
		    }catch (Exception e){e.printStackTrace(); 
		    }
		    return dataSheet;
		    }
 
 
		private static void saveToDatabase(Set dataHolder) {
	    	   String url = "jdbc:mysql://localhost:3306/songs";
	    	   String username = "root";
	    	   String password = "root";
	       Connection con = null;
	       String query = "insert into lieder 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) ((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();
 
	       	       }
 
	       } catch(Exception e) {
	    	   e.printStackTrace();
	       }
	       finally{
	    	   try{
 
	/////////////////////////////handle the results: ///////////////////////////////////
 
	       	   ResultSet rs = ps.executeQuery("SELECT * from lieder");
	           System.out.println(" Lieder :");
	           System.out.println(" ============== ");
 
	               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());
	           }
	}
}
}

so Where should i use Set in my code??

Ok one Idea, that to use Converter from List to Set and vice versa

So the ArrayList dataSheet which contain the whole sheet Attributes (with duplicated values) i will set it in Set, so the list aromatically will drop all duplicated value

Set set = new TreeSet(dataSheet);

but it is give error by execution:

HTML Code:

Exception in thread "main" java.lang.ClassCastException: java.util.ArrayList cannot be cast to java.lang.Comparable
	at java.util.TreeMap.compare(Unknown Source)
	at java.util.TreeMap.put(Unknown Source)
	at java.util.TreeSet.add(Unknown Source)
	at java.util.AbstractCollection.addAll(Unknown Source)
	at java.util.TreeSet.addAll(Unknown Source)
	at java.util.TreeSet.<init>(Unknown Source)
	at Mysql.Excel2DB4.readExcelFile(Excel2DB4.java:73) --> refer to Set set = new TreeSet(dataSheet);

so what should i do and why this error?

--- Update ---

I think i must creat extra class to represent the my data and make some compartion or some thing like that, but iam not sure and i don't know how

Exactly, now i get no more such a error.

But still somthing wrong (not error), i mean i inject my Arraylist in the Set to drop the then i inject the Set in other new empty Arraylist to display it (print) How to convert List to Set (ArrayList to HashSet)

but what i found that it is the duplicated value still there, and using set don't make any sense