Logic Issue to While getting missing records

[sai rama krishna]

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class POIRIRaw4EEb {
      public static void main( String [] args ) {
            String fileName="C:\\File.xls";
            Vector dataHolder=read(fileName);
            checkDatabase(dataHolder);
      }
      public static Vector read(String fileName)    {
            Vector cellVectorHolder = new Vector();
            try{
                  FileInputStream myInput = new FileInputStream(fileName);
                  POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
                  HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
                  HSSFSheet mySheet = myWorkBook.getSheetAt(0);
                  Iterator rowIter = mySheet.rowIterator(); 
                  while(rowIter.hasNext()){
                        HSSFRow myRow = (HSSFRow) rowIter.next();
                        Iterator cellIter = myRow.cellIterator();
                        Vector cellStoreVector=new Vector();
                        while(cellIter.hasNext()){
                              HSSFCell myCell = (HSSFCell) cellIter.next();
                              cellStoreVector.addElement(myCell);
                        }
                        cellVectorHolder.addElement(cellStoreVector);
                  }
            }catch (Exception e){e.printStackTrace(); }
            return cellVectorHolder;
      }
    private static void checkDatabase(Vector dataHolder) {
        String username="";
        String password="";
            List missingList = new ArrayList();
            
        for (int i=1;i<dataHolder.size(); i++){
              Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
              HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(0);
              String stringCellValue = myCell.toString();
              username = stringCellValue;
              
              myCell = (HSSFCell)cellStoreVector.elementAt(1);
              stringCellValue = myCell.toString();
              password=stringCellValue;            
  
              
              try{      
                    
                    
                 
                   /* PreparedStatement ps = con.prepareStatement("select null from login where username=? and password=?");
                  //(in the loop)
                  ps.setString(1, username);
                  ps.setString(2, password);
                  ResultSet rs = ps.executeQuery();
                  if(rs.next()) {
                    // Found
                  }*/
                    
                    
                    
                    Class.forName("com.mysql.jdbc.Driver").newInstance();
                    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
                //   Statement stmt = con.createStatement();
                //  ResultSet rs1 = stmt.executeQuery("select * from login where username='"+username+"' and password='"+password+"'");
                    //PreparedStatement psmt=null;
                      PreparedStatement psmt = con.prepareStatement("select null from login where username=? and password=?");
                    psmt.setString(1, username);
                  psmt.setString(2, password);
                  ResultSet rs1 = psmt.executeQuery(); 
                      System.out.println("rs1-->"+rs1);
                              boolean isExist = false;
                              if (rs1!=null) {
                                    while (rs1.next()){
                                            isExist = true;
                                            String rec1 = rs1.getString(1);
                                            String rec2 = rs1.getString(2);
                                            System.out.println("rec1 is--"+rec1+"---rec2 is---"+rec2);
                                    }
                              }
                              
                              if (!isExist) {
                                  Map mapDetail = new HashMap();
                                  mapDetail.put("username",username);
                                  mapDetail.put("password",password);
                                    missingList.add(mapDetail);
                              }


                  //  pstmt.close();
                    con.close();
              }
              catch(Exception e){
                    
                    System.out.println("e-->"+e);
              }
        }
            
            System.out.println("Missing List : "+missingList);
  }
}

I have above program which reads File.xls like above.

I supposed to get missing list into a array list after checking database.

I am not getting the missed list. It always prints all excel values.

Logic below seems not correct


if (rs1!=null) {
while (rs1.next()){
isExist = true;
String rec1 = rs1.getString(1);
String rec2 = rs1.getString(2);
System.out.println("rec1 is--"+rec1+"---rec2 is---"+rec2);
}
}

if (!isExist) {
Map mapDetail = new HashMap();
mapDetail.put("username",username);
mapDetail.put("password",password);
missingList.add(mapDetail);
}



Ca n you please advise.Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance

[harshvardhan ojha]

What are you trying to achieve here??
select null from login - i have never seen select null.

If required you can use IS NULL and IS NOT NULL operators instead.

What have i understood out of your code is you want to identify what is in database and not present in your excel or vice versa.

please keep a field as LastUpdated and increment it to now() whenever you are adding simething. And next time when you will be back just check what values are missing and import those. We call it delta imports.

Please help me to understand if this is what you want.

[Aleksey Vladimirovich]

The logic, you pointed out, seems to be workable... I can't get how do you get all excel values printed, using this query:
select null from login where username=? and password=?
How can you get values in 1st and 2nd columns of result set, using this:

String rec1 = rs1.getString(1);  
String rec2 = rs1.getString(2); 

when your rs1 should contain only 1 column, named "null"?

[sai rama krishna]

>>What have i understood out of your code is you want to identify what is in database and not present in your excel or vice versa.


That is correct.

>>>please keep a field as LastUpdated and increment it to now() whenever you are adding simething. And next time when you will be back just check what values are missing and import those. We call it delta imports.

Can you please provide some sample code, links, resources to achieve this.

[Pablo Abbate]

In the other hand ... you should not use Vector ...Use ArrayList instead.