A PreparedStatement is a pre-compiled SQL statement. It is a subinterface of Statement. Prepared Statement objects have some useful additional features than Statement objects. Instead of hard coding queries, PreparedStatement object provides a feature to execute a parameterized query.
Advantages of PreparedStatement
- When PreparedStatement is created, the SQL query is passed as a parameter. This Prepared Statement contains a pre-compiled SQL query, so when the PreparedStatement is executed, DBMS can just run the query instead of first compiling it.
- We can use the same PreparedStatement and supply with different parameters at the time of execution.
- An important advantage of PreparedStatements is that they prevent SQL injection attacks.
Steps to use PreparedStatement
1. Create Connection to Database
Connection myCon = DriverManager.getConnection(path,username,password)
2. Prepare Statement
Instead of hardcoding queries like,
select * from students where age>10 and name ='Chhavi'
Set parameter placeholders(use question mark for placeholders) like,
select * from students where age> ? and name = ?
PreparedStatement myStmt; myStmt = myCon.prepareStatement(select * from students where age> ? and name = ?);
3. Set parameter values for type and position
myStmt.setInt(1,10); myStmt.setString(2,"Chhavi");
4. Execute the Query
ResultSet myRs= myStmt.executeQuery();
Methods of PreparedStatement:
- setInt(int, int): This method can be used to set integer value at the given parameter index.
- setString(int, string): This method can be used to set string value at the given parameter index.
- setFloat(int, float): This method can be used to set float value at the given parameter index.
- setDouble(int, double): This method can be used to set a double value at the given parameter index.
- executeUpdate(): This method can be used to create, drop, insert, update, delete etc. It returns int type.
- executeQuery(): It returns an instance of ResultSet when a select query is executed.
Original Table
Execute Query Example Code
Java
// Java program to execute a query using PreparedStatementimport java.sql.*;public class GFG { // Driver Code public static void main(String[] args) throws Exception { // Register Driver Class Class.forName("org.apache.derby.jdbc.ClientDriver"); // Connection to your database Connection con = DriverManager.getConnection(); // Query which needs parameters String query = "Select * from students where age> ? and name = ?"; // Prepare Statement PreparedStatement myStmt = con.prepareStatement(query); // Set Parameters myStmt.setInt(1, 20); myStmt.setString(2, 'Prateek'); // Execute SQL query ResultSet myRs = myStmt.executeQuery(); System.out.println('Age Name'); // Display function to show the Resultset while (myRs.next()) { String Name = rs.getString("name"); int age = rs.getInt("age"); System.out.println(Name + " " + age); } // Close the connection con.close(); }} |
Output
Execute Update Example Code
Java
// Java program to update a table using PreparedStatementimport java.sql.*;public class GFG { // Driver Code public static void main(String[] args) throws Exception { // Register Driver Class Class.forName("org.apache.derby.jdbc.ClientDriver"); // Connection to your database Connection con = DriverManager.getConnection(); // Query which needs parameters String query = "insert into Students values(?,?)"; // Prepare Statement PreparedStatement myStmt = con.prepareStatement(query); // Set Parameters myStmt.setInt(1, 21); myStmt.setString(2, 'Prajjwal'); // Execute SQL query int res = myStmt.executeUpdate(); // Display the records inserted System.out.println(res + " records inserted"); // Close the connection con.close(); }} |
Output
Table After Inserting Values
