Database Access (JDBC)

JDBC (Java Database Connectivity) is Java's standard API for accessing relational databases. JDBC provides interfaces for connecting to, executing queries against, and processing results from databases such as MySQL, PostgreSQL, Oracle, and SQLite.

A JDBC driver is required to connect to a specific database. Each vendor provides its own driver: MySQL (com.mysql.cj.jdbc.Driver), PostgreSQL (org.postgresql.Driver), SQLite (org.sqlite.JDBC). Drivers are added as dependencies on the classpath.

PreparedStatement is recommended over Statement to prevent SQL Injection. Try-with-resources ensures that Connection, Statement, and ResultSet are closed automatically after use. Transactions allow multiple queries to be executed atomically — all succeed or all fail.

JDBC Components:

Code Example

import java.sql.*;

public class JDBCExample {
    static final String URL = "jdbc:mysql://localhost:3306/shop";
    static final String USER = "root";
    static final String PASS = "";

    public static void main(String[] args) {
        // Create table
        String createSQL = """
            CREATE TABLE IF NOT EXISTS product (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                price DOUBLE NOT NULL,
                stock INT DEFAULT 0
            )
        """;

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             Statement stmt = conn.createStatement()) {

            // Create the table
            stmt.executeUpdate(createSQL);

            // Insert data (PreparedStatement — anti SQL Injection)
            String insertSQL = "INSERT INTO product (name, price, stock) VALUES (?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(insertSQL)) {
                ps.setString(1, "Laptop ASUS");
                ps.setDouble(2, 15000000);
                ps.setInt(3, 10);
                ps.executeUpdate();

                ps.setString(1, "Mouse Logitech");
                ps.setDouble(2, 250000);
                ps.setInt(3, 50);
                ps.executeUpdate();
            }

            // Read data
            String selectSQL = "SELECT * FROM product WHERE price > ?";
            try (PreparedStatement ps = conn.prepareStatement(selectSQL)) {
                ps.setDouble(1, 100000);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        System.out.printf("ID: %d | %s | $%,.0f | Stock: %d%n",
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getDouble("price"),
                            rs.getInt("stock"));
                    }
                }
            }

        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
        }
    }
}

Code Explanation

1. DriverManager.getConnection() opens a connection to the database. The JDBC URL is in the format: jdbc:driver://host:port/database.
2. Try-with-resources ensures that Connection, Statement, PreparedStatement, and ResultSet are closed automatically — preventing connection leaks.
3. PreparedStatement uses ? as placeholders, filled with setString(), setDouble(), and so on. This prevents SQL injection because the data is not merged directly into the query.
4. executeQuery() is for SELECT (returns a ResultSet), executeUpdate() is for INSERT/UPDATE/DELETE.
5. ResultSet.next() moves the cursor to the next row. Data is retrieved with getInt(), getString(), getDouble() by column name.

Everyday Example

A simple CRUD program for contact management:

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class KontakCRUD {
    static final String URL = "jdbc:sqlite:contact.db";

    static Connection getConn() throws SQLException {
        return DriverManager.getConnection(URL);
    }

    static void createTable() {
        try (var conn = getConn(); var stmt = conn.createStatement()) {
            stmt.executeUpdate("""
                CREATE TABLE IF NOT EXISTS contact (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    phone TEXT NOT NULL
                )
            """);
        } catch (SQLException e) { e.printStackTrace(); }
    }

    static void add(String name, String phone) {
        try (var conn = getConn();
             var ps = conn.prepareStatement("INSERT INTO contact(name,phone) VALUES(?,?)")) {
            ps.setString(1, name);
            ps.setString(2, phone);
            ps.executeUpdate();
        } catch (SQLException e) { e.printStackTrace(); }
    }

    static List<String> getAll() {
        List<String> list = new ArrayList<>();
        try (var conn = getConn();
             var rs = conn.createStatement().executeQuery("SELECT * FROM contact")) {
            while (rs.next()) {
                list.add(rs.getInt("id") + ". " + rs.getString("name")
                    + " - " + rs.getString("phone"));
            }
        } catch (SQLException e) { e.printStackTrace(); }
        return list;
    }

    public static void main(String[] args) {
        createTable();
        add("John", "08123456789");
        add("Sally", "08567890123");
        getAll().forEach(System.out::println);
    }
}

Summary

JDBC is the foundation of database access in Java. Always use PreparedStatement to prevent SQL Injection, try-with-resources for resource management, and consider connection pooling (HikariCP) for production applications. For advanced ORM, learn Hibernate/JPA.