The PreparedStatement interface in JDBC is used to execute parameterized SQL queries. It uses the ? placeholder to represent values that are supplied at runtime. The setInt(), setString(), setDouble(), and other setter methods are used to assign values to parameters. PreparedStatement is safer and more efficient than Statement for repeated or dynamic SQL queries.
Common Methods
| Method | Description |
|---|---|
setInt() |
Sets an integer value for a parameter. |
setString() |
Sets a string value for a parameter. |
setDouble() |
Sets a double value for a parameter. |
executeQuery() |
Executes a SELECT query and returns a ResultSet. |
executeUpdate() |
Executes INSERT, UPDATE, or DELETE queries and returns the number of affected rows. |
execute() |
Executes the SQL statement and returns whether the result is a ResultSet. |
close() |
Closes the PreparedStatement object. |
Program 1: Executing a Parameterized SELECT Query
This program retrieves an employee based on the employee ID supplied at runtime. The ? placeholder is replaced using the setInt() method before executing the query.
import java.sql.*;
import java.util.Scanner;
public class PreparedStatementSelectExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
Scanner sc = new Scanner(System.in);
System.out.print("Enter employee ID: ");
int id = sc.nextInt();
String sql = "SELECT employee_id, employee_name, salary FROM employee WHERE employee_id = ?";
try {
Connection con =
DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1,id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
System.out.println("ID: " + rs.getInt("employee_id"));
System.out.println("Name: " + rs.getString("employee_name"));
System.out.println("Salary: " + rs.getDouble("salary"));
} else {
System.out.println("Employee not found.");
}
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
sc.close();
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" PreparedStatementSelectExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" PreparedStatementSelectExample
Enter employee ID: 102
ID: 102
Name: Priya
Salary: 42000.0Code language: CSS (css)Program 2: Executing a Parameterized INSERT Query
This program inserts a new employee into the database using parameterized values. The setInt(), setString(), and setDouble() methods assign values to the ? placeholders.
import java.sql.*;
import java.util.Scanner;
public class PreparedStatementInsertExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
Scanner sc = new Scanner(System.in);
System.out.print("Enter employee ID: ");
int id = sc.nextInt();
sc.nextLine();
System.out.print("Enter employee name: ");
String name = sc.nextLine();
System.out.print("Enter employee salary: ");
double salary = sc.nextDouble();
String sql =
"INSERT INTO employee (employee_id, employee_name, salary) VALUES (?, ?, ?)";
try {
Connection con =
DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.setString(2, name);
pstmt.setDouble(3, salary);
int rows = pstmt.executeUpdate();
System.out.println(rows + " record inserted successfully.");
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
sc.close();
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" PreparedStatementInsertExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" PreparedStatementInsertExample
Enter employee ID: 105
Enter employee name: Mahesh
Enter employee salary: 100000
1 record inserted successfully.Code language: CSS (css)Program 3: Executing a Parameterized UPDATE Query
This program updates an employee’s salary based on the employee ID. The setDouble() and setInt() methods are used to provide the salary and ID values at runtime.
import java.sql.*;
import java.util.Scanner;
public class PreparedStatementUpdateExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "password";
Scanner sc = new Scanner(System.in);
System.out.print("Enter employee ID: ");
int id = sc.nextInt();
System.out.print("Enter new salary: ");
double salary = sc.nextDouble();
String sql =
"UPDATE employee SET salary = ? WHERE employee_id = ?";
try {
Connection con =
DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setDouble(1, salary);
pstmt.setInt(2, id);
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("Employee salary updated successfully.");
} else {
System.out.println("Employee not found.");
}
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
sc.close();
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" PreparedStatementUpdateExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" PreparedStatementUpdateExample
Enter employee ID: 105
Enter new salary: 200000
Employee salary updated successfully.Code language: JavaScript (javascript)