JDBC Architecture

// Application code depends ONLY on java.sql interfaces — never on vendor classes directly
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ArchitectureDemo {
    public static void main(String[] args) throws SQLException {
        // The URL prefix (jdbc:postgresql:, jdbc:mysql:, jdbc:oracle:thin:) tells
        // DriverManager which vendor driver implementation to hand back —
        // but the returned type is always the java.sql.Connection INTERFACE
        String url = "jdbc:postgresql://localhost:5432/storedb";
        try (Connection conn = DriverManager.getConnection(url, "app_user", "secret")) {
            // 'conn' is statically typed as Connection — an interface —
            // even though at runtime it's really an org.postgresql.jdbc.PgConnection
            System.out.println("Driver in use: " + conn.getMetaData().getDriverName());
            System.out.println("Driver version: " + conn.getMetaData().getDriverVersion());

            try (PreparedStatement ps = conn.prepareStatement(
                    "SELECT id, name FROM products WHERE price > ?")) {
                ps.setBigDecimal(1, new java.math.BigDecimal("19.99"));
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        System.out.println(rs.getLong("id") + ": " + rs.getString("name"));
                    }
                }
            }
        }
        // Swap the URL to jdbc:mysql://... and add the MySQL driver JAR —
        // this exact same code compiles and runs unchanged against MySQL.
    }
}

// Type 4 (pure Java, direct protocol) is what nearly every modern app uses.
// Identifying it is just a matter of the JAR you add and the URL prefix:

// PostgreSQL — Type 4, pgjdbc speaks Postgres's wire protocol directly
String pgUrl = "jdbc:postgresql://db.example.com:5432/mydb";

// MySQL — Type 4, Connector/J speaks MySQL's protocol directly
String mysqlUrl = "jdbc:mysql://db.example.com:3306/mydb";

// Oracle — Type 4, the "thin" driver speaks Oracle Net (TNS) protocol in pure Java
String oracleUrl = "jdbc:oracle:thin:@db.example.com:1521:ORCL";

// SQL Server — Type 4, mssql-jdbc speaks TDS protocol directly
String mssqlUrl = "jdbc:sqlserver://db.example.com:1433;databaseName=mydb";

// In every case, no native libraries are installed and no middleware server
// is involved — adding the driver JAR to the classpath is sufficient.
// Contrast with the now-removed Type 1 JDBC-ODBC Bridge, which required
// an ODBC driver manager and a registered ODBC data source on the OS itself:
//   jdbc:odbc:MyDataSourceName   (removed from the JDK starting with Java 8)

// ── DriverManager: simple, but creates a brand-new physical connection each time ──
import java.sql.Connection;
import java.sql.DriverManager;

Connection raw = DriverManager.getConnection(
        "jdbc:postgresql://localhost:5432/storedb", "app_user", "secret");
// Expensive: TCP handshake + auth handshake happen again every single call

// ── DataSource + connection pool: the production architecture ──────────────
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.sql.Connection;

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/storedb");
config.setUsername("app_user");
config.setPassword("secret");
config.setMaximumPoolSize(10);

DataSource dataSource = new HikariDataSource(config);  // built once, shared app-wide

// Application code now depends on DataSource, not DriverManager —
// each call below borrows an already-open connection from the pool:
try (Connection conn = dataSource.getConnection()) {
    // use the connection
} // conn.close() here returns it to the pool rather than tearing down the socket

// ── In a Jakarta EE / application-server environment, DataSource is typically
// looked up via JNDI rather than constructed directly:
//   DataSource ds = (DataSource) new InitialContext().lookup("java:comp/env/jdbc/MyDS");