Java Database Connectivity (JDBC)

// DriverManager — simple but no pooling
Connection conn = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/mydb", "user", "pass");

// DataSource — production approach (e.g., HikariCP)
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("user");
config.setPassword("pass");
config.setMaximumPoolSize(10);

DataSource ds = new HikariDataSource(config);
Connection conn = ds.getConnection(); // borrows from pool

try (Statement stmt = conn.createStatement()) {
    ResultSet rs = stmt.executeQuery("SELECT id, name FROM employees");
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
    }
}

String sql = "SELECT * FROM employees WHERE department = ? AND salary > ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, "Engineering");
    ps.setDouble(2, 80000.0);

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    }
}

// Calling: CREATE PROCEDURE get_employee_count(IN dept VARCHAR(50), OUT cnt INT)
String sql = "{CALL get_employee_count(?, ?)}";
try (CallableStatement cs = conn.prepareCall(sql)) {
    cs.setString(1, "Engineering");
    cs.registerOutParameter(2, Types.INTEGER);
    cs.execute();

    int count = cs.getInt(2);
    System.out.println("Employee count: " + count);
}

Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_UPDATABLE
);

ResultSet rs = stmt.executeQuery("SELECT id, name, salary FROM employees");

// Navigate freely
rs.absolute(5);         // jump to row 5
rs.relative(-2);        // move back 2 rows
rs.last();              // jump to last row
rs.beforeFirst();       // reset to before first row

// Update through ResultSet
rs.absolute(3);
rs.updateDouble("salary", 95000.0);
rs.updateRow();         // commits the change to the database

String sql = "INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    conn.setAutoCommit(false);

    for (Employee emp : employees) {
        ps.setString(1, emp.getName());
        ps.setString(2, emp.getDepartment());
        ps.setDouble(3, emp.getSalary());
        ps.addBatch();
    }

    int[] results = ps.executeBatch();  // single round-trip
    conn.commit();
} catch (BatchUpdateException e) {
    conn.rollback();
    int[] updateCounts = e.getUpdateCounts(); // partial results
}

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

    // Transfer $500 from account A to account B
    PreparedStatement debit = conn.prepareStatement(
        "UPDATE accounts SET balance = balance - ? WHERE id = ?");
    debit.setDouble(1, 500.0);
    debit.setInt(2, accountA);
    debit.executeUpdate();

    PreparedStatement credit = conn.prepareStatement(
        "UPDATE accounts SET balance = balance + ? WHERE id = ?");
    credit.setDouble(1, 500.0);
    credit.setInt(2, accountB);
    credit.executeUpdate();

    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
} finally {
    conn.setAutoCommit(true);
    conn.close();
}

conn.setAutoCommit(false);
Savepoint sp = null;

try {
    // Step 1: always required
    stmt.executeUpdate("INSERT INTO orders (id, total) VALUES (1, 100)");

    sp = conn.setSavepoint("beforeItems");

    // Step 2: optional — may fail
    stmt.executeUpdate("INSERT INTO order_items (order_id, item) VALUES (1, 'Widget')");

} catch (SQLException e) {
    if (sp != null) {
        conn.rollback(sp);  // only undo step 2, keep step 1
    }
}
conn.commit();

conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
config.setUsername("user");
config.setPassword("pass");

// Pool sizing
config.setMaximumPoolSize(10);       // max connections
config.setMinimumIdle(5);            // min idle connections
config.setIdleTimeout(300000);       // 5 min idle before eviction
config.setConnectionTimeout(30000);  // 30s wait for connection
config.setMaxLifetime(1800000);      // 30 min max connection age

// Performance tuning
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");

DataSource ds = new HikariDataSource(config);

// Writing a BLOB
String sql = "INSERT INTO documents (name, content) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, "report.pdf");
    try (FileInputStream fis = new FileInputStream("/path/to/report.pdf")) {
        ps.setBinaryStream(2, fis);
    }
    ps.executeUpdate();
}

// Reading a BLOB
try (ResultSet rs = stmt.executeQuery("SELECT content FROM documents WHERE id = 1")) {
    if (rs.next()) {
        try (InputStream is = rs.getBinaryStream("content");
             FileOutputStream fos = new FileOutputStream("/output/report.pdf")) {
            is.transferTo(fos);
        }
    }
}

// Writing a CLOB
ps.setClob(1, new StringReader(largeText));

// Reading a CLOB
Clob clob = rs.getClob("description");
String text = clob.getSubString(1, (int) clob.length());
clob.free(); // release resources

// CachedRowSet — disconnected operation
CachedRowSet crs = RowSetProvider.newFactory().createCachedRowSet();
crs.setUrl("jdbc:mysql://localhost:3306/mydb");
crs.setUsername("user");
crs.setPassword("pass");
crs.setCommand("SELECT * FROM employees WHERE dept = ?");
crs.setString(1, "Engineering");

crs.execute();  // connects, fetches data, disconnects

// Work offline
while (crs.next()) {
    System.out.println(crs.getString("name"));
}

// Reconnect and sync changes
crs.acceptChanges();

// VULNERABLE — never do this
String sql = "SELECT * FROM users WHERE name = '" + userInput + "'";
// If userInput = "'; DROP TABLE users; --" → disaster

// SAFE — always use PreparedStatement
String sql = "SELECT * FROM users WHERE name = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, userInput);  // input is escaped automatically

// Modern, correct approach
public List<Employee> findByDepartment(DataSource ds, String dept) 
        throws SQLException {
    String sql = "SELECT id, name, salary FROM employees WHERE department = ?";
    List<Employee> result = new ArrayList<>();

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

        ps.setString(1, dept);

        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                result.add(new Employee(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getDouble("salary")
                ));
            }
        }
    }
    // Connection, PreparedStatement, and ResultSet are all closed automatically
    return result;
}