Page 2 of 3 FirstFirst 123 LastLast
Results 21 to 40 of 44
  1. #21
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

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

    That does not look like the pseudo code I provided.
    What is the point of the Iterator<Employee>?

    You need to iterate over the rows (which you are doing) and then create an Employee with the data in that row.
    That involves getting the Cells and turning their values into things that match the attributes in the Employee.
    Once you have that Employee you then add them to your Set<Employee>, because you said you want to remove duplicates.
    There is no need for any ArrayLists.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  2. #22
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    177
    Rep Power
    1

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

    What is the point of the Iterator<Employee>?
    I don't understand what you mean about it

    That does not look like the pseudo code I provided
    Java Code:
    for each row
    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();
    Java Code:
    create an employee from the data in the cells
    while(it.hasNext()) {
             
            //Getting the cell contents
            Employee emp = (Employee)(it.next());
    Java Code:
     add that employee to the Set
     data.add(emp);
          }
    any way in the last Step i added the employe to Arraylist not to Set (dosen't matter right now i will retype it when it works correctly with the ArrayList)

    You need to iterate over the rows (which you are doing) and then create an Employee with the data in that row
    so in my code i didn't do it correctly?

  3. #23
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

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

    You said earlier that you understood classes.
    It is clear to me you don't.
    Java Code:
    Employee emp = new Employee(...parameters here...);
    That creates a new Employee.

    That iterator has nothing to do with it.
    Indeed the comment in the code says what you should be doing:
    Java Code:
    //For each row, iterate through each columns
    so iterate through the columns...which are part of the Row.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  4. #24
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    177
    Rep Power
    1

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

    That involves getting the Cells and turning their values into things that match the attributes in the Employee.
    And how can i do that? since i use just 2 iterators ( one to iterate each row and the another to iterate each column in that row) since i don't have to use Employee iterator so how to get the attribute from excel which represent the attributes in Employee.java

    The missing point is how to use the new object of my Employee class in the loop of row and extract the attributes and add them to the Set

    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();
    
    	        Employee employee = new Employee (id, address, address, salary);
    
    	        Set data = new HashSet();
    
    	        // missing point about using employee 
    	            
    	        data.add(employee );
    	          }
    
    	        }

  5. #25
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

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

    Java Code:
    ((HSSFCell)cellStoreArrayList.get(0)).toString());
    This is in your code for updating the database.
    So you know how to get data out of a Cell.
    And since you know what type of data each Cell represents you can create the correct datatype.
    In any case, why have you called your class Employee?
    Your data is all to do with music tracks...
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  6. #26
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    177
    Rep Power
    1

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

    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)
    	        {
    	        	Employee emp= new Employee ();
                            emp.setId(row.getCell(0).getNumericCellValue()));
    	        	emp.setName(row.getCell(1).getStringCellValue());
    	        	emp.setAddress(Double.toString(row.getCell(2).getNumericCellValue()));
                           emp.setSalary(row.getCell(3).getNumericCellValue());
    	        	
    	        	set.add(emp);  //where set is Object from HashSet
    	        	dataSheet.add(set); // where dataSheet id Object from arrayList
    	        }
                                   return dataSheet;
    	        }
    now the another question i use dataSheet.add(set); to put my results in arrayList (convert Set to List) to use this ArrayList in another method.

    but some how when i do this i get the following error:
    XML Code:
    java.lang.ClassCastException: java.util.HashSet cannot be cast to java.util.ArrayList
    it obvious it still read the content of "dataSheet" as HshSet not as ArrayList, despite i convert it to ArrayList, so what is the error

  7. #27
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

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

    You only need the Set.
    There is no need for your ArrayList.
    The HashSet<Employee> will hold your data for the entire spreadsheet, where each Employee represents a row.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  8. #28
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    177
    Rep Power
    1

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

    I need ArrayList to use it in another method

  9. #29
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

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

    Change that method?
    Turn its parameter into a Set<YourClass>?
    Or even Collection<YourClass>?

    It just seems a bit daft to create a Set and then throw it away for no seemingly good reason, especially when that method that uses the ArrayList is going to have to be changed anyway to take into account your List now contains objects of YourClass and not List<Cell>.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  10. #30
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    177
    Rep Power
    1

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

    I tried early to make it just with HashSet
    Java Code:
     public static void main(String[] args) throws Exception{
             
            Set dataHolder = readExcelFile(); 
            saveToDatabase(dataHolder);
        }
     
            public static Set readExcelFile(){
    Set dataSheet = new HashSet();
    // some method here
    return set;
    }
    
    private static void saveToDatabase(Set dataHolder) {
     Set cellStoreSet = null;
                    
                //For inserting into database
                for (int i = 0; i < dataHolder.size(); i++) {
                     
                    cellStoreSet = (Set) dataHolder.get(i); // get error, ofcourse no .get(index) in HashSet
    	       	       ps.setString(1,((<myClass>)cellStoreSet .get(0)).toString());
    	       	       ps.setString(2,((<myClass>)cellStoreSet .get(1)).toString());
    
    }
    that will be with set and no idea to go on, also with collection still worse

  11. #31
    gimbal2 is online now Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,583
    Rep Power
    5

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

    So now you go to investigate the API of the HashSet class to see if there isn't anything else you might use to iterate through the elements of the set, right? Or perhaps you do a google for "java set example" to see what you can find that way.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  12. #32
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

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

    Note, you should be using Generics, so your Set is a Set<YourClass>.
    Then you iterate (I'm being generous here) over the Set<YourClass> in the saveToDatabase method.

    And remember, this is a Set<YourClass> now, so you don't do all that stuff involving Cells, you just get the relevant attribute from the YourClass object.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  13. #33
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    177
    Rep Power
    1

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

    So now you go to investigate the API of the HashSet class to see if there isn't anything else you might use to iterate through the elements of the set, right? Or perhaps you do a google for "java set example" to see what you can find that way
    i know it there is two another ways:
    Java Code:
     Set<myClass> set = new HashSet<myClass>()
    1- for (myClass o: set)
    2- Iterator it = set.iterator();
     while (it.hasNext()){
    //do something
    but the Problem is i trate the Set within any of those how to deal with a rest of code
    Java Code:
             cellStoreSet = (Set) dataHolder.get(i); // get error, ofcourse no .get(index) in HashSet
                       ps.setString(1,((<myClass>)cellStoreSet .get(0)).toString()); //where ps is a Preparesteatment
                       ps.setString(2,((<myClass>)cellStoreSet .get(1)).toString());
    i don't know how to deal with it

    Note, you should be using Generics, so your Set is a Set<YourClass>
    i know and i used it but the problem still how to iterate the Set in the last three lines in the code above
    Last edited by vector_ever; 08-07-2013 at 07:45 PM.

  14. #34
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

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

    You have YourClass, which has getXXX methods to get at the data.
    Have you looked at the API for Iterator?
    What does the next() method return?
    It returns YourClass.
    So assign that to a local variable in the loop and then use the getters to access the relevant data.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  15. #35
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    177
    Rep Power
    1

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

    assign that to a local variable in the loop and then use the getters to access the relevant data
    sorry i don't understand it , do you mean something like that:
    Java Code:
    Set<myClass> set = new HashSet<myClass>()
     Iterator it = set.iterator();
     while (it.hasNext()){
     myClass obj = iterator.next();
    // use obj with getters
     }
    any way i Don't know how to use it with getter to have the same equivalent as:
    Java Code:
    cellStoreSet = (Set) dataHolder.get(i); 
              ps.setString(1,((<myClass>)cellStoreSet .get(0)).toString()); //where ps is a Preparesteatment
              ps.setString(2,((<myClass>)cellStoreSet .get(1)).toString());

  16. #36
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

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

    Why do you insist on trying to work with cells?
    You don't have the cells at this point.
    You have an object that holds the data you are putting into the prepared statement.

    If I have a Employee class, with (for example) a name attribute and an id attribute, and I am inserting this into an employee table with related columns, then I would do something like this:
    Java Code:
    Employee employee = // get the employee from somewhere.
    PreparedStatement ps = conn.prepareStatement("insert into employee (id, name) values (?,?)");
    ps.setInt(1, employee.getId());
    ps.setString(2, employee.getName());
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  17. #37
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    177
    Rep Power
    1

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

    really thank you, but innocently my problem not how to use getxx and insert it in DB, but with this Line
    Java Code:
    cellStoreSet = (Set) dataHolder.get(i);
    where dataHolder is a hashSet (get the data from excel in it) cellStoreSet is another Set use it to iterate the Set to Preparesteatment

    Java Code:
    private static void saveToDatabase(Set dataHolder) {
    PreparedStatement ps = conn.prepareStatement("insert into employee (id, name) values (?,?)");
     Set cellStoreSet = null;
     Set<Employee> set = new HashSet<Employee>()
     Iterator it = set.iterator();
     while (it.hasNext()){
     Employee obj = iterator.next();
    
    ps.setInt(1, employee.getId());
    ps.setString(2, employee.getName());
     }
      }
    The messing is to iterate the Set dataHolder, so i suppose it should be so
    Java Code:
    private static void saveToDatabase(Set dataHolder) {
    PreparedStatement ps = conn.prepareStatement("insert into employee (id, name) values (?,?)");
     //Set cellStoreSet = null;
    // Set<Employee> set = new HashSet<Employee>()
     Iterator it = dataHolder.iterator();
     while (it.hasNext()){
     Employee obj = iterator.next();
    
    ps.setInt(1, employee.getId());
    ps.setString(2, employee.getName());
     }
      }
    Something wrong?

  18. #38
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

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

    Yes, the second one is correct.
    Your original collection was a List<List<Cell>>, which would have been obvious if you had been using generics (and you really should).
    You wanted a Set so that you would not be including duplicates, so you went for Set<List<Cell>>.
    Since that didn't work (as the equals and hashCode methods on a List do not do what you need) I suggested creating a class to model a row in your spreadsheet, which gave you a Set<YourClass>.
    The YourClass was replacing the List<Cell>.
    So no need for the iterator over the list of Cells.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  19. #39
    shell is offline Member
    Join Date
    May 2014
    Posts
    3
    Rep Power
    0

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

    How to go about this when we have duplicates in the primary key set ??

  20. #40
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

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

    Then either it's not a primary key, or you need to come up with some rule, eg the newer rows are considered updates of the earlier ones.
    But that's entirely driven by requirements.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Page 2 of 3 FirstFirst 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
  •