Prepared statement in Java

Prepared statement:

A prepared statement is a statement that is pre-compiled SQL statement, and it is a sub-interface of a statement. Compared to other statement objects in java, Prepared Statement objects have some useful additional features.

Advantages of Prepared Statement

The prepared Statement is created when the SQL query is passed as a parameter.

we can use the same Prepared Statement and supply different parameters at the time of execution

The SQL injection attacks are prevented by prepared Statement.

Creating preparedStatement object:
PreparedStatement pstmt = null;
try {
   String SQL = "Update student SET dept = ? WHERE id = ?";
   pstmt = conn.prepareStatement(SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}

In JDBC, the parameters that are presented are represented by the symbol “?” which is also known as the parameter marker and before executing the sql statement we should supply value to every parameter.

Closing preparedStatement object:
PreparedStatement pstmt = null;
try {
   String SQL = "Update Student SET dept = ? WHERE id = ?";
   pstmt = conn.prepareStatement(SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   pstmt.close();
}

Example 1:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class TestApplication {
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id,name, age FROM Employees";
   static final String UPDATE_QUERY = "UPDATE Employees set age=? WHERE id=?”;


   public static void main (String[] args) {   
// opening a connection 
   try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         PreparedStatement stmt = conn.prepareStatement(UPDATE_QUERY);
      ) {       
         // Bind values into the parameters.
         stmt.setInt(1, 101);  // This would set age
         stmt.setInt(2, 102); // This would set ID
         int rows = stmt.executeUpdate();
         System.out.println("Rows impacted: " + rows );
         //select all the rows and display them
         ResultSet rs = stmt.executeQuery(QUERY);       


         // Extract data from result set
         while (rs.next()) {
            // Retrieve by column name
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", Name: " + rs.getString("name"));
         }
         rs.close();
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Result:

Return value is : false
Rows impacted : 1
ID: 100, Age: 18, Name: Rani
ID: 101, Age: 25, Name: Raju
ID: 102, Age: 35, Name: Ramu
ID: 103, Age: 30, Name: Rahul

Example 2:

import java.sql.*;


public class jdbcConn {
   public static void main(String[] args) throws Exception {
      Class.forName("org.apache.derby.jdbc.ClientDriver");
      Connection con = DriverManager.getConnection ( 
         "jdbc:derby://localhost:1527/testDb","name","pass");
      PreparedStatement updateemp = con.prepareStatement(
         "insert into emp values(?,?,?)");
      
      updateemp.setInt(1,18);
      updateemp.setString(2,"Mounika");
      updateemp.setString(3, "Engineer");
      updateemp.executeUpdate();
      
      Statement stmt = con.createStatement();
      String query = "select * from emp";
      ResultSet rs =  stmt.executeQuery(query);
      System.out.println("Id Name    Job");
      
      while (rs.next()) {
         int id = rs.getInt("id");
         String name = rs.getString("name");
         String job = rs.getString("job");
         System.out.println(id + "  " + name+"   "+job);
      }      
   }
}

Output:

Id name job
18 Mounika Engineer

Difference between Statement and PreparedStatement :

Statement	PreparedStatement
When the SQL query is to be executed only once then it is used.	SQL queries are executed multiple times.
we cannot pass the parameters at runtime.	we can pass the parameters at runtime.
We can use it for CREATE, ALTER, DROP the statements.	This are used for the queries which are to be executed multiple times.
Performance is very much low in statement.	In prepared Statement performance is more better than Statement.
Statement is a base interface.	It is used to extend the statement interface.
Normal SQL queries are executed in this statement.	It used to execute dynamic SQL queries.
We cannot use statement for binary data reading.	We can use Preparedstatement for binary data reading.
We can create DDL statements.	We can create any SQL Query.
We cannot use statement for binary data writing.	We can use Preparedstatement for binary data writing.
No binary protocol in the statement is used for communication. Example: //Creating Statement Object Statement GFG = con.createStatement(); //Executing The Statement GFG.executeUpdate("CREATE TABLE EMPLOYEE(ID NUMBER NOT NULL, NAME VARCHAR)");	Binary protocol in the preparedStatement is used for communication. Example: //Creating the PreparedStatement object PreparedStatement GFG = con.prepareStatement("update STUDENT set NAME = ? where ID = ?"); //Setting values to place holders //Assigns "RAM" to first place holder GFG.setString(1, "RAM"); //Assigns "512" to second place holder GFG.setInt(2, 512); //Executing PreparedStatement GFG.executeUpdate();

← Prev Next →

Prepared statement in Java