บทนำ: CRUD คืออะไร?
CRUD = 4 operation พื้นฐานของ database:
- Create (INSERT) – สร้างข้อมูลใหม่
- Read (SELECT) – อ่านข้อมูล
- Update (UPDATE) – แก้ไขข้อมูล
- Delete (DELETE) – ลบข้อมูล
text┌─────────────────────────────┐
│ Database │
├─────────────────────────────┤
│ CREATE → INSERT │
│ READ → SELECT │
│ UPDATE → UPDATE │
│ DELETE → DELETE │
└─────────────────────────────┘
เมื่อเขียน application ส่วนใหญ่เวลาคุณทำเรื่อยๆ คือ CRUD operations
Database Schema Setup
Create Tables
sql-- Create users table
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create products table
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create orders table
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
total_price DECIMAL(10, 2),
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
ตัวอย่างที่ 1: CREATE (INSERT)
Entity Classes
javapublic class User {
private int id;
private String name;
private String email;
private int age;
// Constructor (without ID - auto-generated)
public User(String name, String email, int age) {
this.name = name;
this.email = email;
this.age = age;
}
// Constructor (with ID)
public User(int id, String name, String email, int age) {
this.id = id;
this.name = name;
this.email = email;
this.age = age;
}
// Getters
public int getId() { return id; }
public String getName() { return name; }
public String getEmail() { return email; }
public int getAge() { return age; }
@Override
public String toString() {
return String.format(
"User{id=%d, name='%s', email='%s', age=%d}",
id, name, email, age
);
}
}
DAO: CREATE Operation
javaimport java.sql.*;
public class UserDAO {
/**
* Insert new user into database
* @param user User object to insert
* @return Generated user ID, or -1 if failed
*/
public int createUser(User user) {
String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
int generatedId = -1;
try (Connection conn = DatabaseConfig.getConnection();
PreparedStatement pstmt = conn.prepareStatement(
sql,
Statement.RETURN_GENERATED_KEYS // ← Get auto-generated ID
)) {
// Set parameters
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getEmail());
pstmt.setInt(3, user.getAge());
// Execute insert
int rowsInserted = pstmt.executeUpdate();
if (rowsInserted > 0) {
// Get generated ID
try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
if (generatedKeys.next()) {
generatedId = generatedKeys.getInt(1);
System.out.println("✓ User created successfully with ID: " + generatedId);
}
}
}
} catch (SQLException e) {
if (e.getMessage().contains("Duplicate entry")) {
System.err.println("❌ Email already exists: " + user.getEmail());
} else {
System.err.println("❌ Error creating user: " + e.getMessage());
}
}
return generatedId;
}
/**
* Insert multiple users (Batch insert)
*/
public void createMultipleUsers(java.util.List<User> users) {
String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
try (Connection conn = DatabaseConfig.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
// Disable auto-commit for better performance
conn.setAutoCommit(false);
for (User user : users) {
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getEmail());
pstmt.setInt(3, user.getAge());
pstmt.addBatch(); // ← Add to batch
}
// Execute all at once
int[] results = pstmt.executeBatch();
conn.commit();
System.out.println("✓ Inserted " + results.length + " users");
} catch (SQLException e) {
System.err.println("❌ Batch insert failed: " + e.getMessage());
}
}
}
// ==== Usage ====
public class CreateExample {
public static void main(String[] args) {
UserDAO userDAO = new UserDAO();
// Create single user
System.out.println("=== Create Single User ===");
User user1 = new User("John Doe", "[email protected]", 25);
int userId = userDAO.createUser(user1);
// Create multiple users
System.out.println("\n=== Create Multiple Users ===");
java.util.List<User> users = new java.util.ArrayList<>();
users.add(new User("Jane Smith", "[email protected]", 28));
users.add(new User("Bob Wilson", "[email protected]", 35));
users.add(new User("Alice Brown", "[email protected]", 30));
userDAO.createMultipleUsers(users);
}
}
// Output:
// === Create Single User ===
// ✓ User created successfully with ID: 1
//
// === Create Multiple Users ===
// ✓ Inserted 3 users
ตัวอย่างที่ 2: READ (SELECT)
DAO: READ Operations
javaimport java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserDAO {
/**
* Get all users from database
*/
public List<User> getAllUsers() {
List<User> users = new ArrayList<>();
String sql = "SELECT * FROM users";
try (Connection conn = DatabaseConfig.getConnection();
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);
}
System.out.println("✓ Retrieved " + users.size() + " users");
} catch (SQLException e) {
System.err.println("❌ Error retrieving users: " + e.getMessage());
}
return users;
}
/**
* Get user by ID
*/
public User getUserById(int userId) {
String sql = "SELECT * FROM users WHERE id = ?";
try (Connection conn = DatabaseConfig.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, userId);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
User user = new User(
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"),
rs.getInt("age")
);
System.out.println("✓ User found: " + user.getName());
return user;
}
}
System.out.println("⚠ User with ID " + userId + " not found");
} catch (SQLException e) {
System.err.println("❌ Error retrieving user: " + e.getMessage());
}
return null;
}
/**
* Get user by email
*/
public User getUserByEmail(String email) {
String sql = "SELECT * FROM users WHERE email = ?";
try (Connection conn = DatabaseConfig.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, email);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return new User(
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"),
rs.getInt("age")
);
}
}
} catch (SQLException e) {
System.err.println("❌ Error: " + e.getMessage());
}
return null;
}
/**
* Get users by age range
*/
public List<User> getUsersByAgeRange(int minAge, int maxAge) {
List<User> users = new ArrayList<>();
String sql = "SELECT * FROM users WHERE age >= ? AND age <= ? ORDER BY age";
try (Connection conn = DatabaseConfig.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, minAge);
pstmt.setInt(2, maxAge);
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
User user = new User(
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"),
rs.getInt("age")
);
users.add(user);
}
}
System.out.println("✓ Found " + users.size() +
" users between age " + minAge + "-" + maxAge);
} catch (SQLException e) {
System.err.println("❌ Error: " + e.getMessage());
}
return users;
}
/**
* Count total users
*/
public int countUsers() {
String sql = "SELECT COUNT(*) as total FROM users";
try (Connection conn = DatabaseConfig.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
if (rs.next()) {
return rs.getInt("total");
}
} catch (SQLException e) {
System.err.println("❌ Error: " + e.getMessage());
}
return 0;
}
}
// ==== Usage ====
public class ReadExample {
public static void main(String[] args) {
UserDAO userDAO = new UserDAO();
System.out.println("=== Get All Users ===");
List<User> allUsers = userDAO.getAllUsers();
allUsers.forEach(System.out::println);
System.out.println("\n=== Get User by ID ===");
User user = userDAO.getUserById(1);
if (user != null) System.out.println(user);
System.out.println("\n=== Get User by Email ===");
User userByEmail = userDAO.getUserByEmail("[email protected]");
if (userByEmail != null) System.out.println(userByEmail);
System.out.println("\n=== Get Users by Age Range ===");
List<User> filtered = userDAO.getUsersByAgeRange(25, 30);
filtered.forEach(System.out::println);
System.out.println("\n=== Count Total Users ===");
int total = userDAO.countUsers();
System.out.println("Total users: " + total);
}
}
// Output:
// === Get All Users ===
// ✓ Retrieved 4 users
// User{id=1, name='John Doe', email='[email protected]', age=25}
// User{id=2, name='Jane Smith', email='[email protected]', age=28}
// User{id=3, name='Bob Wilson', email='[email protected]', age=35}
// User{id=4, name='Alice Brown', email='[email protected]', age=30}
//
// === Get User by ID ===
// ✓ User found: John Doe
// User{id=1, name='John Doe', email='[email protected]', age=25}
//
// === Get User by Email ===
// User{id=1, name='John Doe', email='[email protected]', age=25}
//
// === Get Users by Age Range ===
// ✓ Found 2 users between age 25-30
// User{id=1, name='John Doe', email='[email protected]', age=25}
// User{id=4, name='Alice Brown', email='[email protected]', age=30}
//
// === Count Total Users ===
// Total users: 4
ตัวอย่างที่ 3: UPDATE
DAO: UPDATE Operations
javaimport java.sql.*;
public class UserDAO {
/**
* Update user information
*/
public boolean updateUser(int userId, String name, String email, int age) {
String sql = "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?";
try (Connection conn = DatabaseConfig.getConnection();
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("✓ User updated successfully");
return true;
} else {
System.out.println("⚠ User with ID " + userId + " not found");
return false;
}
} catch (SQLException e) {
System.err.println("❌ Error updating user: " + e.getMessage());
return false;
}
}
/**
* Update only user's age
*/
public boolean updateUserAge(int userId, int newAge) {
String sql = "UPDATE users SET age = ? WHERE id = ?";
try (Connection conn = DatabaseConfig.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, newAge);
pstmt.setInt(2, userId);
int rowsUpdated = pstmt.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("✓ User age updated to: " + newAge);
return true;
}
} catch (SQLException e) {
System.err.println("❌ Error: " + e.getMessage());
}
return false;
}
/**
* Update user with full object
*/
public boolean updateUserFromObject(User user) {
String sql = "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?";
try (Connection conn = DatabaseConfig.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getEmail());
pstmt.setInt(3, user.getAge());
pstmt.setInt(4, user.getId());
int rowsUpdated = pstmt.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("✓ User " + user.getName() + " updated");
return true;
}
} catch (SQLException e) {
System.err.println("❌ Error: " + e.getMessage());
}
return false;
}
/**
* Increment age for users (demonstration)
*/
public int incrementAgeByCondition(int targetAge) {
String sql = "UPDATE users SET age = age + 1 WHERE age >= ?";
try (Connection conn = DatabaseConfig.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, targetAge);
int rowsUpdated = pstmt.executeUpdate();
System.out.println("✓ Updated " + rowsUpdated +
" users aged " + targetAge + "+");
return rowsUpdated;
} catch (SQLException e) {
System.err.println("❌ Error: " + e.getMessage());
}
return 0;
}
}
// ==== Usage ====
public class UpdateExample {
public static void main(String[] args) {
UserDAO userDAO = new UserDAO();
System.out.println("=== Update Single Field ===");
userDAO.updateUserAge(1, 26);
System.out.println("\n=== Update Multiple Fields ===");
userDAO.updateUser(1, "John Updated", "[email protected]", 26);
System.out.println("\n=== Update from Object ===");
User updatedUser = new User(2, "Jane Updated", "[email protected]", 29);
userDAO.updateUserFromObject(updatedUser);
System.out.println("\n=== Increment Age ===");
userDAO.incrementAgeByCondition(25);
}
}
// Output:
// === Update Single Field ===
// ✓ User age updated to: 26
//
// === Update Multiple Fields ===
// ✓ User John Updated updated
//
// === Update from Object ===
// ✓ User Jane Updated updated
//
// === Increment Age ===
// ✓ Updated 3 users aged 25+
ตัวอย่างที่ 4: DELETE
DAO: DELETE Operations
javaimport java.sql.*;
public class UserDAO {
/**
* Delete user by ID
*/
public boolean deleteUser(int userId) {
String sql = "DELETE FROM users WHERE id = ?";
try (Connection conn = DatabaseConfig.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, userId);
int rowsDeleted = pstmt.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("✓ User deleted successfully");
return true;
} else {
System.out.println("⚠ User with ID " + userId + " not found");
return false;
}
} catch (SQLException e) {
System.err.println("❌ Error deleting user: " + e.getMessage());
return false;
}
}
/**
* Delete user by email
*/
public boolean deleteUserByEmail(String email) {
String sql = "DELETE FROM users WHERE email = ?";
try (Connection conn = DatabaseConfig.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, email);
int rowsDeleted = pstmt.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("✓ User " + email + " deleted");
return true;
}
} catch (SQLException e) {
System.err.println("❌ Error: " + e.getMessage());
}
return false;
}
/**
* Delete users older than specified age
*/
public int deleteUsersOlderThan(int age) {
String sql = "DELETE FROM users WHERE age > ?";
try (Connection conn = DatabaseConfig.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, age);
int rowsDeleted = pstmt.executeUpdate();
System.out.println("✓ Deleted " + rowsDeleted +
" users older than " + age);
return rowsDeleted;
} catch (SQLException e) {
System.err.println("❌ Error: " + e.getMessage());
}
return 0;
}
/**
* Delete all users (Use with caution!)
*/
public int deleteAllUsers() {
String sql = "DELETE FROM users";
try (Connection conn = DatabaseConfig.getConnection();
Statement stmt = conn.createStatement()) {
int rowsDeleted = stmt.executeUpdate(sql);
System.out.println("⚠ Deleted " + rowsDeleted + " users");
return rowsDeleted;
} catch (SQLException e) {
System.err.println("❌ Error: " + e.getMessage());
}
return 0;
}
}
// ==== Usage ====
public class DeleteExample {
public static void main(String[] args) {
UserDAO userDAO = new UserDAO();
System.out.println("=== Delete by ID ===");
userDAO.deleteUser(1);
System.out.println("\n=== Delete by Email ===");
userDAO.deleteUserByEmail("[email protected]");
System.out.println("\n=== Delete by Condition ===");
userDAO.deleteUsersOlderThan(30);
// WARNING: Be careful with this!
// System.out.println("\n=== Delete All ===");
// userDAO.deleteAllUsers();
}
}
// Output:
// === Delete by ID ===
// ✓ User deleted successfully
//
// === Delete by Email ===
// ✓ User [email protected] deleted
//
// === Delete by Condition ===
// ⚠ Deleted 1 users older than 30
ตัวอย่างที่ 5: CRUD Complete Application
Integrated Example
javaimport java.util.List;
import java.util.Scanner;
public class UserManagementApp {
private UserDAO userDAO = new UserDAO();
private Scanner scanner = new Scanner(System.in);
public void run() {
boolean running = true;
while (running) {
displayMenu();
System.out.print("Choose option: ");
String choice = scanner.nextLine().trim();
switch (choice) {
case "1":
createUser();
break;
case "2":
readUsers();
break;
case "3":
updateUser();
break;
case "4":
deleteUser();
break;
case "5":
running = false;
System.out.println("Goodbye!");
break;
default:
System.out.println("Invalid option");
}
System.out.println();
}
}
private void displayMenu() {
System.out.println("========== User Management ==========");
System.out.println("1. Create User");
System.out.println("2. Read Users");
System.out.println("3. Update User");
System.out.println("4. Delete User");
System.out.println("5. Exit");
System.out.println("====================================");
}
private void createUser() {
System.out.print("Enter name: ");
String name = scanner.nextLine();
System.out.print("Enter email: ");
String email = scanner.nextLine();
System.out.print("Enter age: ");
int age = Integer.parseInt(scanner.nextLine());
User newUser = new User(name, email, age);
int userId = userDAO.createUser(newUser);
if (userId > 0) {
System.out.println("✓ User created with ID: " + userId);
}
}
private void readUsers() {
System.out.println("1. View all users");
System.out.println("2. Search by ID");
System.out.println("3. Search by email");
System.out.print("Choose: ");
String choice = scanner.nextLine();
switch (choice) {
case "1":
List<User> allUsers = userDAO.getAllUsers();
allUsers.forEach(System.out::println);
break;
case "2":
System.out.print("Enter ID: ");
int id = Integer.parseInt(scanner.nextLine());
User user = userDAO.getUserById(id);
if (user != null) System.out.println(user);
break;
case "3":
System.out.print("Enter email: ");
String email = scanner.nextLine();
User userByEmail = userDAO.getUserByEmail(email);
if (userByEmail != null) System.out.println(userByEmail);
break;
}
}
private void updateUser() {
System.out.print("Enter user ID to update: ");
int id = Integer.parseInt(scanner.nextLine());
System.out.print("Enter new name: ");
String name = scanner.nextLine();
System.out.print("Enter new email: ");
String email = scanner.nextLine();
System.out.print("Enter new age: ");
int age = Integer.parseInt(scanner.nextLine());
boolean updated = userDAO.updateUser(id, name, email, age);
if (!updated) {
System.out.println("❌ User not found");
}
}
private void deleteUser() {
System.out.print("Enter user ID to delete: ");
int id = Integer.parseInt(scanner.nextLine());
boolean deleted = userDAO.deleteUser(id);
if (!deleted) {
System.out.println("❌ User not found");
}
}
public static void main(String[] args) {
UserManagementApp app = new UserManagementApp();
app.run();
}
}
SQL CRUD Cheat Sheet
sql-- CREATE (Insert)
INSERT INTO users (name, email, age) VALUES ('John', '[email protected]', 25);
-- READ (Select)
SELECT * FROM users;
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE age > 25 ORDER BY age;
SELECT COUNT(*) FROM users;
-- UPDATE (Modify)
UPDATE users SET age = 26 WHERE id = 1;
UPDATE users SET age = age + 1 WHERE age >= 25;
-- DELETE (Remove)
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE age > 30;
Best Practices: CRUD Checklist
text✓ DO:
☑ Use PreparedStatement for all queries
☑ Validate input before database operation
☑ Handle SQLException properly
☑ Use try-with-resources for auto-close
☑ Check rowsAffected for success
☑ Implement transactions for related operations
☑ Log database operations
☑ Use batch operations for multiple inserts
✗ DON'T:
☐ Use string concatenation for SQL (SQL Injection!)
☐ Ignore SQLException
☐ Leave connections open
☐ Mix business logic with SQL
☐ Assume operations always succeed
☐ Delete without WHERE clause (accidentally deletes all!)
☐ Update without WHERE clause
☐ Hardcode database credentials
สรุป
SQL CRUD Operations ไม่ใช่แค่ “run SQL queries” แต่เป็นการออกแบบ systematic data management:
ทำไมต้อง CRUD:
- Create → Persist new data
- Read → Retrieve and display data
- Update → Maintain data accuracy
- Delete → Clean up unnecessary data
Key Principles:
- PreparedStatement → Prevent SQL Injection
- Error Handling → Gracefully handle failures
- Resource Management → Close connections properly
- Validation → Check before operating
ในการออกแบบ CRUD ที่ดี:
- มี defensive programming – ตรวจสอบทุกอย่าง
- มี clear separation – DAO layer ต่าง entity
- มี proper exception handling – ไม่ให้ crash
- มี transaction support – สำหรับ related operations
CRUD operations คือ “DNA” ของเกือบทุก application – มันคือพื้นฐาน ที่ทุก feature ขึ้นอยู่กับมัน การ master CRUD ให้ดี ทำให้คุณสามารถสร้าง robust database-driven applications ได้