JDBC Prepared Statements
Secure and efficient SQL execution with parameters
🛡️ Prepared Statements
PreparedStatement is a precompiled SQL statement that accepts parameters. It provides better performance, security against SQL injection, and cleaner code compared to regular Statement objects.
// Prepared statement example
PreparedStatement pstmt = conn.prepareStatement(
"SELECT * FROM users WHERE name = ?"
);
pstmt.setString(1, "John");
PreparedStatement Benefits
SQL Injection Prevention
Protects against malicious SQL
pstmt.setString(1, userInput); // SafeBetter Performance
Precompiled and cached queries
// Compiled once, executed many timesParameter Binding
Type-safe parameter setting
pstmt.setInt(1, 123);
pstmt.setString(2, "text");Cleaner Code
No string concatenation needed
// No more "SELECT * FROM users WHERE id=" + id🔹 Basic Prepared Statement
Creating and using a simple prepared statement:
import java.sql.*;
public class BasicPreparedStatement {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/testdb";
String username = "root";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, username, password)) {
// Prepare SQL with parameter placeholder
String sql = "SELECT id, name, email FROM users WHERE name = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
// Set parameter value
pstmt.setString(1, "John Doe");
// Execute query
ResultSet rs = pstmt.executeQuery();
System.out.println("Search Results:");
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id"));
System.out.println("Name: " + rs.getString("name"));
System.out.println("Email: " + rs.getString("email"));
System.out.println("---");
}
// Reuse same prepared statement with different parameter
pstmt.setString(1, "Jane Smith");
ResultSet rs2 = pstmt.executeQuery();
System.out.println("Second Search:");
while (rs2.next()) {
System.out.println("Found: " + rs2.getString("name"));
}
}
} catch (SQLException e) {
System.out.println("Error: " + e.getMessage());
}
}
}Output:
🔹 INSERT with Prepared Statement
Safely inserting data using prepared statements:
import java.sql.*;
public class PreparedInsert {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/testdb";
String username = "root";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, username, password)) {
// Prepare INSERT statement
String insertSQL = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS)) {
// Insert first user
pstmt.setString(1, "Mike Wilson");
pstmt.setString(2, "[email protected]");
pstmt.setInt(3, 28);
int rowsAffected = pstmt.executeUpdate();
System.out.println("Rows inserted: " + rowsAffected);
// Get generated key
ResultSet generatedKeys = pstmt.getGeneratedKeys();
if (generatedKeys.next()) {
System.out.println("Generated ID: " + generatedKeys.getInt(1));
}
// Insert second user (reusing same prepared statement)
pstmt.setString(1, "Sarah Davis");
pstmt.setString(2, "[email protected]");
pstmt.setInt(3, 25);
pstmt.executeUpdate();
System.out.println("Second user inserted successfully!");
// Insert with null handling
pstmt.setString(1, "Tom Brown");
pstmt.setString(2, "[email protected]");
pstmt.setNull(3, Types.INTEGER); // Handle null values
pstmt.executeUpdate();
System.out.println("User with null age inserted!");
}
} catch (SQLException e) {
System.out.println("Error: " + e.getMessage());
}
}
}Output:
Rows inserted: 1
Generated ID: 4
Second user inserted successfully!
User with null age inserted!
🔹 UPDATE and DELETE with Parameters
Modifying and deleting records safely:
import java.sql.*;
public class PreparedUpdateDelete {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/testdb";
String username = "root";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, username, password)) {
// UPDATE with prepared statement
String updateSQL = "UPDATE users SET email = ?, age = ? WHERE name = ?";
try (PreparedStatement updateStmt = conn.prepareStatement(updateSQL)) {
updateStmt.setString(1, "[email protected]");
updateStmt.setInt(2, 29);
updateStmt.setString(3, "Mike Wilson");
int updatedRows = updateStmt.executeUpdate();
System.out.println("Updated rows: " + updatedRows);
}
// DELETE with prepared statement
String deleteSQL = "DELETE FROM users WHERE age < ? OR email LIKE ?";
try (PreparedStatement deleteStmt = conn.prepareStatement(deleteSQL)) {
deleteStmt.setInt(1, 20);
deleteStmt.setString(2, "%temp%");
int deletedRows = deleteStmt.executeUpdate();
System.out.println("Deleted rows: " + deletedRows);
}
// Conditional DELETE
String conditionalDelete = "DELETE FROM users WHERE name = ? AND age > ?";
try (PreparedStatement condStmt = conn.prepareStatement(conditionalDelete)) {
condStmt.setString(1, "Tom Brown");
condStmt.setInt(2, 18);
int result = condStmt.executeUpdate();
if (result > 0) {
System.out.println("User Tom Brown deleted successfully!");
} else {
System.out.println("No matching user found for deletion.");
}
}
// Verify remaining records
String selectSQL = "SELECT name, email FROM users";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(selectSQL)) {
System.out.println("\nRemaining users:");
while (rs.next()) {
System.out.println("- " + rs.getString("name") + " (" + rs.getString("email") + ")");
}
}
} catch (SQLException e) {
System.out.println("Error: " + e.getMessage());
}
}
}Output:
Updated rows: 1
Deleted rows: 0
User Tom Brown deleted successfully!
Remaining users:
- John Doe ([email protected])
- Jane Smith ([email protected])
- Mike Wilson ([email protected])
- Sarah Davis ([email protected])
🔹 Parameter Types and Methods
Different parameter types and their setter methods:
import java.sql.*;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
public class ParameterTypes {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/testdb";
String username = "root";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, username, password)) {
// Create table with various data types
String createTable = "CREATE TABLE IF NOT EXISTS products (" +
"id INT PRIMARY KEY AUTO_INCREMENT, " +
"name VARCHAR(100), " +
"price DECIMAL(10,2), " +
"in_stock BOOLEAN, " +
"created_date DATE, " +
"updated_at TIMESTAMP)";
Statement createStmt = conn.createStatement();
createStmt.executeUpdate(createTable);
// Insert with different parameter types
String insertSQL = "INSERT INTO products (name, price, in_stock, created_date, updated_at) " +
"VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
// Set different parameter types
pstmt.setString(1, "Laptop Computer"); // String
pstmt.setBigDecimal(2, new BigDecimal("999.99")); // BigDecimal
pstmt.setBoolean(3, true); // Boolean
pstmt.setDate(4, Date.valueOf(LocalDate.now())); // Date
pstmt.setTimestamp(5, Timestamp.valueOf(LocalDateTime.now())); // Timestamp
pstmt.executeUpdate();
System.out.println("Product inserted with various data types!");
// Example with null values
pstmt.setString(1, "Out of Stock Item");
pstmt.setBigDecimal(2, new BigDecimal("0.00"));
pstmt.setBoolean(3, false);
pstmt.setNull(4, Types.DATE); // Null date
pstmt.setNull(5, Types.TIMESTAMP); // Null timestamp
pstmt.executeUpdate();
System.out.println("Product with null values inserted!");
}
// Query with different parameter types
String querySQL = "SELECT * FROM products WHERE price > ? AND in_stock = ?";
try (PreparedStatement queryStmt = conn.prepareStatement(querySQL)) {
queryStmt.setBigDecimal(1, new BigDecimal("100.00"));
queryStmt.setBoolean(2, true);
ResultSet rs = queryStmt.executeQuery();
System.out.println("\nProducts over $100 in stock:");
while (rs.next()) {
System.out.println("- " + rs.getString("name") +
" ($" + rs.getBigDecimal("price") + ")");
}
}
} catch (SQLException e) {
System.out.println("Error: " + e.getMessage());
}
}
}Output:
Product inserted with various data types!
Product with null values inserted!
Products over $100 in stock:
- Laptop Computer ($999.99)
🔹 SQL Injection Prevention
How prepared statements prevent SQL injection attacks:
❌ Vulnerable Code (Regular Statement):
// NEVER DO THIS - Vulnerable to SQL injection
String userInput = "'; DROP TABLE users; --";
String sql = "SELECT * FROM users WHERE name = '" + userInput + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql); // DANGEROUS!✅ Safe Code (Prepared Statement):
// SAFE - Protected against SQL injection
String userInput = "'; DROP TABLE users; --";
String sql = "SELECT * FROM users WHERE name = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userInput); // Treated as literal string, not SQL
ResultSet rs = pstmt.executeQuery(); // SAFE!import java.sql.*;
public class SQLInjectionDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/testdb";
String username = "root";
String password = "password";
// Simulating malicious input
String maliciousInput = "admin' OR '1'='1";
try (Connection conn = DriverManager.getConnection(url, username, password)) {
// Safe approach with PreparedStatement
String safeSQL = "SELECT * FROM users WHERE name = ?";
try (PreparedStatement pstmt = conn.prepareStatement(safeSQL)) {
pstmt.setString(1, maliciousInput);
ResultSet rs = pstmt.executeQuery();
System.out.println("Safe query results:");
int count = 0;
while (rs.next()) {
System.out.println("Found user: " + rs.getString("name"));
count++;
}
if (count == 0) {
System.out.println("No users found - SQL injection prevented!");
}
}
} catch (SQLException e) {
System.out.println("Error: " + e.getMessage());
}
}
}Output:
Safe query results:
No users found - SQL injection prevented!