Multi-Table Query Strategies

A Cartesian product arises when every row from one table is paired with every row from another—often unintentional and inefficient. To retrieve meaningful results, explicit join conditions must constrain the result set.

SELECT e.id, e.name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

Join Types

• Inner Join: Returns only rows where matching entries exist in both tables.
• Left Outer Join: Includes all rows from the left table and matched rows from the right; unmatched right-side columns appear as NULL.
• Right Outer Join: Mirrors left outer join but prioritizes the right table.

Subqueries

Subqueries embed SELECT statements within clauses like WHERE, FROM, or SELECT. They fall into four categories:

• Scalar subquery: Returns exactly one value (e.g., SELECT name FROM users WHERE id = (SELECT MAX(id) FROM archived_users)).
• Column subquery: Returns a single column of values (e.g., used with IN or NOT IN).
• Row subquery: Returns a single row with multiple columns (e.g., compared using = or <> with a row constructor).
• Table subquery: Produces a full result set used as a derived table in the FROM clause.

Transaction Management

Transactions ensure data consistency across related operations. For example, deleting a department should atomically remove its associated employees—or fail entirely if any step encounters an error.

MySQL defaults to autocommit mode. To manage transactions explicitly:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT; -- or ROLLBACK if an error occurs

Core ACID properties guarantee reliability:

• Atomicity: All operations succeed or none do.
• Consistency: Data remains valid before and after execution.
• Isolation: Concurrent transactions don’t interfere.
• Durability: Committed changes persist despite failures.

Indexign for Performence Optimization

Indexes act as lookup structures that accelerate WHERE, JOIN, ORDER BY, and GROUP BY operations—reducing disk I/O and CPU usage during sorting.

However, indexes impose overhead on write-heavy workloads: each INSERT, UPDATE, or DELETE must also update associated indexes.

Common index commands:

-- Create a composite index
CREATE INDEX idx_emp_dept_age ON employees(dept_id, age);

-- View existing indexes
SHOW INDEX FROM employees;

-- Remove an index
DROP INDEX idx_emp_dept_age ON employees;

Integrating MyBatis in Java Applications

MyBatis bridges object-oriented Java code with relational database logic by mapping SQL queries to method calls without boilerplate JDBC code.

Basic Setup

1. Define a domain class (e.g., Employee) annotated with Lombok for concise POJOs:

import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
    private Long id;
    private String fullName;
    private Integer departmentId;
    private Integer salary;
}

2. Conifgure database connectivity in application.yml:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/company_db?serverTimezone=UTC
    username: dev_user
    password: secure_pass
    driver-class-name: com.mysql.cj.jdbc.Driver

3. Declare a mapper interface with annotated SQL:

@Mapper
public interface EmployeeMapper {
    @Select("SELECT * FROM employees WHERE departmentId = #{deptId}")
    List<Employee> findByDepartment(Long deptId);

    @Insert("INSERT INTO employees(fullName, departmentId, salary) VALUES(#{fullName}, #{departmentId}, #{salary})")
    void insert(Employee employee);
}

Connection Pooling

Modern applications use connection pools like HikariCP (default in Spring Boot) to reuse connections efficiently, minimizing latency and preventing resource exhaustion.

IDE Support

Enable SQL dialect resolution in IntelliJ IDEA by attaching a database data source under Database Tools > Attach Data Source, enabling syntax highlighting and auto-completion for embedded SQL.