SQL Database

String url = "jdbc:postgresql://localhost:5432/mydb";  // => JDBC connection URL
                                                         // => Format: jdbc:<database>://<host>:<port>/<database-name>
                                                         // => postgresql is database type, localhost:5432 is server
String user = "dbuser";  // => Database username
String password = "dbpass";  // => Database password (NEVER hardcode in production - use environment variables!)

try (Connection conn = DriverManager.getConnection(url, user, password)) {  // => Establishes TCP connection to database
                                                                             // => Authenticates with username/password
                                                                             // => Connection is "conn" object
                                                                             // => try-with-resources auto-closes connection
    // Use connection  // => Execute queries, inserts, updates, deletes
} catch (SQLException e) {  // => Catches database errors (connection failed, authentication failed)
    System.err.println("Connection error: " + e.getMessage());  // => Prints error message
}  // => Connection automatically closed here (try-with-resources)

String sql = "SELECT id, name, email FROM users WHERE active = true";

try (Connection conn = getConnection();
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {

    while (rs.next()) {
        long id = rs.getLong("id");
        String name = rs.getString("name");
        String email = rs.getString("email");

        System.out.printf("User %d: %s (%s)%n", id, name, email);
    }
} catch (SQLException e) {
    System.err.println("Query error: " + e.getMessage());
}

String sql = "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')";

try (Connection conn = getConnection();
     Statement stmt = conn.createStatement()) {

    int rowsAffected = stmt.executeUpdate(sql);
    System.out.println("Inserted " + rowsAffected + " row(s)");
} catch (SQLException e) {
    System.err.println("Insert error: " + e.getMessage());
}

// DANGEROUS - SQL INJECTION VULNERABILITY
String username = request.getParameter("username");  // => Gets username from HTTP request (user-controlled input!)
String sql = "SELECT * FROM users WHERE username = '" + username + "'";  // => String concatenation creates SQL
                                                                          // => If username = "alice", SQL is: SELECT * FROM users WHERE username = 'alice'
                                                                          // => If username = "' OR '1'='1", SQL is: SELECT * FROM users WHERE username = '' OR '1'='1'
                                                                          // => Attack SQL returns ALL users (bypasses authentication!)
// Attack: username = "' OR '1'='1"  // => Attacker closes quote, adds OR condition always true
                                     // => '1'='1' is always true → query returns all rows
                                     // => Can also use: "; DROP TABLE users; --" to delete table

String sql = "SELECT * FROM users WHERE username = ?";  // => Parameterized query with ? placeholder
                                                          // => ? is parameter marker (NOT string concatenation)
                                                          // => Database treats parameter as DATA, never as SQL code

try (Connection conn = getConnection();  // => Gets connection from pool or DriverManager
     PreparedStatement stmt = conn.prepareStatement(sql)) {  // => Creates PreparedStatement (compiles SQL once)
                                                              // => PreparedStatement is reusable, efficient

    stmt.setString(1, username);  // Parameter binding prevents injection  // => Binds username to parameter 1 (first ?)
                                                                            // => Parameter index starts at 1 (NOT 0!)
                                                                            // => Database escapes special characters automatically
                                                                            // => If username = "' OR '1'='1", treated as LITERAL string (not SQL)
                                                                            // => Attack SQL becomes: SELECT * FROM users WHERE username = '\' OR \'1\'=\'1\''
                                                                            // => Returns zero rows (safe!)

    try (ResultSet rs = stmt.executeQuery()) {  // => Executes prepared statement
                                                 // => Returns ResultSet with query results
        while (rs.next()) {  // => Iterates through result rows
            // Process results  // => Extract columns with rs.getString(), rs.getInt(), etc.
        }
    }  // => ResultSet auto-closed
} catch (SQLException e) {  // => Catches database errors
    System.err.println("Query error: " + e.getMessage());
}  // => PreparedStatement and Connection auto-closed

stmt.setString(1, "Alice");           // String
stmt.setInt(2, 30);                   // Integer
stmt.setTimestamp(3, timestamp);      // Timestamp
stmt.setBigDecimal(4, amount);        // BigDecimal (for money)

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";

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

    stmt.setString(1, "Alice");
    stmt.setString(2, "alice@example.com");

    int rowsAffected = stmt.executeUpdate();

    try (ResultSet rs = stmt.getGeneratedKeys()) {
        if (rs.next()) {
            long id = rs.getLong(1);
            System.out.println("Inserted user with ID: " + id);
        }
    }
} catch (SQLException e) {
    System.err.println("Insert error: " + e.getMessage());
}

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>6.2.1</version>
</dependency>

import com.zaxxer.hikari.*;

HikariConfig config = new HikariConfig();  // => Creates configuration object
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");  // => Database connection URL
config.setUsername("dbuser");  // => Database username
config.setPassword("dbpass");  // => Database password (use environment variables in production!)

// Pool configuration
config.setMaximumPoolSize(10);              // Max connections  // => Maximum 10 connections in pool
                                                                 // => Requests wait if all connections busy
                                                                 // => Too many connections waste memory, too few causes contention
config.setMinimumIdle(2);                   // Min idle connections  // => Keeps 2 connections ready (minimizes wait time)
                                                                       // => Pool grows from 2 to 10 based on demand
config.setConnectionTimeout(30000);          // 30s connection timeout  // => Wait max 30 seconds for connection before throwing exception
                                                                         // => Prevents infinite waiting if pool exhausted
config.setIdleTimeout(600000);              // 10min idle timeout  // => Idle connections closed after 10 minutes (saves resources)
                                                                    // => Pool shrinks back to minimumIdle when idle
config.setMaxLifetime(1800000);             // 30min max lifetime  // => Connections recycled after 30 minutes (prevents stale connections)
                                                                    // => Database may close connections after idle time - this prevents errors

HikariDataSource dataSource = new HikariDataSource(config);  // => Creates connection pool
                                                              // => Opens minimum idle connections immediately
                                                              // => Pool ready for requests

// Use connections
try (Connection conn = dataSource.getConnection()) {  // => Gets connection from pool (fast: ~1ms vs ~50-100ms without pooling)
                                                       // => Waits if all connections busy (up to connectionTimeout)
    // Execute queries  // => Use connection for database operations
}  // => try-with-resources returns connection to pool (NOT closed, reused!)
   // => Connection immediately available for next request

// Shutdown pool
dataSource.close();  // => Closes all connections in pool
                     // => Call on application shutdown ONLY (not per request!)

Connection conn = null;  // => Declare outside try for finally access
try {
    conn = dataSource.getConnection();  // => Gets connection from pool
    conn.setAutoCommit(false);  // Start transaction  // => Disables auto-commit (default: each statement commits immediately)
                                                        // => Transaction starts - changes not visible until commit()

    // Multiple operations in transaction
    updateInventory(conn, productId, -quantity);  // => UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?
                                                   // => Deducts quantity from inventory
    createOrder(conn, userId, productId, quantity);  // => INSERT INTO orders (user_id, product_id, quantity) VALUES (?, ?, ?)
                                                      // => Creates order record
    chargePayment(conn, userId, amount);  // => INSERT INTO payments (user_id, amount) VALUES (?, ?)
                                          // => Charges customer
                                          // => All three operations in SAME transaction - atomic

    conn.commit();  // Commit if all succeed  // => Makes all changes permanent
                                               // => Other connections can see changes
                                               // => Releases locks
} catch (SQLException e) {  // => Catches ANY database error in transaction
    if (conn != null) {  // => Check connection not null
        try {
            conn.rollback();  // Rollback on error  // => Undoes ALL changes in transaction
                                                     // => Inventory NOT deducted, order NOT created, payment NOT charged
                                                     // => Database returns to state before transaction
                                                     // => CRITICAL: All-or-nothing guarantee
        } catch (SQLException ex) {  // => Rollback can also fail (connection lost)
            ex.printStackTrace();
        }
    }
    throw new RuntimeException("Transaction failed", e);  // => Re-throw to caller
} finally {  // => Always executes (success or failure)
    if (conn != null) {
        try {
            conn.setAutoCommit(true);  // Restore auto-commit  // => Re-enables auto-commit for connection reuse
            conn.close();  // => Returns connection to pool (doesn't actually close)
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

conn.setAutoCommit(false);

Savepoint savepoint1 = conn.setSavepoint("beforeRiskyOperation");

try {
    riskyOperation(conn);
} catch (SQLException e) {
    conn.rollback(savepoint1);  // Rollback to savepoint
}

conn.commit();

while (rs.next()) {
    long id = rs.getLong("id");
    String name = rs.getString("name");
    LocalDate birthDate = rs.getObject("birth_date", LocalDate.class);
    BigDecimal balance = rs.getBigDecimal("balance");

    User user = new User(id, name, birthDate, balance);
}

String email = rs.getString("email");
if (rs.wasNull()) {
    email = "no-email@example.com";  // Default for null
}

public User mapUser(ResultSet rs) throws SQLException {
    return new User(
        rs.getLong("id"),
        rs.getString("name"),
        rs.getString("email"),
        rs.getTimestamp("created_at").toLocalDateTime()
    );
}

List<User> users = new ArrayList<>();
while (rs.next()) {
    users.add(mapUser(rs));
}

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";

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

    conn.setAutoCommit(false);

    for (User user : users) {
        stmt.setString(1, user.getName());
        stmt.setString(2, user.getEmail());
        stmt.addBatch();

        if (users.indexOf(user) % 100 == 0) {
            stmt.executeBatch();  // Execute every 100 rows
        }
    }

    stmt.executeBatch();  // Execute remaining
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>11.1.0</version>
</dependency>

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

import org.flywaydb.core.Flyway;

Flyway flyway = Flyway.configure()
    .dataSource(dataSource)
    .load();

flyway.migrate();  // Apply pending migrations

<changeSet id="1" author="alice">
    <createTable tableName="users">
        <column name="id" type="bigint" autoIncrement="true">
            <constraints primaryKey="true"/>
        </column>
        <column name="name" type="varchar(255)">
            <constraints nullable="false"/>
        </column>
    </createTable>
</changeSet>

import jakarta.persistence.*;

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(unique = true)
    private String email;

    @Column(name = "created_at")
    private LocalDateTime createdAt;

    // Getters/setters
}

EntityManagerFactory emf = Persistence.createEntityManagerFactory("myapp");
EntityManager em = emf.createEntityManager();

// Begin transaction
em.getTransaction().begin();

// Create
User user = new User();
user.setName("Alice");
user.setEmail("alice@example.com");
em.persist(user);

// Read
User found = em.find(User.class, 1L);

// Update
found.setEmail("newemail@example.com");
em.merge(found);

// Delete
em.remove(found);

// Commit transaction
em.getTransaction().commit();

em.close();
emf.close();

TypedQuery<User> query = em.createQuery(
    "SELECT u FROM User u WHERE u.email LIKE :pattern",
    User.class
);
query.setParameter("pattern", "%@example.com");

List<User> users = query.getResultList();

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.3.232</version>
    <scope>test</scope>
</dependency>

@Test
void testUserRepository() {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl("jdbc:h2:mem:test");
    config.setUsername("sa");
    config.setPassword("");

    HikariDataSource dataSource = new HikariDataSource(config);

    // Run migrations
    Flyway.configure()
        .dataSource(dataSource)
        .load()
        .migrate();

    // Test database operations
    UserRepository repo = new UserRepository(dataSource);
    User user = repo.create("Alice", "alice@example.com");

    assertThat(user.getId()).isNotNull();
    assertThat(repo.findById(user.getId())).isPresent();

    dataSource.close();
}

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.20.4</version>
    <scope>test</scope>
</dependency>

import org.testcontainers.containers.PostgreSQLContainer;
import org.junit.jupiter.api.*;

@TestInstance(TestInstance.Lifecycle.PER_CLASS)
class UserRepositoryIntegrationTest {

    private PostgreSQLContainer<?> postgres;
    private HikariDataSource dataSource;

    @BeforeAll
    void setUp() {
        postgres = new PostgreSQLContainer<>("postgres:16")
            .withDatabaseName("test")
            .withUsername("test")
            .withPassword("test");

        postgres.start();

        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(postgres.getJdbcUrl());
        config.setUsername(postgres.getUsername());
        config.setPassword(postgres.getPassword());

        dataSource = new HikariDataSource(config);

        // Run migrations
        Flyway.configure()
            .dataSource(dataSource)
            .load()
            .migrate();
    }

    @AfterAll
    void tearDown() {
        dataSource.close();
        postgres.stop();
    }

    @Test
    void testCreateUser() {
        UserRepository repo = new UserRepository(dataSource);
        User user = repo.create("Alice", "alice@example.com");

        assertThat(user.getId()).isNotNull();
    }
}

try (Connection conn = dataSource.getConnection();
     PreparedStatement stmt = conn.prepareStatement(sql);
     ResultSet rs = stmt.executeQuery()) {
    // Use resources
}  // Automatically closed

BigDecimal balance = rs.getBigDecimal("balance");
if (rs.wasNull()) {
    balance = BigDecimal.ZERO;
}

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);

public class UserRepository {
    private final DataSource dataSource;

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

    public Optional<User> findById(Long id) {
        String sql = "SELECT id, name, email FROM users WHERE id = ?";

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

            stmt.setLong(1, id);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapUser(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error finding user", e);
        }

        return Optional.empty();
    }

    public User create(String name, String email) {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?) RETURNING id";

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

            stmt.setString(1, name);
            stmt.setString(2, email);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    long id = rs.getLong("id");
                    return new User(id, name, email);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error creating user", e);
        }

        throw new RuntimeException("Failed to create user");
    }

    private User mapUser(ResultSet rs) throws SQLException {
        return new User(
            rs.getLong("id"),
            rs.getString("name"),
            rs.getString("email")
        );
    }
}