• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1108
  • Last Modified:

Exception loop for null result set

Hi,

I am working on following java application based out of following link

http://www.roseindia.net/tutorial/java/poi/insertExcelFileData.html

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.Iterator;
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="";
            PreparedStatement pstmt = null;      
            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{      
                        
                        
                        
                        
                        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+"'");
                        
                        System.out.println("rs1-->"+rs1);
                        while (rs1.next()){
                              String rec1 = rs1.getString(1);
                              String rec2 = rs1.getString(2);
                              System.out.println("rec1 is--"+rec1+"---rec2 is---"+rec2);
                        }


                        pstmt.close();
                        con.close();
                  }
                  catch(Exception e){
                        
                        System.out.println("e-->"+e);
                  }
            }
      }
}



I have  query formed out of attached excel and checks database and and if it finds the corresponding record displays to console.  I need to check if rs1 is null/0, if it is null/0 then create exception loop to send that particular missing username and password information as a excel report and email that excel attachment. Here rs1 is in for loop so checks one after other record. Please advise on how to generate missing record excel output file through exception block and later email that attachment.Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance.
File.xls
0
gudii9
Asked:
gudii9
  • 9
  • 8
2 Solutions
 
CEHJCommented:
As i've said several times, you should be using a PreparedStatement
0
 
Amitkumar PSr. ConsultantCommented:
Agree with CEHJ, need to use prepared statement here to get the performance.

I have updated the checkDatabase() method slightly to fulfill the requirement. Basically, added a flag to figure out that the detail is exist or not. If not the details will be captured in the map and prepare list of maps having missing information in db.
At the end of method, printing the values that are missing.

Check and verify.


      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{      
                        
                        
                        
                        
                        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+"'");
                        
                        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();
							map.put("username",username);
							map.put("password",password);
							missingList.add(mapDetail);
						}

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

Open in new window

0
 
gudii9Author Commented:
>>Statement stmt = con.createStatement();
>>ResultSet rs1 = stmt.executeQuery("select * from login where username='"+username+"' and password='"+password+"'");
>>System.out.println("rs1-->"+rs1);

How do I change above lines to use PreparedStatement instead of regular statement.

>>System.out.println("Missing List : "+missingList);
How do I generate excel output report out of missing list Hash Map.
Please advise.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CEHJCommented:
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
}

Open in new window

0
 
gudii9Author Commented:
This works well. Thank you.

>>>what does following lines do         
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);


In case of statement it is straight foward without setString etc methods like

Statement stmt = con.createStatement();
                        ResultSet rs1 = stmt.executeQuery("select * from login where username='"+username+"' and password='"+password+"'");
                       
                        System.out.println("rs1-->"+rs1);
                        while (rs1.next()){



>>>            if (!isExist) {


What is the meaning of above line.
>>!isExist
Which does not exist put it in hash map?

How to put this missing hash map into a output excel file of attached format. Please advise
File-Output.xls
0
 
CEHJCommented:
In case of statement it is straight foward without setString etc methods like

No.

a. PreparedStatements with their setX methods are optimised for repeated execution in a loop
b. with the password CEHJ'2 your code will break (try it)

What is the meaning of above line.
No idea. Would have to see the context.
0
 
gudii9Author Commented:
>>>with the password CEHJ'2 your code will break (try it)

What you mean by CEHJ'2

Do I need to put that in excel or in the login table or in the query. I am not clear on this. Please advise
0
 
CEHJCommented:
That's just an example of why you should use the code i posted for PreparedStatement
0
 
gudii9Author Commented:
>>with the password CEHJ'2 your code will break (try it)


How can I try this and break it. Please advise.
0
 
CEHJCommented:
Use the old code you had

 password = "CEHJ'2";
ResultSet rs1 = stmt.executeQuery("select * from login where username='"+username+"' and password='"+password+"'");

Open in new window

0
 
gudii9Author Commented:
>> password = "CEHJ'2";
>>ResultSet rs1 = stmt.executeQuery("select * from login where username='"+username+"' and password='"+password+"'");



you mean  password = "CEHJ'2";

breaks below


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.Iterator;
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="";
            PreparedStatement pstmt = null;      
            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{      
                       
                       
                       
                       
                        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+"'");
                       
                        System.out.println("rs1-->"+rs1);
                        while (rs1.next()){
                              String rec1 = rs1.getString(1);
                              String rec2 = rs1.getString(2);
                              System.out.println("rec1 is--"+rec1+"---rec2 is---"+rec2);
                        }


                        pstmt.close();
                        con.close();
                  }
                  catch(Exception e){
                       
                        System.out.println("e-->"+e);
                  }
            }
      }
}





>>>you mean  password = "CEHJ'2";



where as it will not break below code


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.Iterator;
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="";
            PreparedStatement pstmt = null;      
            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{      
                       
                       
                       
                       
                        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+"'");
                       
                        System.out.println("rs1-->"+rs1);
                        while (rs1.next()){
                              String rec1 = rs1.getString(1);
                              String rec2 = rs1.getString(2);
                              System.out.println("rec1 is--"+rec1+"---rec2 is---"+rec2);
                        }


                        pstmt.close();
                        con.close();
                  }
                  catch(Exception e){
                       
                        System.out.println("e-->"+e);
                  }
            }
      }
}


please advise on why it breaks.
0
 
CEHJCommented:
where as it will not break below code
Where is the password that i gave you entering the code? If from the spreadsheet, please attach the spreadsheet file
0
 
gudii9Author Commented:
Here is the Excel
File.xls
0
 
CEHJCommented:
The password i gave you as an example is not in there
0
 
gudii9Author Commented:
Sorry, Here is updated spreadsheet

with prepared statement i got console output as

rs1-->com.mysql.jdbc.JDBC4ResultSet@bb7465
e-->java.sql.SQLException: Column Index out of range, 2 > 1.
rs1-->com.mysql.jdbc.JDBC4ResultSet@122cdb6
e-->java.sql.SQLException: Column Index out of range, 2 > 1.
rs1-->com.mysql.jdbc.JDBC4ResultSet@bfc8e0
Missing List : [{username=jill, password=CEHZ2}]




with old statement i got console output as which seems broken
rs1-->com.mysql.jdbc.JDBC4ResultSet@15e83f9
rec1 is--jim---rec2 is---yyy
e-->java.lang.NullPointerException
rs1-->com.mysql.jdbc.JDBC4ResultSet@ef5502
rec1 is--bill---rec2 is---zzz
e-->java.lang.NullPointerException
rs1-->com.mysql.jdbc.JDBC4ResultSet@11f2ee1
e-->java.lang.NullPointerException
File.xls
0
 
CEHJCommented:
The password i gave you isn't in there either - just something like it
0
 
gudii9Author Commented:
I was under impression that it should break the new preparedstatement program but not the old statement program. But it actually broke the pld statement program instead of new preparedstatement program. I am more confusedn now. Please advise
0
 
CEHJCommented:
Please advise
I did
b. with the password CEHJ'2 your code will break (try it)
The emphasis is on the word 'your'
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now