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

PrepareStatement to build query on MYSQL server


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);
      public static Vector read(String fileName)    {
            Vector cellVectorHolder = new Vector();
                  FileInputStream myInput = new FileInputStream(fileName);
                  POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
                  HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
                  HSSFSheet mySheet = myWorkBook.getSheetAt(0);
                  Iterator rowIter = mySheet.rowIterator();
                        HSSFRow myRow = (HSSFRow) rowIter.next();
                        Iterator cellIter = myRow.cellIterator();
                        Vector cellStoreVector=new Vector();
                              HSSFCell myCell = (HSSFCell) cellIter.next();
            }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();
                  //System.out.println("value username--->"+username);
                  //System.out.println("value password-->"+password);
                        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+"'");
                        while (rs1.next()){
                              String rec1 = rs1.getString(1);
                              String rec2 = rs1.getString(2);
                              System.out.println("rec1 is--"+rec1+"---rec2 is---"+rec2);

                  catch(Exception e){

I am getting console output like

rec1 is--jim---rec2 is---yyy
rec1 is--bill---rec2 is---zzz

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
2 Solutions
Tomas Helgi JohannssonCommented:

Put this line into the

}Catch(Exception e) {
     System.out.println("ErrorMsg " + e.getMessage());

compile and run it again to see in more detail what the error message you are getting.

    Tomas Helgi
Pramod KumarCommented:
Just put below under catch block which will print full exception stack so that we can get the exact line number from where the nullpointer exception is thrown. This would help a lot to fix the issue.

 catch(Exception e){
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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