PrepareStatement to build query on MYSQL server

Posted on 2012-08-30
Last Modified: 2012-08-31

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.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);
                        Iterator cellIter = myRow.cellIterator();
                        Vector cellStoreVector=new Vector();
                              HSSFCell myCell = (HSSFCell);
            }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 ({
                              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
Question by:gudii9
    LVL 24

    Assisted Solution

    by:Tomas Helgi Johannsson

    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
    LVL 10

    Accepted Solution

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The viewer will learn how to implement Singleton Design Pattern in Java.
    This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now