Java JDBC - Database Connection, PreparedStatement and ResultSet | Java Codeex

1. What is JDBC?

JDBC (Java Database Connectivity) is a standard Java API that defines how Java applications communicate with relational databases. It is part of the Java SE platform (java.sql and javax.sql packages).

Key characteristics:

• Database-agnostic: The same Java code works with MySQL, PostgreSQL, Oracle, SQLite, and more — you only change the driver and connection URL.
• Driver-based: Each database vendor provides a JDBC driver (a JAR file) that translates JDBC calls into database-specific wire protocol.
• SQL-based: You write standard SQL — JDBC sends it to the database and returns results as Java objects.

2. JDBC Architecture

JDBC follows a layered architecture that decouples your application code from any specific database:

Layer	Component	Role
1	Java Application	Your code — calls JDBC API interfaces.
2	JDBC API	Interfaces: Connection, Statement, ResultSet, etc.
3	DriverManager	Manages registered drivers; creates Connection objects.
4	JDBC Driver	Vendor-specific JAR that translates JDBC calls to database protocol.
5	Database	MySQL, PostgreSQL, Oracle, SQLite, etc.

// The 5-step JDBC workflow
// 1. Load the driver (automatic since JDBC 4.0)
// 2. Establish a connection
Connection conn = DriverManager.getConnection(url, user, pass);

// 3. Create a statement
Statement stmt = conn.createStatement();

// 4. Execute SQL and get results
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

// 5. Process results and close resources
while (rs.next()) { System.out.println(rs.getString("name")); }
rs.close(); stmt.close(); conn.close();

3. JDBC Driver Types

Type	Name	Description	Use Today?
Type 1	JDBC-ODBC Bridge	Converts JDBC calls to ODBC calls. Requires ODBC driver installed on client.	No — removed in Java 8.
Type 2	Native-API Driver	Uses database-specific native libraries. Requires native install on client.	Rare.
Type 3	Network Protocol Driver	Sends JDBC calls over network to a middleware server that talks to the DB.	Rare.
Type 4	Thin Driver (Pure Java)	Pure Java driver that communicates directly with the database using its native network protocol. No native libraries needed.	Yes — standard choice.

4. Setting Up JDBC

Add the database driver to your project. With Maven, add to pom.xml:

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

<!-- PostgreSQL -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.3</version>
</dependency>

<!-- SQLite (great for learning, no server required) -->
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.45.1.0</version>
</dependency>

Since JDBC 4.0 (Java 6+), drivers are auto-loaded via the Service Provider mechanism — no Class.forName() is needed. For legacy code you may still see:

// Legacy: manually load driver class (not needed since JDBC 4.0)
Class.forName("com.mysql.cj.jdbc.Driver");      // MySQL
Class.forName("org.postgresql.Driver");          // PostgreSQL
Class.forName("oracle.jdbc.driver.OracleDriver"); // Oracle

5. Establishing a Connection

DriverManager.getConnection(url, user, password) creates a Connection object. The JDBC URL format varies by database:

import java.sql.*;

public class ConnectionDemo {
    // MySQL connection URL format:
    // jdbc:mysql://host:port/database?parameters
    static final String MYSQL_URL =
        "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";

    // PostgreSQL:
    // static final String PG_URL = "jdbc:postgresql://localhost:5432/mydb";

    // SQLite (file-based, no server needed):
    // static final String SQLITE_URL = "jdbc:sqlite:mydb.sqlite";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                MYSQL_URL, "root", "password")) {

            System.out.println("Connected to: " + conn.getMetaData().getURL());
            System.out.println("Driver:       "
                + conn.getMetaData().getDriverName());
            System.out.println("Auto-commit:  " + conn.getAutoCommit());
            System.out.println("DB Product:   "
                + conn.getMetaData().getDatabaseProductName() + " "
                + conn.getMetaData().getDatabaseProductVersion());

        } catch (SQLException e) {
            System.err.println("Connection failed!");
            System.err.println("SQL State: " + e.getSQLState());
            System.err.println("Error Code: " + e.getErrorCode());
            System.err.println("Message: " + e.getMessage());
        }
    }
}