ORMs (Object-Relational Mapping)

What is ORM?

Object-Relational Mapping (ORM) is a programming technique that converts data between incompatible type systems: object-oriented programming languages and relational databases. ORMs allow developers to work with database data using their language's native objects instead of writing SQL.

The Impedance Mismatch Problem

// Object-Oriented World vs Relational World

┌────────────────────────────────────────────────────────────────────┐
│         OBJECTS (Java)              │        TABLES (SQL)          │
├────────────────────────────────────────────────────────────────────┤
│                                     │                              │
│  class Employee {                   │  CREATE TABLE employees (    │
│      Long id;                       │      id BIGINT PRIMARY KEY,  │
│      String name;                   │      name VARCHAR(100),      │
│      Department dept;  ← Object     │      dept_id BIGINT  ← FK    │
│      List<Project> projects; ←Many │  );                          │
│  }                                  │                              │
│                                     │  CREATE TABLE emp_projects ( │
│  Inheritance:                       │      emp_id BIGINT,          │
│  class Manager extends Employee     │      proj_id BIGINT          │
│                                     │  ); ← Join table             │
│                                     │                              │
│  Encapsulation:                     │  No encapsulation -          │
│  private fields, methods            │  just columns                │
│                                     │                              │
│  Identity:                          │  Identity:                   │
│  emp1 == emp2 (reference)           │  id = id (value)             │
│                                     │                              │
└────────────────────────────────────────────────────────────────────┘

// Key mismatches ORMs solve:
// 1. Granularity - Objects can have complex structure, tables are flat
// 2. Inheritance - Objects have it, tables don't
// 3. Identity - Object identity vs primary key
// 4. Associations - References vs foreign keys
// 5. Navigation - Objects traverse, SQL joins

What ORM Does

// WITHOUT ORM (Manual mapping)
public Employee findById(long id) {
    String sql = "SELECT * FROM employees WHERE id = ?";

    try (Connection conn = getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {

        stmt.setLong(1, id);
        ResultSet rs = stmt.executeQuery();

        if (rs.next()) {
            Employee emp = new Employee();
            emp.setId(rs.getLong("id"));
            emp.setName(rs.getString("name"));
            emp.setEmail(rs.getString("email"));
            emp.setSalary(rs.getBigDecimal("salary"));
            // Load department? More queries...
            // Load projects? Even more queries...
            return emp;
        }
    }
    return null;
}

// WITH ORM (Automatic mapping)
public Employee findById(long id) {
    return entityManager.find(Employee.class, id);  // That's it!
}

ORM Benefits:

Productivity: Write less boilerplate code
Maintainability: Schema changes in one place
Type Safety: Compile-time checking
Database Independence: Switch databases easier
Caching: Built-in caching mechanisms
Lazy Loading: Load data on demand

ORM Frameworks in Java

Framework	Type	Description	Best For
Hibernate	Full ORM	Most popular, JPA provider, feature-rich	Most enterprise applications
EclipseLink	Full ORM	Reference JPA implementation	JPA-strict applications
MyBatis	SQL Mapper	SQL-centric, more control	Complex SQL, DBA teams
jOOQ	SQL Builder	Type-safe SQL, code generation	SQL-heavy applications
Spring Data JPA	Repository	Abstraction over JPA	Spring Boot applications
JDBI	SQL Abstraction	Lightweight, SQL-focused	Simple JDBC wrapper

Framework Categories

// 1. FULL ORM (Hibernate, EclipseLink)
// - Complete object-relational mapping
// - Automatic SQL generation
// - Caching, lazy loading, dirty checking
// - You work with objects, framework handles SQL

@Entity
public class Employee {
    @Id private Long id;
    private String name;
    @ManyToOne private Department dept;
}

Employee emp = em.find(Employee.class, 1L);  // No SQL written!

// 2. SQL MAPPER (MyBatis)
// - You write SQL, framework maps results
// - More control over queries
// - No automatic relationship handling

// mapper.xml
<select id="findById" resultType="Employee">
    SELECT * FROM employees WHERE id = #{id}
</select>

Employee emp = mapper.findById(1L);  // Your SQL, mapped result

// 3. SQL BUILDER (jOOQ)
// - Type-safe SQL construction
// - Compile-time SQL validation
// - Generated code from schema

Result<Record> result = create
    .select(EMPLOYEE.NAME, DEPARTMENT.NAME)
    .from(EMPLOYEE)
    .join(DEPARTMENT).on(EMPLOYEE.DEPT_ID.eq(DEPARTMENT.ID))
    .where(EMPLOYEE.SALARY.gt(50000))
    .fetch();

Hibernate: The Most Popular ORM

Key Features

// Hibernate provides:

// 1. TRANSPARENT PERSISTENCE
// - Persist objects without implementing interfaces
// - POJOs with annotations

@Entity
public class Product {
    @Id
    @GeneratedValue
    private Long id;

    private String name;
    private BigDecimal price;
}

// 2. AUTOMATIC DIRTY CHECKING
// - Hibernate tracks changes to managed entities
// - Updates generated automatically at flush time

Product product = em.find(Product.class, 1L);
product.setPrice(new BigDecimal("99.99"));  // Changed!
// No explicit save needed - Hibernate sees the change
tx.commit();  // UPDATE product SET price = 99.99 WHERE id = 1

// 3. LAZY LOADING
// - Load relationships on demand

@OneToMany(fetch = FetchType.LAZY)
private List<OrderItem> items;

Order order = em.find(Order.class, 1L);  // Items NOT loaded
order.getItems().size();  // NOW items are loaded

// 4. CACHING
// - First level cache (session-scoped)
// - Second level cache (shared)

Product p1 = em.find(Product.class, 1L);  // DB query
Product p2 = em.find(Product.class, 1L);  // From L1 cache!
System.out.println(p1 == p2);  // true - same instance

// 5. HQL (Hibernate Query Language)
// - Object-oriented SQL

List<Product> products = em.createQuery(
    "SELECT p FROM Product p WHERE p.price > :minPrice",
    Product.class
)
.setParameter("minPrice", new BigDecimal("50"))
.getResultList();

Hibernate Architecture

// Hibernate Core Components

┌─────────────────────────────────────────────────────────────────────┐
│                        APPLICATION                                   │
└────────────────────────────┬────────────────────────────────────────┘
                             │
                             ▼
┌─────────────────────────────────────────────────────────────────────┐
│                   SessionFactory / EntityManagerFactory              │
│   - One per database (expensive to create)                          │
│   - Thread-safe                                                      │
│   - Creates Sessions                                                 │
└────────────────────────────┬────────────────────────────────────────┘
                             │ creates
                             ▼
┌─────────────────────────────────────────────────────────────────────┐
│                   Session / EntityManager                            │
│   - One per request/transaction                                      │
│   - NOT thread-safe                                                  │
│   - Contains persistence context (L1 cache)                         │
│   - Manages entity lifecycle                                         │
└────────────────────────────┬────────────────────────────────────────┘
                             │ uses
                             ▼
┌─────────────────────────────────────────────────────────────────────┐
│                       JDBC / Connection Pool                         │
└─────────────────────────────────────────────────────────────────────┘

MyBatis: SQL-Centric Approach

When to Choose MyBatis

// MyBatis is better when:
// - You have complex, hand-tuned SQL
// - DBAs want control over SQL
// - Working with stored procedures
// - Legacy database with unusual schema
// - Need database-specific features

MyBatis Configuration

<!-- Mapper XML -->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.mapper.EmployeeMapper">

    <!-- Result mapping -->
    <resultMap id="employeeResultMap" type="Employee">
        <id property="id" column="emp_id"/>
        <result property="name" column="emp_name"/>
        <result property="email" column="emp_email"/>
        <association property="department" javaType="Department">
            <id property="id" column="dept_id"/>
            <result property="name" column="dept_name"/>
        </association>
    </resultMap>

    <!-- Queries -->
    <select id="findById" resultMap="employeeResultMap">
        SELECT
            e.id as emp_id,
            e.name as emp_name,
            e.email as emp_email,
            d.id as dept_id,
            d.name as dept_name
        FROM employees e
        LEFT JOIN departments d ON e.dept_id = d.id
        WHERE e.id = #{id}
    </select>

    <select id="findByDepartment" resultMap="employeeResultMap">
        SELECT * FROM employees WHERE dept_id = #{deptId}
    </select>

    <!-- Dynamic SQL -->
    <select id="search" resultMap="employeeResultMap">
        SELECT * FROM employees
        <where>
            <if test="name != null">
                AND name LIKE #{name}
            </if>
            <if test="email != null">
                AND email = #{email}
            </if>
            <if test="minSalary != null">
                AND salary >= #{minSalary}
            </if>
        </where>
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO employees (name, email, dept_id)
        VALUES (#{name}, #{email}, #{department.id})
    </insert>

</mapper>

MyBatis Mapper Interface

public interface EmployeeMapper {

    Employee findById(long id);

    List<Employee> findByDepartment(long deptId);

    List<Employee> search(EmployeeSearchCriteria criteria);

    void insert(Employee employee);

    void update(Employee employee);

    void delete(long id);

    // With annotations (alternative to XML)
    @Select("SELECT * FROM employees WHERE id = #{id}")
    Employee findByIdSimple(long id);

    @Insert("INSERT INTO employees (name, email) VALUES (#{name}, #{email})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insertSimple(Employee employee);
}

// Usage
@Service
public class EmployeeService {

    private final EmployeeMapper mapper;

    public Employee getEmployee(long id) {
        return mapper.findById(id);  // Your SQL, automatic mapping
    }
}

jOOQ: Type-Safe SQL

Why jOOQ?

// jOOQ generates Java code from your database schema
// Provides compile-time type safety for SQL

// Traditional JPQL - errors at runtime
em.createQuery("SELECT e FROM Employe e");  // Typo! Runtime error

// jOOQ - errors at compile time
create.selectFrom(EMPLOYE);  // Won't compile if EMPLOYE doesn't exist

jOOQ Examples

import static org.jooq.impl.DSL.*;
import static com.example.generated.Tables.*;  // Generated from DB

public class EmployeeRepository {

    private final DSLContext create;

    // Simple query
    public List<Employee> findAll() {
        return create
            .selectFrom(EMPLOYEES)
            .fetchInto(Employee.class);
    }

    // Query with conditions
    public List<Employee> findBySalaryRange(BigDecimal min, BigDecimal max) {
        return create
            .selectFrom(EMPLOYEES)
            .where(EMPLOYEES.SALARY.between(min, max))
            .orderBy(EMPLOYEES.SALARY.desc())
            .fetchInto(Employee.class);
    }

    // Join query
    public List<EmployeeWithDept> findWithDepartment() {
        return create
            .select(EMPLOYEES.NAME, DEPARTMENTS.NAME.as("dept_name"))
            .from(EMPLOYEES)
            .join(DEPARTMENTS).on(EMPLOYEES.DEPT_ID.eq(DEPARTMENTS.ID))
            .fetchInto(EmployeeWithDept.class);
    }

    // Aggregate query
    public Map<String, BigDecimal> avgSalaryByDepartment() {
        return create
            .select(DEPARTMENTS.NAME, avg(EMPLOYEES.SALARY))
            .from(EMPLOYEES)
            .join(DEPARTMENTS).on(EMPLOYEES.DEPT_ID.eq(DEPARTMENTS.ID))
            .groupBy(DEPARTMENTS.NAME)
            .fetchMap(DEPARTMENTS.NAME, avg(EMPLOYEES.SALARY));
    }

    // Insert
    public void insert(Employee emp) {
        create
            .insertInto(EMPLOYEES, EMPLOYEES.NAME, EMPLOYEES.EMAIL, EMPLOYEES.SALARY)
            .values(emp.getName(), emp.getEmail(), emp.getSalary())
            .execute();
    }

    // Update
    public void giveRaise(Long deptId, BigDecimal percentage) {
        create
            .update(EMPLOYEES)
            .set(EMPLOYEES.SALARY, EMPLOYEES.SALARY.multiply(BigDecimal.ONE.add(percentage)))
            .where(EMPLOYEES.DEPT_ID.eq(deptId))
            .execute();
    }

    // Complex dynamic query
    public List<Employee> search(SearchCriteria criteria) {
        SelectConditionStep<Record> query = create
            .selectFrom(EMPLOYEES)
            .where(trueCondition());  // Start with always-true

        if (criteria.getName() != null) {
            query = query.and(EMPLOYEES.NAME.containsIgnoreCase(criteria.getName()));
        }
        if (criteria.getMinSalary() != null) {
            query = query.and(EMPLOYEES.SALARY.ge(criteria.getMinSalary()));
        }
        if (criteria.getDeptId() != null) {
            query = query.and(EMPLOYEES.DEPT_ID.eq(criteria.getDeptId()));
        }

        return query.fetchInto(Employee.class);
    }
}

Choosing the Right Approach

Decision Matrix

Scenario	Recommended	Reason
New enterprise app	JPA (Hibernate)	Productivity, ecosystem, Spring integration
Complex queries, DBA team	MyBatis	Full SQL control, query optimization
Type-safe SQL, refactoring	jOOQ	Compile-time validation, IDE support
Simple CRUD, Spring Boot	Spring Data JPA	Minimal code, rapid development
Stored procedures heavy	MyBatis or jOOQ	Better stored procedure support
Database-first design	jOOQ	Code generation from schema
Domain-first design	JPA (Hibernate)	Code generates schema

Hybrid Approaches

// You can combine approaches!

// Spring Data JPA for simple CRUD
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    List<Employee> findByDepartmentId(Long deptId);
}

// Native query for complex cases
@Query(value = """
    SELECT e.*, d.name as dept_name
    FROM employees e
    JOIN departments d ON e.dept_id = d.id
    WHERE e.salary > (SELECT AVG(salary) FROM employees)
    """, nativeQuery = true)
List<EmployeeProjection> findHighEarners();

// Or use jOOQ for complex reporting while JPA handles CRUD
@Service
public class ReportService {

    private final DSLContext dsl;  // jOOQ

    public List<SalaryReport> generateSalaryReport() {
        return dsl
            .select(
                DEPARTMENTS.NAME,
                count(EMPLOYEES.ID).as("emp_count"),
                sum(EMPLOYEES.SALARY).as("total_salary"),
                avg(EMPLOYEES.SALARY).as("avg_salary")
            )
            .from(EMPLOYEES)
            .join(DEPARTMENTS).on(EMPLOYEES.DEPT_ID.eq(DEPARTMENTS.ID))
            .groupBy(DEPARTMENTS.NAME)
            .having(count(EMPLOYEES.ID).gt(5))
            .orderBy(avg(EMPLOYEES.SALARY).desc())
            .fetchInto(SalaryReport.class);
    }
}

ORM Best Practices

General Guidelines

Know your ORM: Understand how it generates SQL
Monitor SQL: Always enable SQL logging in development
Watch for N+1: Use JOIN FETCH or batch loading
Use DTOs: For read-only data, project into DTOs
Transaction boundaries: Keep transactions short
Don't fight the ORM: If ORM makes it hard, maybe use native SQL

Hibernate-Specific

Prefer LAZY: Make all relationships lazy by default
Avoid open-session-in-view: Fetch data in service layer
Use natural IDs: For business-meaningful lookups
Consider stateless session: For batch operations

MyBatis-Specific

Use result maps: For complex mappings
Leverage dynamic SQL: <if>, <choose>, <foreach>
Enable caching: For read-heavy queries

Summary

ORM: Bridges object-oriented and relational worlds
Hibernate: Full-featured ORM, JPA provider, most popular
MyBatis: SQL mapper, you control the SQL
jOOQ: Type-safe SQL builder, compile-time validation
Spring Data JPA: Repository abstraction over JPA
Trade-offs: Productivity vs Control vs Type Safety
Hybrid: Mix approaches for different use cases
Key: Understand generated SQL, monitor performance