JDBC — Java Database Connectivity

What is JDBC — and Why Should a JPA Developer Learn It?

JDBC (Java Database Connectivity) is the standard Java API for communicating with relational databases. It's a set of interfaces in java.sql and javax.sql; database vendors supply the concrete implementations as drivers. Your code uses the same API whether the database is PostgreSQL, MySQL, or Oracle.

Every Java database abstraction — Hibernate, Spring Data JPA, Spring JDBC, MyBatis, jOOQ — translates your high-level operations down to JDBC at the bottom. When JPA generates an unexpected query, when Hibernate throws a cryptic exception, when a query is inexplicably slow, the investigation always ends at actual SQL sent over a JDBC connection. Understanding JDBC is what lets you debug those situations instead of guessing.

// The full stack for a typical Spring Boot + JPA application

  Your code (@Repository, @Query, findByEmail)
      │
      ▼
  Spring Data JPA   ← generates the JPQL / method query
      │
      ▼
  Hibernate ORM     ← translates JPQL to SQL
      │
      ▼
  JDBC API          ← PreparedStatement, ResultSet
      │
      ▼
  JDBC Driver       ← vendor-specific (e.g. pgjdbc for PostgreSQL)
      │
      ▼
  Database          ← executes the SQL

Setup: Driver and DataSource

Driver dependency

<!-- PostgreSQL (preferred for new projects) -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <!-- managed by Spring Boot BOM; no version needed in Boot projects -->
</dependency>

<!-- MySQL -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
</dependency>

<!-- H2 for tests —  scope=test keeps it off the production classpath -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>

DataSource vs DriverManager

// What DriverManager looks like under the hood — educational only
// Creates a new TCP connection every call. Never do this in a server.
Connection conn = DriverManager.getConnection(
    "jdbc:postgresql://localhost:5432/shop", "app", "secret");

// What you actually use in production — DataSource from HikariCP
// Spring Boot auto-configures this from application.properties
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/shop");
config.setUsername("app");
config.setPassword("${DB_PASSWORD}");
config.setMaximumPoolSize(20);
DataSource dataSource = new HikariDataSource(config);
// Pool sizing and tuning covered in Connection Pooling

# Spring Boot: configure the DataSource from properties — nothing else needed
spring.datasource.url=jdbc:postgresql://localhost:5432/shop
spring.datasource.username=app
spring.datasource.password=${DB_PASSWORD}

# HikariCP is the default pool in Spring Boot
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000

JDBC URL formats

// Format: jdbc:subprotocol://host:port/database[?options]
"jdbc:postgresql://localhost:5432/shop"
"jdbc:postgresql://prod-db.internal:5432/shop?ssl=true&sslmode=require"

"jdbc:mysql://localhost:3306/shop"
"jdbc:mysql://localhost:3306/shop?useSSL=true&serverTimezone=UTC"

"jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1"   // in-memory, kept alive for test duration
"jdbc:h2:file:./data/shop"               // file-based H2

PreparedStatement: the Only Correct Way to Send Parameters

A PreparedStatement separates the SQL structure from the parameter values. The SQL is sent once; the database compiles it once; subsequent calls only send the values. This prevents SQL injection (the values can never change the structure of the query) and allows the database to reuse the execution plan.

SELECT — reading data

public class OrderRepository {

    private final DataSource dataSource;

    public OrderRepository(DataSource dataSource) {
        this.dataSource = dataSource;   // constructor injection — never field injection
    }

    public Optional<Order> findById(long id) {
        String sql = """
            SELECT o.id, o.status, o.created_at, c.email AS customer_email
            FROM orders o
            JOIN customers c ON c.id = o.customer_id
            WHERE o.id = ?
            """;

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

            ps.setLong(1, id);   // parameter index starts at 1

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRow(rs));
                }
                return Optional.empty();
            }

        } catch (SQLException e) {
            throw new DataAccessException("Failed to find order: " + id, e);
        }
    }

    public List<Order> findByStatus(String status, int limit) {
        String sql = """
            SELECT o.id, o.status, o.created_at, c.email AS customer_email
            FROM orders o
            JOIN customers c ON c.id = o.customer_id
            WHERE o.status = ?
            ORDER BY o.created_at DESC
            LIMIT ?
            """;

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

            ps.setString(1, status);
            ps.setInt(2, limit);

            try (ResultSet rs = ps.executeQuery()) {
                List<Order> orders = new ArrayList<>();
                while (rs.next()) {
                    orders.add(mapRow(rs));
                }
                return orders;
            }

        } catch (SQLException e) {
            throw new DataAccessException("Failed to find orders by status", e);
        }
    }

    private Order mapRow(ResultSet rs) throws SQLException {
        return new Order(
            rs.getLong("id"),
            rs.getString("status"),
            rs.getString("customer_email"),
            rs.getObject("created_at", OffsetDateTime.class)  // see Section 4
        );
    }
}

INSERT with generated key retrieval

public long save(CreateOrderRequest request) {
    String sql = "INSERT INTO orders (customer_id, status) VALUES (?, ?)";

    try (Connection conn = dataSource.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

        ps.setLong(1, request.customerId());
        ps.setString(2, "PENDING");
        ps.executeUpdate();

        try (ResultSet keys = ps.getGeneratedKeys()) {
            if (keys.next()) {
                return keys.getLong(1);   // the auto-generated ID
            }
            throw new DataAccessException("No key returned after insert", null);
        }

    } catch (SQLException e) {
        throw new DataAccessException("Failed to save order", e);
    }
}

UPDATE and DELETE — always check affected rows

public void updateStatus(long orderId, String newStatus) {
    String sql = "UPDATE orders SET status = ? WHERE id = ?";

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

        ps.setString(1, newStatus);
        ps.setLong(2, orderId);

        int affected = ps.executeUpdate();
        if (affected == 0) {
            throw new OrderNotFoundException(orderId);
            // executeUpdate() returning 0 means the WHERE clause matched nothing.
            // Silently ignoring this is how "updates that silently do nothing" happen.
        }

    } catch (SQLException e) {
        throw new DataAccessException("Failed to update order status", e);
    }
}

public void delete(long orderId) {
    String sql = "DELETE FROM orders WHERE id = ?";

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

        ps.setLong(1, orderId);
        ps.executeUpdate();   // returns the count; check it if idempotence matters

    } catch (SQLException e) {
        throw new DataAccessException("Failed to delete order", e);
    }
}

Batch INSERT — when performance matters

// Inserting 10,000 order items one by one: 10,000 round trips to the database.
// Batch insert: one round trip per batch. Dramatically faster under load.

public void saveItems(long orderId, List<OrderItemRequest> items) {
    String sql = """
        INSERT INTO order_items (order_id, product_id, quantity, unit_price)
        VALUES (?, ?, ?, ?)
        """;

    try (Connection conn = dataSource.getConnection()) {
        conn.setAutoCommit(false);

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            int batchCount = 0;

            for (OrderItemRequest item : items) {
                ps.setLong(1, orderId);
                ps.setLong(2, item.productId());
                ps.setInt(3, item.quantity());
                ps.setBigDecimal(4, item.unitPrice());
                ps.addBatch();

                if (++batchCount % 500 == 0) {
                    ps.executeBatch();  // flush every 500 rows — avoids unbounded memory growth
                }
            }

            ps.executeBatch();   // flush the remaining rows
            conn.commit();

        } catch (SQLException e) {
            conn.rollback();
            throw new DataAccessException("Batch insert failed", e);
        }

    } catch (SQLException e) {
        throw new DataAccessException("Failed to get connection", e);
    }
}

Transaction Management in JDBC

By default, every statement in JDBC auto-commits immediately. To group multiple statements into a single atomic operation, disable auto-commit, execute the statements, then commit or roll back.

public class OrderFulfillmentService {

    private final DataSource dataSource;

    public OrderFulfillmentService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // Place an order: debit stock and create the order atomically
    public long placeOrder(long customerId, long productId, int quantity) {
        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);

            try {
                // Step 1: check and debit stock atomically
                String debitSql = """
                    UPDATE products
                    SET stock = stock - ?
                    WHERE id = ? AND stock >= ?
                    """;
                try (PreparedStatement ps = conn.prepareStatement(debitSql)) {
                    ps.setInt(1, quantity);
                    ps.setLong(2, productId);
                    ps.setInt(3, quantity);
                    if (ps.executeUpdate() == 0) {
                        throw new InsufficientStockException(productId, quantity);
                    }
                }

                // Step 2: create the order
                long orderId;
                String orderSql = "INSERT INTO orders (customer_id, status) VALUES (?, 'PENDING')";
                try (PreparedStatement ps = conn.prepareStatement(orderSql, Statement.RETURN_GENERATED_KEYS)) {
                    ps.setLong(1, customerId);
                    ps.executeUpdate();
                    try (ResultSet keys = ps.getGeneratedKeys()) {
                        keys.next();
                        orderId = keys.getLong(1);
                    }
                }

                conn.commit();
                return orderId;

            } catch (Exception e) {
                conn.rollback();
                throw e;   // re-throw after rollback — never swallow
            }

        } catch (SQLException e) {
            throw new DataAccessException("Failed to place order", e);
        }
        // try-with-resources on Connection resets autoCommit and returns it to pool
    }
}

Date and Time: The Part Everyone Gets Wrong

JDBC's legacy date/time API (java.sql.Date, java.sql.Timestamp) predates java.time and has well-known timezone conversion bugs. Since JDBC 4.2, the correct approach is to use java.time types directly via setObject() and getObject(Class).

// WRONG — legacy types with silent timezone conversion
ps.setTimestamp(1, Timestamp.from(instant));
// Timestamp.from() uses the JVM default timezone.
// If the JVM and the database have different default timezones,
// the stored value will be wrong by the offset difference.

Timestamp ts = rs.getTimestamp("created_at");
// Also uses the JVM timezone. If the server moved or the JVM default changed,
// every historical timestamp reads as a different moment.

// CORRECT — java.time types directly via JDBC 4.2
// WRITING:
ps.setObject(1, OffsetDateTime.now(ZoneOffset.UTC));   // always UTC at the boundary
ps.setObject(2, LocalDate.of(2026, 7, 1));                // date-only, no timezone

// READING:
OffsetDateTime createdAt = rs.getObject("created_at", OffsetDateTime.class);
LocalDate deliveryDate  = rs.getObject("delivery_date", LocalDate.class);

// Handle nullable timestamps correctly:
OffsetDateTime deletedAt = rs.getObject("deleted_at", OffsetDateTime.class);
// getObject() returns null when the column is NULL — no wasNull() check needed

Spring JdbcTemplate — JDBC Without the Boilerplate

Raw JDBC requires the same try-with-resources, connection retrieval, and exception handling on every method. Spring's JdbcTemplate removes that repetition while keeping full control over the SQL — a significant step up from raw JDBC without the full ORM abstraction of JPA.

<!-- Spring Boot auto-configures JdbcTemplate when this is on the classpath -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

@Repository
public class OrderRepository {

    private final JdbcTemplate jdbc;

    public OrderRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    // Query for a list — JdbcTemplate handles connection, PreparedStatement, ResultSet
    public List<Order> findByStatus(String status) {
        String sql = """
            SELECT o.id, o.status, o.created_at, c.email AS customer_email
            FROM orders o
            JOIN customers c ON c.id = o.customer_id
            WHERE o.status = ?
            ORDER BY o.created_at DESC
            """;
        return jdbc.query(sql, this::mapRow, status);
    }

    // Query for a single row — throws EmptyResultDataAccessException if nothing found
    public Optional<Order> findById(long id) {
        try {
            Order order = jdbc.queryForObject(
                "SELECT o.id, o.status, o.created_at, c.email AS customer_email FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.id = ?",
                this::mapRow,
                id
            );
            return Optional.ofNullable(order);
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty();
        }
    }

    // Query for a scalar value
    public long countByStatus(String status) {
        return jdbc.queryForObject(
            "SELECT COUNT(*) FROM orders WHERE status = ?",
            Long.class,
            status
        );
    }

    // INSERT returning generated key
    public long save(long customerId) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbc.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(
                "INSERT INTO orders (customer_id, status) VALUES (?, 'PENDING')",
                Statement.RETURN_GENERATED_KEYS
            );
            ps.setLong(1, customerId);
            return ps;
        }, keyHolder);
        return keyHolder.getKey().longValue();
    }

    // UPDATE / DELETE — update() returns affected row count
    public boolean updateStatus(long orderId, String newStatus) {
        int affected = jdbc.update(
            "UPDATE orders SET status = ? WHERE id = ?",
            newStatus, orderId
        );
        return affected > 0;
    }

    private Order mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new Order(
            rs.getLong("id"),
            rs.getString("status"),
            rs.getString("customer_email"),
            rs.getObject("created_at", OffsetDateTime.class)
        );
    }
}

The DAO Pattern

The Data Access Object pattern isolates all database access behind an interface. The rest of the application works against the interface — it doesn't know or care whether the implementation uses raw JDBC, JdbcTemplate, or JPA. This makes the persistence layer replaceable and the service layer testable without a database.

// The contract — no SQL, no JDBC, no implementation detail
public interface OrderDao {
    Optional<Order> findById(long id);
    List<Order> findByStatus(String status);
    long save(long customerId);
    boolean updateStatus(long orderId, String newStatus);
    void delete(long orderId);
}

// JDBC implementation — service code never sees a Statement or ResultSet
@Repository
public class JdbcOrderDao implements OrderDao {

    private final JdbcTemplate jdbc;

    public JdbcOrderDao(JdbcTemplate jdbc) { this.jdbc = jdbc; }

    // ... methods from Section 5
}

// Service layer — only knows the interface
@Service
public class OrderService {

    private final OrderDao orderDao;

    public OrderService(OrderDao orderDao) { this.orderDao = orderDao; }

    public OrderDetail getOrder(long id) {
        return orderDao.findById(id)
            .map(OrderDetail::from)
            .orElseThrow(() -> new OrderNotFoundException(id));
    }
}

// In tests: inject a mock instead of a real database
@ExtendWith(MockitoExtension.class)
class OrderServiceTest {
    @Mock  OrderDao orderDao;
    @InjectMocks OrderService orderService;

    @Test
    void throwsWhenOrderNotFound() {
        Mockito.when(orderDao.findById(99L)).thenReturn(Optional.empty());
        assertThrows(OrderNotFoundException.class, () -> orderService.getOrder(99L));
    }
}

Best Practices and Common Pitfalls

Interview Questions