How to Use PreparedStatement in Java?
Last Updated :
13 Sep, 2022
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
import java.sql.*;
public class GFG {
public static void main(String[] args) throws Exception
{
Class.forName( "org.apache.derby.jdbc.ClientDriver" );
Connection con = DriverManager.getConnection();
String query
= "Select * from students where age> ? and name = ?" ;
PreparedStatement myStmt
= con.prepareStatement(query);
myStmt.setInt( 1 , 20 );
myStmt.setString( 2 , 'Prateek' );
ResultSet myRs = myStmt.executeQuery();
System.out.println( 'Age Name' );
while (myRs.next()) {
String Name = rs.getString( "name" );
int age = rs.getInt( "age" );
System.out.println(Name + " " + age);
}
con.close();
}
}
|
Output
Execute Update Example Code
Java
import java.sql.*;
public class GFG {
public static void main(String[] args) throws Exception
{
Class.forName( "org.apache.derby.jdbc.ClientDriver" );
Connection con = DriverManager.getConnection();
String query = "insert into Students values(?,?)" ;
PreparedStatement myStmt
= con.prepareStatement(query);
myStmt.setInt( 1 , 21 );
myStmt.setString( 2 , 'Prajjwal' );
int res = myStmt.executeUpdate();
System.out.println(res + " records inserted" );
con.close();
}
}
|
Output
Table After Inserting Values
Share your thoughts in the comments
Please Login to comment...