Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is PreparedStatement in JDBC?
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)
9K+ Views
