asked on

Logic Incorrect

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 pri

Logic here 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

gudii9

ASKER

In my case I only ghave jim and bill in the database
I do not have greg in database.

So console missing list I should only see the greg.

But I am seeing all 3 ie jim, bill, greg as below

rs1-->com.mysql.jdbc.JDBC4ResultSet@18dfef8
rec1 is--jim---rec2 is---yyy
rs1-->com.mysql.jdbc.JDBC4ResultSet@b61fd1
rec1 is--bill---rec2 is---zzz
rs1-->com.mysql.jdbc.JDBC4ResultSet@1c99159
Missing List : [{username=jim, password=yyy}, {username=bill, password=zzz}, {username=greg, password=ccc}]
mysql10.JPG

gudii9

ASKER

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

I chaned above code

as shown below

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 (rs1==null) {
Map mapDetail = new HashMap();
mapDetail.put("username",username);
mapDetail.put("password",password);
missingList.add(mapDetail);
}

That also did not help much. Please advise

CEHJ

Looking///

CEHJ

Sounds like you're not emptying the List at any point, which perhaps you ought to do...

Manikandan Thiagarajan

change the query select * from login and check it again

gudii9

ASKER

>>>Sounds like you're not emptying the List at any point, which perhaps you ought to do...\
How can I empty list. Please provide me sample code to do it

>>>change the query select * from login and check it again

Change the query to which one. I am not clear on what to modify in query.

Please advise

ASKER CERTIFIED SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gudii9

ASKER

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.clear();
missingList.add(mapDetail);
//missingList.clear();

}

// pstmt.close();
con.close();
}
catch(Exception e){

System.out.println("e-->"+e);
}
}

System.out.println("Missing List : "+missingList);
}
}

i have added clear statement before and after
missingList.add(mapDetail);
but it did not help. Please advise. Am I adding at right location or not

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

                                                                                           missingList.clear();
                              missingList.add(mapDetail);
                              // missingList.clear();
                        }

Manish

What is output of query in database, kindly post it .
select null from login where username=? and password=? when you pass values as jim and yyy .

gudii9

ASKER

it gives 1 as in screenshot when i run
select null from login where username='jim' and password='yyy'
mysql11.JPG

CEHJ

select null from login where username='jim' and password='yyy'

But that's not the sql you used

gudii9

ASKER

it finally worked as follows

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

                        }

not sure what second if loop is doing

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


                              missingList.add(mapDetail);

                        }

CEHJ

:)
btw, the last comment i made refers to the incorrect sql in the console interface