[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1132
  • Last Modified:

Statement vs PreparedStatement

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



Above application enhance to use prepared statement instead of  statement as 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);
                  }
            }
      }
}





I would like to know what are the differences between using statement and prepared statement and the impact with respect to performance.


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



What are advantages, disadvantages uses of statements and prepared statement.
I have not understood concept of precompiling with prepared statement.Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance

0
gudii9
Asked:
gudii9
  • 4
  • 3
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Howdy....

When the DBMS receives a SQL string, it has to parse the string for correctness, and generate meta-code for the database to execute.  The act of generating this meta-code is called "preparing" the statement.

It really doesn't matter much if you prepare the statement or if you let the DBMS do it, the performance will be about the same.

However, if you have reason to run the same SQL repeatedly, preparing the statement in your code and submitting the prepared object can result in better performance.


Good Luck,
Kent
0
 
gudii9Author Commented:
>>>PreparedStatement psmt = con.prepareStatement("select null from login where username=? and password=?");
what is the meaning null in the query. why are we using null.


>>psmt.setString(1, username);
>>                  psmt.setString(2, password);



What  is meaning of 1, 2  above. Is it correspond to first question mark(?) and second question mark(?)

>>>However, if you have reason to run the same SQL repeatedly, preparing the statement in your code and submitting the prepared object can result in better performance.



in the code where are we preparing the object. Please advise
0
 
Kent OlsenData Warehouse Architect / DBACommented:
>>>PreparedStatement psmt = con.prepareStatement("select null from login where username=? and password=?");
what is the meaning null in the query. why are we using null.

The prepareStatement () function is being called to prepare the statement for execution by the DBMS.  Note that the SQL string has two question marks.  These are parameter "place holders".  A subsequent API call will fill in the parameters.

Selecting NULL in that context is a bit odd, but it's still valid.  I suspect that the programmer intends to test the number of results returned, not the actual value returned.  If the statements returns 1 row, then the userid/password combination was found in the database.


>>psmt.setString(1, username);
>>                  psmt.setString(2, password);

These are the function calls that substitute the parameter values into the prepared statement.  The first question mark in the original SQL is replaced by the value in the username variable.  The second question mark is replaced by the value in the password variable.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
gudii9Author Commented:
>>The prepareStatement () function is being called to prepare the statement for execution by the DBMS.

>>>preparing the statement in your code and submitting the prepared object can result in better performance.
>>>substitute the parameter values into the prepared statement.



We are preparing the statement to substitute the parameter values to be substitued into the prepared object tight. Please advise
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi gudii,

I don't understand your question.  Sorry....
0
 
gudii9Author Commented:
Using Prepared Statement We are preparing the statement to substitute the parameter values to be put into the prepared object right?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
That's correct.  You prepare the statement, substitute the parameter values, then execute the prepared statement.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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