PrepareStatement to build query with parameters based on spreadsheet data

[sai rama krishna]

Hi,

I would like to use PrepareStatement to build my query by supplying the parameters based on spreadsheet data on mysql database. How can i achieve it.


I wrote my application like

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 POIRIRaw4EE {
	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;
			
			//System.out.println("value username--->"+username);
			//System.out.println("value password-->"+password);
			
			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 am getting console output like

rs1-->com.mysql.jdbc.JDBC4ResultSet@1006d75
rec1 is--jim---rec2 is---yyy
e-->java.lang.NullPointerException
rs1-->com.mysql.jdbc.JDBC4ResultSet@1ccce3c
rec1 is--bill---rec2 is---zzz
e-->java.lang.NullPointerException



since those excel(File.xls) records are already there in login table as well. Now I would like to I would like extend program to use PrepareStatement to build my query by supplying the parameters based on spreadsheet data.

Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance

[R. Jain]

See PreparedStatement at JdbcFaq

[Campbell Ritchie]

Moving to our databases fora.

You should divide that task into smaller pieces. Look for POI which I have never used, but people say it can access spreadsheets.

[sai rama krishna]

I modified my program as below.

If there are missing records in excel compared to database it is sending to hashmap and printing to console. Instead I want to generate output excel report and email to user. Please advise on how can i do it.

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);
  }
}

[Winston Gutkowski]

sai rama krishna wrote:I would like to use PrepareStatement to build my query by supplying the parameters based on spreadsheet data on mysql database. How can i achieve it.

I think the others have covered the semantic part, but I'd question the design. Seems to me that storing a spreadsheet in a database is the height of redundancy; and I suspect it'll cause you problems in the future (if it hasn't already).

Winston

[sai rama krishna]

Sorry for not communicating properly. I am not storing excel in database. I am reading username and password informationf from File.xls and then check if the database has those username password information. If any username and password is there in excel but not in the database, then i need to generate missaingRecord excel report. Please advise