In the post Statement interface in Java we have already seen how you can create a Statement using Connection object and execute SQL statements. However, the Statement interface has a major limitation; it only works with static SQL queries and offers no direct way to pass parameters. Developers often resorted to string concatenation or StringBuilder to inject values, but this approach is error‑prone and vulnerable to SQL injection attacks.
To solve these issues, JDBC introduced the PreparedStatement Interface in Java, a sub‑interface of Statement designed for parameterized queries. With PreparedStatement, you can safely bind values to placeholders (?) in your SQL, making your code more secure, readable, and efficient. In this post we'll see how to use PreparedStatement in JDBC with examples
Obtaining JDBC PreparedStatement object
You can create a PreparedStatement object by calling the prepareStatement() method of the Connection class.
PreparedStatement preparedStatement = connection.prepareStatement(sql);
Advantages of using PreparedStatement in JDBC
- Parameterized Queries: You can reuse the same SQL statement with different parameter values, reducing duplication.
- Efficiency: Unlike Statement object, PreparedStatement is given the SQL statement when it is created. So the SQL is sent to the DB right away where it is already compiled. When you come to execute() method to actually execute the SQL that SQL is pre-compiled making it more efficient for repeated executions.
- Security: By separating SQL logic from parameter values, PreparedStatement prevents SQL injection attacks.
- Cleaner Syntax: No need for multiple break statements or messy string concatenation, parameters are set using methods like setInt(), setString(), etc.
Java PreparedStatement Example
Let’s see an example using PreparedStatement in JDBC. DB used here is MySql, schema is netjs and table is employee with columns id, age and name, where id is auto-generated.
In the code there are methods for insert, update, delete and select from the table.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCPrepStmt {
public static void main(String[] args) {
Connection connection = null;
try {
// Loading driver
Class.forName("com.mysql.jdbc.Driver");
// Creating connection
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs",
"root", "admin");
JDBCPrepStmt prep = new JDBCPrepStmt();
prep.insertEmployee(connection, "Kate", 24);
prep.updateEmployee(connection, 22, 30);
prep.displayEmployee(connection, 22);
//prep.deleteEmployee(connection, 24);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null){
//closing connection
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} // if condition
}// finally
}
// Method to insert
private void insertEmployee(Connection connection, String name, int age)
throws SQLException{
String insertSQL = "Insert into employee (name, age) values (?, ?)";
PreparedStatement prepStmt = null;
try {
prepStmt = connection.prepareStatement(insertSQL);
prepStmt.setString(1, name);
prepStmt.setInt(2, age);
int count = prepStmt.executeUpdate();
System.out.println("Count of rows inserted " + count);
}finally{
if(prepStmt != null){
prepStmt.close();
}
}
}
// Method to update
private void updateEmployee(Connection connection, int id, int age) throws SQLException{
String updateSQL = "Update employee set age = ? where id = ?";
PreparedStatement prepStmt = null;
try {
prepStmt = connection.prepareStatement(updateSQL);
prepStmt.setInt(1, age);
prepStmt.setInt(2, id);
int count = prepStmt.executeUpdate();
System.out.println("Count of rows updated " + count);
}finally{
if(prepStmt != null){
prepStmt.close();
}
}
}
// Method to delete
private void deleteEmployee(Connection connection, int id) throws SQLException {
String deleteSQL = "Delete from employee where id = ?";
PreparedStatement prepStmt = null;
try {
prepStmt = connection.prepareStatement(deleteSQL);
prepStmt.setInt(1, id);
int count = prepStmt.executeUpdate();
System.out.println("Count of rows deleted " + count);
}finally{
if(prepStmt != null){
prepStmt.close();
}
}
}
// Method to retrieve
private void displayEmployee(Connection connection, int id) throws SQLException{
String selectSQL = "Select * from employee where id = ?";
PreparedStatement prepStmt = null;
try {
prepStmt = connection.prepareStatement(selectSQL);
prepStmt.setInt(1, id);
ResultSet rs = prepStmt.executeQuery();
while(rs.next()){
System.out.println("id : " + rs.getInt("id") + " Name : "
+ rs.getString("name") + " Age : " + rs.getInt("age"));
}
}finally{
if(prepStmt != null){
prepStmt.close();
}
}
}
}
Points to note here:
Taking this example as reference let’s go through some of the points you will have to keep in mind when using PreparedStatement in JDBC.
- Parameterized statement– In the example you can see that all the SQL statements are parameterized and '?' is used
as a placeholder in parameterized statements. For example-
String insertSQL = "Insert into employee (name, age) values (?, ?)";
- Setter methods– Values for these placeholders are provided through setter methods. PreparedStatement
has various setter methods for different data types i.e. setInt(), setString(), setDate() etc.
General form of the setter method-
setXXX(int parameterIndex, value)
Here parameterIndex is the index of the parameter in the statement, index starts from 1. For example-
String insertSQL = "Insert into employee (name, age) values (?, ?)";
For this sql, where the first parameter is String (name) and second parameter is of type int (age), you need to set the parameters on the PreparedStatement object as follows-
prepStmt.setString(1, name); prepStmt.setInt(2, age);
- Executing PreparedStatement objects– You can use execute methods for executing the queries.
- boolean execute()- Executes the SQL statement in this PreparedStatement object, (it can be any kind of SQL query),
which may return multiple results.
Returns a boolean which is true if the first result is a ResultSet object; false if it is an update count or there are no results. - ResultSet executeQuery(String sql)- Executes the SQL statement in this PreparedStatement object, which returns a single ResultSet object. If you want to execute a Select SQL query which returns results you should use this method.
- int executeUpdate()- Executes the SQL statement in this PreparedStatement object, which may be an INSERT,
UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
Returns an int denoting either the row count for the rows that are inserted, deleted, updated or returns 0 if nothing is returned.
- boolean execute()- Executes the SQL statement in this PreparedStatement object, (it can be any kind of SQL query),
which may return multiple results.
That's all for this topic PreparedStatement Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!
>>>Return to Java Advanced Tutorial Page
Related Topics
You may also like-
