What is PreparedStatement in JDBC?

JDBCJava 8MySQL

The PreparedStatement interface extends the Statement interface it represents a precompiled SQL statement which can be executed multiple times. This accepts parameterized SQL quires and you can pass 0 or more parameters to this query.

Initially this statement uses place holders “?” instead of parameters, later on you can pass arguments to these dynamically using the setXXX() methods of the PreparedStatement interface.

Creating a PreparedStatement

You can create an object of the PreparedStatement (interface) using the prepareStatement() method of the Connection interface. This method accepts a query (parameterized) and returns a PreparedStatement object.

When you invoke this method the Connection object sends the given query to the database to compile and save it. If the query got compiled successfully then only it returns the object.

To compile a query, database doesn’t require any values so you can use (zero or more) placeholders (Question marks ” ?”) in the place of values in the query.

For example, if you have a table named Employee in the database, created using the following query.

CREATE TABLE Employee(Name VARCHAR(255), Salary INT NOT NULL, Location VARCHAR(255));

Then the you should can use a Prepared Statement to insert values in to it as shown below.

//Creating a Prepared Statement
String query="INSERT INTO Employee(Name, Salary, Location)VALUES(?, ?, ?)";
Statement pstmt = con.prepareStatement(query);

Setting values to the place holders

The PreparedStatement interface provides several setter methods such as setInt(), setFloat(), setArray(), setDate(), setDouble() etc.. to set values to the place holders of the prepared statement.

These methods accept two arguments, one is an integer value representing the placement index of the place folder and, the other is an int or, String or, float etc… representing the value you need to insert at that particular position.

You can set values to the place holders of the above created statement using the setter methods as shown below:

pstmt.setString(1, "Amit");
pstmt.setInt(2, 3000);
pstmt.setString(3, "Hyderabad");

pstmt.setString(1, "Kalyan");
pstmt.setInt(2, 4000);
pstmt.setString(3, "Vishakhapatnam");

pstmt.setString(1, "Renuka");
pstmt.setInt(2, 5000);
pstmt.setString(3, "Delhi");

pstmt.setString(1, "Archana");
pstmt.setInt(2, 15000);
pstmt.setString(3, "Mumbai");

Executing the Prepared Statement

Once you have created the PreparedStatement object you can execute it using one of the execute() methods of the PreparedStatement interface namely, execute(), executeUpdate() and, executeQuery().

  • execute(): This method executes normal static SQL statements in the current prepared statement object and returns a boolean value.

  • executeQuery(): This method executes the current prepared statement and returns a ResultSet object.

  • executeUpdate(): This method executes SQL DML statements such as insert update or delete in the current Prepared statement. It returns an integer value representing the number of rows affected.

You can execute the above created prepared statement as shown below:

Example

In this example we are trying to insert values into a table named Employees using a prepared statement.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class PreparedStatementExample {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());

      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/testdb";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");

      //Creating a Prepared Statement
      String query = "INSERT INTO Employees(Name, Salary, Location) VALUES (?, ?, ?)";
      PreparedStatement pstmt = con.prepareStatement(query);

      pstmt.setString(1, "Amit");
      pstmt.setInt(2, 3000);
      pstmt.setString(3, "Hyderabad");

      pstmt.setString(1, "Kalyan");
      pstmt.setInt(2, 4000);
      pstmt.setString(3, "Vishakhapatnam");

      pstmt.setString(1, "Renuka");
      pstmt.setInt(2, 5000);
      pstmt.setString(3, "Delhi");

      pstmt.setString(1, "Archana");
      pstmt.setInt(2, 15000);
      pstmt.setString(3, "Mumbai");

      int num = pstmt.executeUpdate();
      System.out.println("Rows inserted ....");
   }
}

Output

Connection established......
Number of rows inserted: 1

If you verify the database, you can observe the inserted values in the table as:

+---------+--------+----------------+
| Name    | Salary | Location       |
+---------+--------+----------------+
| Amit    | 30000  | Hyderabad      |
| Kalyan  | 40000  | Vishakhapatnam |
| Renuka  | 50000  | Delhi          |
| Archana | 15000  | Mumbai         |
+---------+--------+----------------+
5 rows in set (0.00 sec)
raja
Published on 20-Mar-2019 10:38:23
Advertisements