Skip to main content

SQL Essentials for Java Backend Developers

1. Core CRUD Operations (90% of your queries)​

Create​

INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 25);

Read (Most Important)​

-- Basic select
SELECT * FROM users;
SELECT name, email FROM users WHERE id = ?;

-- Filtering (very common in APIs)
SELECT * FROM users WHERE status = 'active' AND age > 18;
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Pagination (essential for REST APIs)
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;

Update​

UPDATE users SET status = 'inactive' WHERE id = ?;

Delete​

DELETE FROM users WHERE id = ?;

2. JOINs (Critical for Backend APIs)​

-- INNER JOIN (most common)
SELECT u.name, o.total, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.id = ?;

-- LEFT JOIN (when you need all records from left table)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

3. Aggregation (For Dashboard/Analytics APIs)​

-- Common aggregates
SELECT COUNT(*) FROM users WHERE status = 'active';
SELECT AVG(total) FROM orders WHERE created_at > '2024-01-01';
SELECT SUM(total) FROM orders WHERE user_id = ?;

-- GROUP BY (for reporting endpoints)
SELECT DATE(created_at) as date, COUNT(*) as daily_orders
FROM orders
GROUP BY DATE(created_at)
ORDER BY date DESC;

4. Table Creation (For Migrations)​

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Foreign Key
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

5. Indexes (Performance Essentials)​

-- Most important indexes for Java backend
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Composite index (for complex WHERE clauses)
CREATE INDEX idx_users_status_created ON users(status, created_at);

6. Java Integration Patterns​

With JDBC PreparedStatement​

// ✅ Always use PreparedStatement
String sql = "SELECT * FROM users WHERE email = ? AND status = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, email);
stmt.setString(2, "active");
ResultSet rs = stmt.executeQuery();

Common Query Patterns in Java Backend​

-- User authentication
SELECT id, password_hash FROM users WHERE email = ?;

-- Fetch user with profile
SELECT u.*, p.bio, p.avatar_url
FROM users u
LEFT JOIN profiles p ON u.id = p.user_id
WHERE u.id = ?;

-- Paginated list for REST API
SELECT * FROM products
WHERE category = ? AND price BETWEEN ? AND ?
ORDER BY created_at DESC
LIMIT ? OFFSET ?;

-- Check if exists (before insert)
SELECT COUNT(*) FROM users WHERE email = ?;

7. Date/Time Queries (Common in Business Logic)​

-- Recent records
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL 30 DAY;

-- Date range filtering
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- Group by date
SELECT DATE(created_at) as order_date, COUNT(*)
FROM orders
GROUP BY DATE(created_at);

8. Transactions (Critical for Data Integrity)​

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK on error;

What You Can Skip Initially​

  • Advanced window functions - Learn later when you need analytics
  • Stored procedures - Most Java teams prefer business logic in Java
  • Triggers - Usually avoided in modern architectures
  • Complex CTEs - Start with basic queries first
  • Database-specific functions - Stick to standard SQL initially

Key Tips for Java Developers​

  1. Always use PreparedStatement - Never concatenate user input
  2. Index your WHERE clauses - Especially foreign keys and commonly filtered columns
  3. LIMIT your queries - Always paginate in APIs
  4. Use transactions - For any multi-table operations
  5. Test queries separately - Use MySQL Workbench/phpMyAdmin before coding
  6. Learn JPA/Hibernate - But understand the SQL it generates

Most Frequent Backend Patterns​

-- 1. Find by ID (single record)
SELECT * FROM users WHERE id = ?;

-- 2. List with filters (paginated)
SELECT * FROM products
WHERE category = ? AND status = 'active'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- 3. Count for pagination metadata
SELECT COUNT(*) FROM products WHERE category = ?;

-- 4. Update single field
UPDATE users SET last_login = NOW() WHERE id = ?;

-- 5. Soft delete
UPDATE users SET status = 'deleted' WHERE id = ?;

Focus on these patterns first - they cover 90% of what you'll need in Java backend development!