Page 1 of 3 123 LastLast
Results 1 to 20 of 44
  1. #1
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

    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:
    XML 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
    Java Code:
    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??
    Last edited by vector_ever; 08-01-2013 at 03:49 PM.

  2. #2
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,242
    Rep Power
    19

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

    I've changed the title. To do this yourself, click 'Edit Post' and then 'Go Advanced'

    db
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  3. #3
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

    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
    Java Code:
    Set set = new TreeSet(dataSheet);
    but it is give error by execution:
    XML 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?
    Last edited by vector_ever; 08-01-2013 at 04:34 PM.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

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

    You need a class that represents a row.
    That class needs to have equals and hashcode methods defined.
    You will create an instance of this class for each row (where you are currently creating the List<cell data>) and add it to the List representing all the rows (List<YourModel>).

    That will ensure only one of each row is added.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

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

    here i see now extra class just immediately convert form list to set How to convert List to Set (ArrayList to HashSet)

    innocently i didn't understand what you wrote

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

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

    You need a class that represents your data.
    I can't say it any simpler than that.

    In order for a Set to decide if something is already in the Set it uses the equals() method of the objects stored in the Set.
    TreeSet is slightly different in that it also requires that the either the classes stored implement Comparable or that you provide a Comparator when creating the TreeSet. This is because a TreeSet is ordered.
    All this is in the API documentation.

    In your case a HashSet would be sufficient (unless you require ordering). So you need a class that implements hashcode() and equals().
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

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

    Thank you any way, but maybe i am not good engough in java to understand your hints, but cann you give me a small example or any link to see what did you mean

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

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

    You're trying to write something that uses POI and JDBC and you don't understand classes?

    Learning the Java language.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  9. #9
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

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

    I don't need such as link, i don't to learn java from the beginning, i understand the classes and maybe i have a lack somewhere, so therefore i hoped to find here aid and asked you link about how can exactly to represents my data in class to implements hashcode() and equals()

    All what i wanted example to explain it

    anyway thank you

  10. #10
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,642
    Rep Power
    7

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


  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

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

    Quote Originally Posted by vector_ever View Post
    I don't need such as link, i don't to learn java from the beginning, i understand the classes and maybe i have a lack somewhere, so therefore i hoped to find here aid and asked you link about how can exactly to represents my data in class to implements hashcode() and equals()

    All what i wanted example to explain it

    anyway thank you
    OK, so you understand classes then at the minimum you need to create a class that represents your model (the data you are reading from a row in your excel file).
    We can worry about the equals and hashcode after you have that class in place and are successfully populating it.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  12. #12
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

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

    it was very obvious that i need to configure my good with equal() method and hashcod(), since i have not use them ever, so i got troubles.

    since i don't don't know what is the attribute should i compare to also i don't have any getter

    my Problem was from the beginning what should i write in equals() and hashCode() methods, which data should i compare to.

    that maybe you find them very stupid Questions, but i really for 3 days and i looking for the right answer, i don't deal with normal class which i created and add its attributes and setter and getter, here i have elements exported immediately from excel, which i don't any idea about its Generic ( or that is not important).

    What i need really the modification for my code

    Java Code:
    @Override
       public boolean equals(Object obj){  
     
            if(!(obj instanceof myClass))  
                return false;  
     
            return (attribute== ((myClass) obj1).getAttribute());   
        }  
     
     
        @Override
        public int hashCode(){  
     
            return  attribute.hashCode();    
        }

  13. #13
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

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

    Where is that code?
    What class is it in?

    Have you written a class to represent a row of your data?
    Until you have that then there's little point going any further.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  14. #14
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

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

    Where is that code?
    What class is it in?
    I didn't get it, which code you meant it

    any way that is my code again
    Java Code:
    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.HashSet;
    import java.util.Iterator;
    import java.util.Set;
    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 Excel2DB4 {
    
    
    	//static ArrayList cellArrayLisstHolder = new ArrayList();
    	
    	public static void main(String[] args) throws Exception{
    		
    		ArrayList<ArrayList<Cell>> dataHolder = readExcelFile(); 
    		saveToDatabase(dataHolder);
    	}
    		public static ArrayList<ArrayList<Cell>> readExcelFile(){
    			
    		ArrayList<ArrayList<Cell>> dataSheet = new ArrayList<ArrayList<Cell>>();
    		Set set = null;
    	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<Cell> data = new ArrayList<Cell>();
    	        
    	        while(cellIterator.hasNext()) {
    	            
    	            //Getting the cell contents
    	            Cell cell = cellIterator.next();
    	            
    	        data.add(cell);
    	          }
    	        dataSheet.add(data);
    	        set = new HashSet(dataSheet); 
    	        }
    	        }
    		    }catch (Exception e){e.printStackTrace(); 
    		    }
    		//Set set = new HashSet(dataSheet);
    		ArrayList list = new ArrayList(set);
    		    return list;
    		    }
    so you can see that my code read each row from excel and stroe it in ArrayList dataSheet, then i convert this ArryaList to HashCode to drop the duplicated value (which need to configure its Equal and HashSet methods)
    and at the last i convert the HashSet back to ArrayList to use it in another methode.

    Obviously the Hashcode need to modify its Equal and HashSet methods to specify which attribute must make the comparison, but stay the question always, which attribute must fulfill those method.

    Have you written a class to represent a row of your data
    here the iterate each cell and each row and add them to the arraylist
    Java Code:
    	    //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<Cell> data = new ArrayList<Cell>();
    	        
    	        while(cellIterator.hasNext()) {
    	            
    	            //Getting the cell contents
    	            Cell cell = cellIterator.next();
    	            
    	        data.add(cell);
    	          }
    	        dataSheet.add(data);
    	        set = new HashSet(dataSheet); 
    	        }
    	        }

  15. #15
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

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

    No.
    A class that represents a row of your data.
    If I had a table of employees then I would have an Employee class that represents that data:
    Java Code:
    public class Employee {
       private int id;
       private String name;
       
    ... getters and setters in here
    ... and an equals() and a hashCode() method if needed.
    }
    That is a model.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  16. #16
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

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

    ok, i supposed it is not important to know which data in excel we have.

    schould i open the excel file and represent my data of that file in a separated class??

  17. #17
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

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

    Instead of creating a Set<List<Cell>> or similar when reading in the Excel sheet, you create a Set<YourClass>, creating an instance of YourClass for each row of data in the sheet.
    If equals and hashCode (assuming a HashSet) are correct for the class then you'll only get one of each row.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  18. #18
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

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

    Ok, i think i is a very proper idea ( right now theoretical ) but with coding !!

    assume the attributes in my Excel file as the following:

    PHP Code:
    Id	    Name     Address    Salary
    ----------------------------------------
    4711	john	address1	1000
    4710	Adam	address2	1200
    4712	Jasmin	address3	900
    4722	Lila	address4	1500
    4713	Sami	address5	1200
    4710	Adam	address2	1200
    4712	Jasmin	address3	900
    4711	john	address1	1000
    the code to represent this data
    Java Code:
    public class Employee {
    
    		private int id;
    	    private String name;
    	    private String address;
    	    private double salary;
    	 
    	    public Employee(int id, String name, String address, double salary) {
    	        this.id = id;
    	        this.name = name;
    	        this.address = address;
    	        this.salary = salary;
    	    }
    	    
    	    public Employee(){
    	    	
    	    }
    	 
               //Setters and Getters
    	     
    	    public String toString(){
    	        return "ID:" + getId() + ", Name:" + getName() + ", Address:" + getAddress() + ", Salary:" + getSalary();
    	    }
    	            }
    now come to the class where it must read from Excel:
    Java Code:
    Employee emp = new Employee ();
    	    //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();  //which type of Iterator should i use 
    	        
    	        ArrayList data = new ArrayList();
    	        
    	        while(cellIterator.hasNext()) {
    	            
    	            //Getting the cell contents
    	            Cell cell = cellIterator.next(); 
    	            
    	        data.add(cell);
    	          }
    	        
    	        dataSheet.add(data);
    		      
    	        }
    	        }
    here the first problem i face it, was which which type of Iterator should i use instead of cellIterator if i want to use Employee.java.
    write now i don't use Eqaul or Hashcode methods (they are now not the big problem in our new case)

  19. #19
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

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

    So, at the moment you have it iterating over the rows in the sheet.
    That's correct.
    For each row you want to create a new Employee (don't do that outside the iterator loop, do it inside), passing in the relevant Cell values.
    Then add this new Employee to your List<Employee>.

    In fact, since you want to avoid duplicates then make that a Set<Employee>.

    In pseudo code:
    Java Code:
    for each row
        create an employee from the data in the cells
        add that employee to the Set
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  20. #20
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

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

    Java Code:
    	    while(rowIterator.hasNext()) {
    	        Row row = rowIterator.next();
    	        
    	        //display from the first row 
    	        if(row.getRowNum() > 0)
    	        {
    	       
    	        //For each row, iterate through each columns
    	        Iterator<Employee> it = dataSheet.iterator();
    	        
    	        ArrayList data = new ArrayList();
    	        
    	        while(it.hasNext()) {
    	            
    	            //Getting the cell contents
    	        	Employee emp = (Employee)(it.next()); -----> cannot be cast to Employee
    	            
    	        data.add(emp);
    	          }
    	        
    	        dataSheet.add(data);
    		      
    	        }
    	        }
    by running it to test at first if it add the data in this way to ArrayList at first then i get error:
    XML Code:
    java.lang.ClassCastException: java.util.ArrayList cannot be cast to test.Employee

Page 1 of 3 123 LastLast

Similar Threads

  1. Replies: 9
    Last Post: 04-18-2013, 12:16 PM
  2. Replies: 1
    Last Post: 01-24-2013, 09:30 AM
  3. How can i store a data from Excel sheet to an array.
    By deshmukh.niraj04 in forum New To Java
    Replies: 0
    Last Post: 05-03-2011, 06:33 AM
  4. How to read data from excel and store it in db
    By saran123 in forum New To Java
    Replies: 5
    Last Post: 10-03-2008, 10:19 AM
  5. How to store extracted xml values in a database?
    By palanikumark in forum Advanced Java
    Replies: 6
    Last Post: 05-30-2008, 12:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •