JDBC · Lesson 5
CRUD Operations
Understanding CRUD Operations
CRUD stands for Create (INSERT), Read (SELECT), Update (UPDATE), and Delete (DELETE) — the four fundamental database operations. Most applications spend 90% of their time performing CRUD operations. Mastering CRUD with JDBC is essential for building data-driven applications. Each operation uses SQL statements executed through JDBC in slightly different ways.
CREATE: Inserting Data with INSERT
The INSERT statement adds new records to the database. Use a PreparedStatement with parameters for data values — this prevents SQL injection and makes code more readable. For single inserts, execute immediately; for bulk inserts, use batching for better performance.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
public class CreateOperations {
// Single insert
public static void insertUser(Connection conn, String name, String email, int age) {
String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.setInt(3, age);
int rowsInserted = pstmt.executeUpdate();
System.out.println(rowsInserted + " row(s) inserted");
} catch (SQLException e) {
System.err.println("Insert failed: " + e.getMessage());
}
}
// Insert and get generated key (auto-increment ID)
public static int insertUserGetId(Connection conn, String name, String email) {
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.executeUpdate();
// Get the generated ID
ResultSet keys = pstmt.getGeneratedKeys();
if (keys.next()) {
int userId = keys.getInt(1);
System.out.println("User created with ID: " + userId);
return userId;
}
} catch (SQLException e) {
System.err.println("Insert failed: " + e.getMessage());
}
return -1;
}
// Batch insert for multiple records
public static void batchInsertUsers(Connection conn, java.util.List<String[]> users) {
String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
for (String[] user : users) {
pstmt.setString(1, user[0]); // name
pstmt.setString(2, user[1]); // email
pstmt.setInt(3, Integer.parseInt(user[2])); // age
pstmt.addBatch(); // Add to batch
}
int[] results = pstmt.executeBatch();
System.out.println("Inserted " + results.length + " users");
} catch (SQLException e) {
System.err.println("Batch insert failed: " + e.getMessage());
}
}
}
READ: Querying Data with SELECT
The SELECT statement retrieves data from the database. Use PreparedStatement with parameters for filtering. Process the ResultSet to extract and use the data. For large result sets, consider pagination (LIMIT/OFFSET) to avoid loading everything into memory.
import java.sql.*;
import java.util.*;
public class ReadOperations {
// Simple SELECT
public static void selectAllUsers(Connection conn) {
String sql = "SELECT id, name, email, age FROM users";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
int age = rs.getInt("age");
System.out.println(id + " - " + name + " (" + email + "), Age: " + age);
}
} catch (SQLException e) {
System.err.println("Query failed: " + e.getMessage());
}
}
// Parameterized query with filtering
public static void selectUsersByAge(Connection conn, int minAge) {
String sql = "SELECT id, name, email, age FROM users WHERE age >= ? ORDER BY age DESC";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, minAge);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("name") + " - " + rs.getInt("age"));
}
} catch (SQLException e) {
System.err.println("Query failed: " + e.getMessage());
}
}
// SELECT with pagination
public static void selectUsersPaginated(Connection conn, int page, int pageSize) {
String sql = "SELECT id, name, email FROM users LIMIT ? OFFSET ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, pageSize);
pstmt.setInt(2, (page - 1) * pageSize); // Offset = (page-1) * pageSize
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("name") + " - " + rs.getString("email"));
}
} catch (SQLException e) {
System.err.println("Pagination query failed: " + e.getMessage());
}
}
// Return results as Java objects
public static List<User> selectUsersAsObjects(Connection conn) {
List<User> users = new ArrayList<>();
String sql = "SELECT id, name, email, age FROM users";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
User user = new User(
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"),
rs.getInt("age")
);
users.add(user);
}
} catch (SQLException e) {
System.err.println("Query failed: " + e.getMessage());
}
return users;
}
}
class User {
int id;
String name;
String email;
int age;
User(int id, String name, String email, int age) {
this.id = id;
this.name = name;
this.email = email;
this.age = age;
}
}
UPDATE: Modifying Existing Data
The UPDATE statement modifies existing records. Always include a WHERE clause to specify which records to update — without it, you'll update all records! Use PreparedStatement parameters for safety and clarity.
public class UpdateOperations {
// Simple update
public static void updateUserEmail(Connection conn, int userId, String newEmail) {
String sql = "UPDATE users SET email = ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, newEmail);
pstmt.setInt(2, userId);
int rowsUpdated = pstmt.executeUpdate();
System.out.println(rowsUpdated + " row(s) updated");
} catch (SQLException e) {
System.err.println("Update failed: " + e.getMessage());
}
}
// Update multiple fields
public static void updateUserProfile(Connection conn, int userId, String name, String email, int age) {
String sql = "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.setInt(3, age);
pstmt.setInt(4, userId);
int rowsUpdated = pstmt.executeUpdate();
if (rowsUpdated == 0) {
System.out.println("No user found with ID: " + userId);
} else {
System.out.println("Profile updated successfully");
}
} catch (SQLException e) {
System.err.println("Update failed: " + e.getMessage());
}
}
// Conditional update based on current value
public static void incrementUserAge(Connection conn, int userId) {
String sql = "UPDATE users SET age = age + 1 WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, userId);
pstmt.executeUpdate();
} catch (SQLException e) {
System.err.println("Update failed: " + e.getMessage());
}
}
}
DELETE: Removing Data
The DELETE statement removes records from the database. Like UPDATE, always include a WHERE clause — without it, you'll delete all records! Be extremely careful with DELETE operations; consider soft deletes (marking as deleted) instead of hard deletes for audit trails.
public class DeleteOperations {
// Simple delete
public static void deleteUser(Connection conn, int userId) {
String sql = "DELETE FROM users WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, userId);
int rowsDeleted = pstmt.executeUpdate();
if (rowsDeleted == 0) {
System.out.println("No user found with ID: " + userId);
} else {
System.out.println(rowsDeleted + " row(s) deleted");
}
} catch (SQLException e) {
System.err.println("Delete failed: " + e.getMessage());
}
}
// Soft delete (mark as deleted instead of removing)
public static void softDeleteUser(Connection conn, int userId) {
String sql = "UPDATE users SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, userId);
int rowsUpdated = pstmt.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("User marked as deleted");
}
} catch (SQLException e) {
System.err.println("Soft delete failed: " + e.getMessage());
}
}
// Delete with condition
public static void deleteInactiveUsers(Connection conn, int daysSinceLastLogin) {
String sql = "DELETE FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL ? DAY)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, daysSinceLastLogin);
int rowsDeleted = pstmt.executeUpdate();
System.out.println("Deleted " + rowsDeleted + " inactive users");
} catch (SQLException e) {
System.err.println("Delete failed: " + e.getMessage());
}
}
}
| Operation | SQL | Purpose | Example |
|---|---|---|---|
| CREATE | INSERT | Add new records | INSERT INTO users (name, email) VALUES (?, ?) |
| READ | SELECT | Retrieve records | SELECT * FROM users WHERE age > ? |
| UPDATE | UPDATE | Modify existing records | UPDATE users SET email = ? WHERE id = ? |
| DELETE | DELETE | Remove records | DELETE FROM users WHERE id = ? |
Common Mistake: Forgetting the WHERE clause in UPDATE or DELETE. Without it, the operation affects ALL records. Always double-check your WHERE condition before executing. For critical operations, consider a "soft delete" (mark as deleted) instead of permanent deletion, allowing recovery and maintaining audit trails.
Best Practices for CRUD Operations
- Always use PreparedStatement for parameterized queries
- Include WHERE clauses in UPDATE and DELETE; never forget!
- Use batch operations for multiple inserts/updates (better performance)
- Get generated keys for auto-increment IDs after inserts
- Validate data before inserting to prevent invalid states
- Use pagination for large SELECT result sets (LIMIT/OFFSET)
- Consider soft deletes (mark as deleted) instead of hard deletes
- Log or audit all CRUD operations for compliance and debugging
Real-World Example: User Management System
A user management application handles CRUD operations on a users table. On registration, it INSERTS a new user and retrieves the generated ID. When viewing a profile, it SELECTs user data by ID. On profile updates, it UPDATES email, name, and age with proper validation. When a user requests deletion, it soft-deletes by setting a deleted_at timestamp. The system uses PreparedStatements for all queries, paginated SELECT for listing users, and batch inserts for bulk imports. All operations are logged for audit trails.
Quiz Check