«Back to Home

Core Java
Topics

PreparedStatement Interface In Java

PreparedStatement Interface
 
In JDBC, The PreparedStatement interface is a sub interface of Statement and it is used to execute parameterized query.
 
The example is given below.
 
String str="insert into student values(?,?,?,?)";
 
We are passing the parameter (?) for the values and its value will be set by calling the setter methods of PreparedStatement.

Why we use PreparedStatement?

PreparedStatement is mainly used to improve the performance.
 
The performance of the Application will be faster, if we use PreparedStatement interface because query is compiled only once.
 
Get the PreparedStatement object

The PrepareStatement() method of connection interface is used to return the PreparedStatement object.
 
Syntax

public PreparedStatement prepareStatement(String query)throws SQLException{}
 
Methods of PreparedStatement interface
 
public void setInt(int paramIndex, int value)

This method is used to set the integer value to the given parameter index.
 
public void setString(int paramIndex, String value)

This method is used to set the string value to the given parameter index.
 
public void setFloat(int paramIndex, float value)

This method is used to set the float value to the given parameter index.
 
public void setDouble(int paramIndex, double value)

This method is used to set the double value to the given parameter index.
 
public int executeUpdate()

This method is used to execute the query. It is used to create, drop, insert, update, delete etc.
 
public ResultSet executeQuery()

This method is used to execute the select query. It returns an instance of ResultSet.
 
Let’s see an example1 to insert the records, given below.
 
Code
  1. import java.sql.*;  
  2. public class StudentDatabase2 {  
  3.     public static void main(String args[]) throws Exception {  
  4.         Class.forName("org.apache.derby.jdbc.ClientDriver");  
  5.         String url = "jdbc:derby://localhost:1527/Student";  
  6.         String username = "Student";  
  7.         String password = "student";  
  8.         Connection conn = DriverManager.getConnection(url, username, password);  
  9.         PreparedStatement st = conn.prepareStatement("insert into STUDENT.STUDENTDB values(?,?,?,?)");  
  10.         st.setInt(1, 289);  
  11.         st.setString(2, "David");  
  12.         st.setInt(3, 45);  
  13.         st.setString(4, "JTMN college");  
  14.         int i = st.executeUpdate();  
  15.         System.out.println(i + " :records inserted");  
  16.         conn.close();  
  17.     }  
  18. }  
9

Output

10

Let’s see an example2 to update the records, given below.
 
Code
  1. import java.sql.*;  
  2.   
  3. public class StudentDatabase2 {  
  4.     public static void main(String args[]) throws Exception {  
  5.         Class.forName("org.apache.derby.jdbc.ClientDriver");  
  6.         String url = "jdbc:derby://localhost:1527/Student";  
  7.         String username = "Student";  
  8.         String password = "student";  
  9.         Connection conn = DriverManager.getConnection(url, username, password);  
  10.         PreparedStatement st = conn.prepareStatement("update STUDENT.STUDENTDB set STUDNAME=? where STUDID=?");  
  11.         st.setString(1, "Angel");  
  12.         st.setInt(2, 289);  
  13.         int i = st.executeUpdate();  
  14.         System.out.println(i + " records updated");  
  15.         conn.close();  
  16.     }  
  17. }  
11

Output

12

Let’s see an example3, given below, to delete the records.
 
Code
  1. import java.sql.*;  
  2. public class StudentDatabase2 {  
  3.     public static void main(String args[]) throws Exception {  
  4.         Class.forName("org.apache.derby.jdbc.ClientDriver");  
  5.         String url = "jdbc:derby://localhost:1527/Student";  
  6.         String username = "Student";  
  7.         String password = "student";  
  8.         Connection conn = DriverManager.getConnection(url, username, password);  
  9.         PreparedStatement st = conn.prepareStatement("delete from STUDENT.STUDENTDB where STUDID=?");  
  10.         st.setInt(1, 289);  
  11.         int i = st.executeUpdate();  
  12.         System.out.println(i + " records deleted");  
  13.         conn.close();  
  14.     }  
  15. }  
13

Output

14

Let’s see an example4, given below, to retrieve the records.
 
Code
  1. import java.sql.*;  
  2. public class StudentDatabase2 {  
  3.   
  4.     public static void main(String args[]) throws Exception {  
  5.         Class.forName("org.apache.derby.jdbc.ClientDriver");  
  6.         String url = "jdbc:derby://localhost:1527/Student";  
  7.         String username = "Student";  
  8.         String password = "student";  
  9.         Connection conn = DriverManager.getConnection(url, username, password);  
  10.         PreparedStatement st = conn.prepareStatement("select * from STUDENT.STUDENTDB");  
  11.         ResultSet rs = st.executeQuery();  
  12.         while (rs.next()) {  
  13.             System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4));  
  14.         }  
  15.         conn.close();  
  16.     }  
  17. }  
15

Output

16

Let’s see an example5, given below, to insert the records until user presses no.
 
Code
  1. import java.sql.*;  
  2. import java.io.*;  
  3. public class StudentDatabase2 {  
  4.     public static void main(String args[]) throws Exception {  
  5.         Class.forName("org.apache.derby.jdbc.ClientDriver");  
  6.   
  7.         String url = "jdbc:derby://localhost:1527/Student";  
  8.         String username = "Student";  
  9.         String password = "student";  
  10.         Connection conn = DriverManager.getConnection(url, username, password);  
  11.         PreparedStatement ps = conn.prepareStatement("insert into STUDENT.STUDENTDB values(?,?,?,?)");  
  12.         BufferedReader br = new BufferedReader(new InputStreamReader(System.in));  
  13.         do {  
  14.             System.out.println("Enter STUDID:");  
  15.             int id = Integer.parseInt(br.readLine());  
  16.             System.out.println("Enter STUDNAME:");  
  17.             String name = br.readLine();  
  18.             System.out.println("Enter STUDAGE:");  
  19.             int age = Integer.parseInt(br.readLine());  
  20.             System.out.println("Enter STUDCOLLEGE:");  
  21.             String college = br.readLine();  
  22.             ps.setInt(1, id);  
  23.             ps.setString(2, name);  
  24.             ps.setFloat(3, age);  
  25.             ps.setString(4, college);  
  26.             int i = ps.executeUpdate();  
  27.             System.out.println(i + " records affected");  
  28.             System.out.println("Do you want to continue: y/n");  
  29.             String s = br.readLine();  
  30.             if (s.startsWith("n")) {  
  31.                 break;  
  32.             }  
  33.         } while (true);  
  34.         conn.close();  
  35.     }  
  36. }  
17
18

Output

19

21

Summary

Thus, we learnt, JDBC PreparedStatement interface is a sub interface of statement. It is used to execute the parameterized query and also learnt its important methods in Java.