PreparedStatement

Learn how to use PreparedStatement


Statement v/s PreparedStatement

When a query is executed by using Statement object following steps takes place on database server

  • Parse the Query

  • Compile the Query

  • Optimize the Query

  • Execute the Query

Let's say programmer has to insert some records in database using jdbc. Insert query sent to database server, above four steps take place for every record insertion.
Use of PreparedStatement for query execution is efficient than Statement in case of multiple query execution. PreparedStatement send query to database server and database server will Parse the query then compile it and optimize it, Which is known as pre-compiled query.
Next time when same query is executed then database server only execute fourth step.


PreparedStatement Insert Example:

import java.sql.*;  
 public class dataConnect
  {
    public static void main(String arg[]) throws Exception
	 {
	  // Load the Driver
	  Class.forName("oracle.jdbc.driver.OracleDriver");
	  
	  // Create connection
	  // Specify user name and password of your database server
	  Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","root","root");	  
	  System.out.println("Connected to database");
	  
	  // Pre-compiled query  
	  PreparedStatement ptmt=conn.prepareStatement("insert into emp values (?,?)");
	  
	  // Setting values in query
	  ptmt.setInt(1,101);
	  ptmt.setString(2,"john");
	  
	  
	  int count=ptmt.executeUpdate();
	
		rs.close();
		ptmt.close();
		conn.close();
	 
	 }
	 
  }


PreparedStatement Select Example:

import java.sql.*;  
 public class dataConnect
  {
    public static void main(String arg[]) throws Exception
	 {
	  // Load the Driver
	  Class.forName("oracle.jdbc.driver.OracleDriver");
	  
	  // Create connection
	  // Specify user name and password of your database server
	  Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","root","root");	  
	  System.out.println("Connected to database");
	  
	  // Pre-compiled query  
	  PreparedStatement ptmt=conn.prepareStatement("select * from emp where name=?");
	  
	  // Setting values in query
	  ptmt.setString(1,"rakesh");
	 
	  	  
	  ResultSet rs=ptmt.executeQuery();
	  
	  while(rs.next())
	   {
	    System.out.println("Name:"+rs.getString(2));
	   
	   }
	
		rs.close();
		ptmt.close();
		conn.close();
	 
	 }
	 
  }

PreparedStatement Update Example:

import java.sql.*;  
 public class dataConnect
  {
    public static void main(String arg[]) throws Exception
	 {
	  // Load the Driver
	  Class.forName("oracle.jdbc.driver.OracleDriver");
	  
	  // Create connection
	  // Specify user name and password of your database server
	  Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","root","root");	  
	  System.out.println("Connected to database");
	  
	  // Pre-compiled query  
	  PreparedStatement ptmt=conn.prepareStatement("update emp set name=? where id=?");
	  
	  // Setting values in query
	  ptmt.setString(1,"rakesh");
	  ptmt.setString(2,101);
	  	  
	  int count=ptmt.executeUpdate();
	
		rs.close();
		ptmt.close();
		conn.close();
	 
	 }
	 
  }

Difference between Statement and PreparedStatement