Lecture 10: Prepared Statement Solution
What is PreparedStatement?
PreparedStatement is a precompiled SQL statement that:
● Prevents SQL injection attacks
● Improves performance through query plan reuse
● Provides cleaner, more readable code
● Handles data type conversions automatically
Complete PreparedStatement Example:
import [Link].*;
public class PreparedStatementDemo {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/demo";
String username = "postgres";
String password = "0000";
Connection conn = null;
PreparedStatement pstmt = null;
try {
[Link]("[Link]");
conn = [Link](url, username, password);
// SQL with placeholders (?)
String sql = "INSERT INTO student VALUES (?, ?, ?)";
// Create PreparedStatement
pstmt = [Link](sql);
// Set parameters (1-indexed)
[Link](1, 10); // sid
[Link](2, "Ananya"); // sname
[Link](3, 90); // marks
// Execute the statement
int rowsAffected = [Link]();
[Link]("Rows inserted: " + rowsAffected);
} catch (Exception e) {
[Link]("Error: " + [Link]());
[Link]();
} finally {
try {
if (pstmt != null) [Link]();
if (conn != null) [Link]();
} catch (SQLException e) {
[Link]("Error closing resources: " + [Link]());
}
}
}
}
Understanding Placeholders (?):
● ? represents a parameter placeholder
● Parameters are 1-indexed (first ? is index 1)
● Use appropriate setter methods based on data type:
○ setInt(index, value) for integers
○ setString(index, value) for strings
○ setDouble(index, value) for doubles
○ setDate(index, value) for dates
○ setBoolean(index, value) for booleans
PreparedStatement vs Statement Comparison:
Aspect Statement PreparedStatement
SQL Injection Vulnerable Safe
Performance Slower (compiled each Faster (precompiled)
time)
Code Readability Complex concatenation Clean and simple
Parameter Manual string manipulation Automatic type handling
Handling
Reusability Limited High (can reuse with different parameters)
Complete CRUD with PreparedStatement:
public class PreparedStatementCRUD {
private static final String URL = "jdbc:postgresql://localhost:5432/demo";
private static final String USERNAME = "postgres";
private static final String PASSWORD = "0000";
// CREATE
public static void insertStudent(int sid, String sname, int marks) {
String sql = "INSERT INTO student VALUES (?, ?, ?)";
try (Connection conn = [Link](URL, USERNAME,
PASSWORD);
PreparedStatement pstmt = [Link](sql)) {
[Link](1, sid);
[Link](2, sname);
[Link](3, marks);
int rows = [Link]();
[Link]("Inserted " + rows + " record(s)");
} catch (SQLException e) {
[Link]("Insert error: " + [Link]());
}
}
// READ
public static void getStudent(int sid) {
String sql = "SELECT * FROM student WHERE sid = ?";
try (Connection conn = [Link](URL, USERNAME,
PASSWORD);
PreparedStatement pstmt = [Link](sql)) {
[Link](1, sid);
ResultSet rs = [Link]();
if ([Link]()) {
[Link]("ID: " + [Link]("sid") +
", Name: " + [Link]("sname") +
", Marks: " + [Link]("marks"));
} else {
[Link]("Student not found");
}
} catch (SQLException e) {
[Link]("Select error: " + [Link]());
}
}
// UPDATE
public static void updateStudent(int sid, String newName, int newMarks) {
String sql = "UPDATE student SET sname = ?, marks = ? WHERE sid = ?";
try (Connection conn = [Link](URL, USERNAME,
PASSWORD);
PreparedStatement pstmt = [Link](sql)) {
[Link](1, newName);
[Link](2, newMarks);
[Link](3, sid);
int rows = [Link]();
[Link]("Updated " + rows + " record(s)");
} catch (SQLException e) {
[Link]("Update error: " + [Link]());
}
}
// DELETE
public static void deleteStudent(int sid) {
String sql = "DELETE FROM student WHERE sid = ?";
try (Connection conn = [Link](URL, USERNAME,
PASSWORD);
PreparedStatement pstmt = [Link](sql)) {
[Link](1, sid);
int rows = [Link]();
[Link]("Deleted " + rows + " record(s)");
} catch (SQLException e) {
[Link]("Delete error: " + [Link]());
}
}
}