Interacting with Databases in Java: JDBC, ORM, and JPA

A Database Management System (DBMS) is software used to store, manage, and retrieve data efficiently. Popular examples include MySQL, PostgreSQL, Oracle, MariaDB, and SQL Server. Traditionally, Java applications communicated with DBMSs using JDBC. As applications grew in complexity, developers needed a less repetitive, more object-focused approach, leading to the rise of Object-Relational Mapping (ORM) and eventually the Java Persistence API (JPA).

1. Creating a MySQL User

Applications should not connect as root. Create a dedicated MySQL user:

CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT ON *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

This user now has sufficient privileges for typical CRUD operations.

2. JDBC (Java Database Connectivity)

JDBC is the low-level API used for direct SQL execution in Java. It gives full control but requires more boilerplate code.

2.1 Connecting to MySQL

import java.sql.*;

public class ConnectExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/dbms";
        String user = "sammy";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            System.out.println("Connected to database!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2.2 Running a SELECT Query

String query = "SELECT * FROM company";

try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(query)) {

    while (rs.next()) {
        System.out.println(rs.getString(1) + " " 
                         + rs.getInt(2) + " " 
                         + rs.getString(3));
    }
}

2.3 Insert Example

String insert = "INSERT INTO company VALUES ('BMW', 10, 'Germany')";

try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement()) {

    stmt.executeUpdate(INSERT);
    conn.COMMIT();  // or automatic IF AUTOCOMMIT=true
}

3. Introduction to ORM (Object-Relational Mapping)

ORM is a programming technique that allows developers to interact with a relational database using Java objects. Instead of manually writing SQL and processing ResultSet rows, the ORM handles:

• Mapping Java classes ↔ tables
• Mapping fields ↔ columns
• Automatic SQL generation
• Lazy loading
• Transaction management
• Caching

History & Motivation

Originally, Java developers used JDBC exclusively. As applications grew, developers wanted:

• Less boilerplate
• Automatic object mapping
• Reusable queries
• Caching and lazy loading
• Vendor independence

This led to early ORMs like Hibernate, and eventually the Java Persistence API (JPA), which standardized ORM behavior across Java.

How ORM and JPA Are Related

JPA is not an ORM itself—it is a specification. ORM frameworks like Hibernate, EclipseLink, and OpenJPA are implementations of the JPA standard.

In short:

• ORM = concept + technique (object ↔ relational mapping)
• JPA = the official Java API/standard for ORM
• Hibernate = most widely used JPA implementation

4. Complete ORM Example (End-to-End)

This full example shows:

• Java entity class
• JPA annotations
• CRUD operations
• SQL generated behind the scenes

4.1 The Entity (Mapped to a Table)

import jakarta.persistence.*;

@Entity
@Table(name = "student")
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private int year;

    // getters AND setters
}

4.2 Persisting an Object (Insert)

EntityManager em = emf.createEntityManager();
em.getTransaction().BEGIN();

Student s = new Student();
s.setName("Alice");
s.setYear(2025);

em.persist(s);

em.getTransaction().COMMIT();
em.CLOSE();

Generated SQL under the hood:

INSERT INTO student (name, YEAR) VALUES (?, ?);

4.3 Query with JPQL

String jpql = "SELECT s FROM Student s WHERE s.year = :yr";

List list = em.createQuery(jpql, Student.class)
                       .setParameter("yr", 2025)
                       .getResultList();

4.4 Update

em.getTransaction().BEGIN();
Student s = em.find(Student.class, 1L);
s.setName("Alicia");   // Hibernate detects this CHANGE automatically
em.getTransaction().COMMIT();

4.5 Delete

em.getTransaction().BEGIN();
Student s = em.find(Student.class, 1L);
em.remove(s);
em.getTransaction().COMMIT();

5. Relationships in JPA

One-to-Many Example with Lazy Loading

@Entity
public class User {

    @Id @GeneratedValue
    private Long id;

    private String name;

    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    private List orders = new ArrayList<>();
}

@Entity
public class Order {

    @Id @GeneratedValue
    private Long id;

    private double total;

    @ManyToOne
    private User user;
}

6. Spring Data JPA Example

public interface StudentRepository 
       extends JpaRepository {
    List findByYear(int YEAR);
}

7. What Happens Under the Hood (ORM + JPA)

• Annotation scanning loads mappings
• EntityManager tracks state (new, managed, detached, removed)
• Dirty checking auto-detects changes
• SQL generation is optimized and cached
• Lazy loading uses runtime proxies
• Dialect adapts SQL to MySQL/Postgres/Oracle

8. When to Use JDBC vs ORM vs JPA