What is PreparedStatement in JDBC?

PreparedStatementinterface extends the Statement interface, which represents a precompiled SQL statement that can be executed multiple times. It accepts a parameterized SQL query, and you can pass 0 or more parameters to this query.

Initially this statement uses placeholders "?" instead of parameters, later you can pass parameters to implement these dynamically using the setXXX() method of the PreparedStatement interface .

Creating a PreparedStatement

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

When you call this method, the Connection object sends the given query to the database to compile and save it. If the query compiles successfully, only the object is returned.

To compile the query, the database does not require any values, so you can use (zero or more) placeholders (question marks” ?”) in place of the query value in .

For example, if there is a table named Employee in the database, create it using the following query.

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

You should then be able to insert values ​​into it using prepared statements as shown below.

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

Set the value of the placeholder

PreparedStatementThe interface provides several setter methods, such as setInt(), setFloat(), setArray(), setDate() , setDouble(), etc., used to set the value of the placeholder of the precompiled statement.

These methods accept two parameters, one is an integer value representing the placeholder position index, and the other is an int, String, float, etc. representing the value you need to insert.

You can use the setter method in the following example to set the value of the placeholder for the statement created above:

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

Execute Prepared Statement

After creating the PreparedStatement object, you can Use one of the execute() methods of the PreparedStatement interface to execute it, namely execute (), executeUpdate() and executeQuery().

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

• executeQuery(): This method executes

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

You can execute the prepared statement created above as follows:

Example

In this example, we try to use prepared The statement inserts values ​​into the table named "Employees".

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 validate the database, you can observe the values ​​inserted in the table:

+---------+--------+----------------+
| Name    | Salary | Location       |
+---------+--------+----------------+
| Amit    | 30000  | Hyderabad      |
| Kalyan  | 40000  | Vishakhapatnam |
| Renuka  | 50000  | Delhi          |
| Archana | 15000  | Mumbai         |
+---------+--------+----------------+
5 rows in set (0.00 sec)

The above is the detailed content of What is PreparedStatement in JDBC?. For more information, please follow other related articles on the PHP Chinese website!