Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 6 of 6

Thread: Store data from Excel to Database without duplicate values

  1. #1
    Member
    Join Date
    Jul 2013
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default 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??


  2. #2
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,318
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

    Default Re: Store data from Excel to Database without duplicate values

    so Where should i use Set in my code??
    How do you define duplicates? Column values, combinations of column values, entire rows? Your database schema may be a guide here (you may have unique constraints on a table). Once you've define this, use an existing class or create one (be careful here as you must override and understand how a Set defines equality between classes), then add these to your set.

  3. #3
    Member
    Join Date
    Jul 2013
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default Re: Store data from Excel to Database without duplicate values

    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

  4. #4
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,318
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

    Default Re: Store data from Excel to Database without duplicate values

    A TreeSet sorts whatever is added to it relative the whatever else it contains - thus the class type you add must implement Comparable to facilitate sorting. Use a HashSet if you don't need sorting.

    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
    A good start - doing so can be tricky as you must override the equals and hashCode methods...I would suggest reading the API of these methods prior to doing so.

  5. The Following User Says Thank You to copeg For This Useful Post:

    vector_ever (August 1st, 2013)

  6. #5
    Member
    Join Date
    Jul 2013
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default Re: Store data from Excel to Database without duplicate values

    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

  7. #6
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,318
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

    Default Re: Store data from Excel to Database without duplicate values

    Please post an SSCCE, as this behavior is fully dependent upon the details of implementation.

    --- Update ---

    This thread has been cross posted here:

    http://www.java-forums.org/apache-poi/80542-store-data-excel-database-without-duplicate-values.html

    Although cross posting is allowed, for everyone's benefit, please read:

    Java Programming Forums Cross Posting Rules

    The Problems With Cross Posting


Similar Threads

  1. Creating a Thread from a Thread
    By angstrem in forum Threads
    Replies: 11
    Last Post: May 29th, 2013, 09:31 AM
  2. Replies: 4
    Last Post: June 15th, 2012, 01:50 PM
  3. My First Post
    By Student X in forum Member Introductions
    Replies: 1
    Last Post: July 26th, 2011, 03:14 AM