JDBC · Lesson 1

Introduction to JDBC

What is JDBC?

JDBC (Java Database Connectivity) is a Java API for connecting to and executing SQL commands against databases. Instead of building raw database connections yourself, JDBC provides a standardized interface that works with any JDBC-compliant database (MySQL, PostgreSQL, Oracle, SQL Server). JDBC is the foundation for database access in Java — modern frameworks like Hibernate and Spring Data build on top of JDBC. Understanding JDBC is essential for learning how databases work in Java applications.

The JDBC Architecture and Driver Types

JDBC uses a driver to communicate with the database. The driver translates JDBC calls into the specific protocol the database understands. There are four JDBC driver types: Type 1 (JDBC-ODBC bridge, deprecated), Type 2 (native code), Type 3 (network), and Type 4 (pure Java, most common). A typical JDBC stack looks like: Application → JDBC API → JDBC Driver → Database. This layered approach lets you switch databases with minimal code changes.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCBasics {
    public static void main(String[] args) {
        // JDBC URL components: driver:protocol://host:port/database
        String url = "jdbc:mysql://localhost:3306/myapp";
        String username = "root";
        String password = "password";
        
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        
        try {
            // Load driver (some drivers auto-register, but explicit is safer)
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // Get connection from DriverManager
            connection = DriverManager.getConnection(url, username, password);
            System.out.println("Connected to database!");
            
            // Create statement for executing SQL
            statement = connection.createStatement();
            
            // Execute query
            resultSet = statement.executeQuery("SELECT id, name FROM users LIMIT 5");
            
            // Process results
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("ID: " + id + ", Name: " + name);
            }
            
        } catch (ClassNotFoundException e) {
            System.err.println("JDBC Driver not found: " + e.getMessage());
        } catch (Exception e) {
            System.err.println("Database error: " + e.getMessage());
        } finally {
            // Always close resources in reverse order
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (Exception e) {
                System.err.println("Error closing resources: " + e.getMessage());
            }
        }
    }
}

Connection Management: Getting and Closing Connections

Connections are expensive resources — they consume memory and database resources. Always close connections when done using them. In production, use a connection pool that reuses connections instead of creating new ones for each operation. Java provides DataSource interface; application servers and libraries like Apache Commons DBCP implement it efficiently.

import java.sql.Connection;
import java.sql.DriverManager;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;

public class ConnectionPooling {
    // Connection pool: reuses connections efficiently
    private static DataSource createDataSource() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/myapp");
        dataSource.setUsername("root");
        dataSource.setPassword("password");
        dataSource.setInitialSize(5);      // Start with 5 connections
        dataSource.setMaxTotal(20);        // Max 20 connections
        dataSource.setMaxIdle(10);         // Keep 10 idle
        return dataSource;
    }
    
    public void example() {
        DataSource dataSource = createDataSource();
        Connection connection = null;
        
        try {
            // Get connection from pool (reused if available)
            connection = dataSource.getConnection();
            // Use connection for queries
            
        } catch (Exception e) {
            System.err.println("Error: " + e.getMessage());
        } finally {
            // Return to pool (don't close, just release)
            try {
                if (connection != null) connection.close();
            } catch (Exception e) {
                System.err.println("Error returning connection: " + e.getMessage());
            }
        }
    }
}

Executing SQL Queries with Statements

A Statement executes SQL queries. There are three types: Statement (basic SQL), PreparedStatement (parameterized queries, prevents SQL injection), and CallableStatement (stored procedures). For most purposes, use PreparedStatement. Always use parameterized queries when the SQL contains user input.

import java.sql.*;

public class QueryExecution {
    public void usingPreparedStatement(Connection connection) {
        String sql = "SELECT id, name, email FROM users WHERE age > ? AND country = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            // Set parameters (safer than string concatenation)
            pstmt.setInt(1, 18);           // ? = 18
            pstmt.setString(2, "USA");     // ? = "USA"
            
            // Execute query
            ResultSet resultSet = pstmt.executeQuery();
            
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name") + " - " + resultSet.getString("email"));
            }
            
        } catch (SQLException e) {
            System.err.println("Query failed: " + e.getMessage());
        }
    }
    
    // BAD: Don't do this! Vulnerable to SQL injection
    public void unsafeQuery(Connection connection, String userAge) {
        String sql = "SELECT * FROM users WHERE age > " + userAge;  // Dangerous!
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            // If userAge is "18 OR 1=1", this will return ALL users!
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Processing Results with ResultSet

A ResultSet represents query results as a table. Use next() to iterate rows, and getInt(), getString(), getDouble(), etc. to extract column values. ResultSet can be scrollable (move forward/backward) or forward-only (more efficient). Most queries use forward-only scrolling.

import java.sql.*;

public class ResultSetExample {
    public void processResults(Connection connection) {
        String sql = "SELECT id, name, email, created_date FROM users ORDER BY created_date DESC";
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            // Forward-only iteration
            while (rs.next()) {
                int id = rs.getInt("id");                    // By column name
                String name = rs.getString(2);               // By index (1-based)
                String email = rs.getString("email");
                java.sql.Date createdDate = rs.getDate("created_date");
                
                System.out.println("ID: " + id + ", Name: " + name);
                System.out.println("Email: " + email);
                System.out.println("Created: " + createdDate);
                System.out.println("---");
            }
            
        } catch (SQLException e) {
            System.err.println("Error processing results: " + e.getMessage());
        }
    }
}

JDBC Component	Purpose	Example
Driver	Implements JDBC for specific database	`com.mysql.cj.jdbc.Driver`
Connection	Link to database	`DriverManager.getConnection(url, user, pass)`
Statement	Executes SQL	`connection.createStatement()`
PreparedStatement	Parameterized SQL (safe)	`connection.prepareStatement("SELECT * FROM users WHERE id = ?")`
ResultSet	Query results as rows/columns	Loop with `next()`, extract with `getInt()`, etc.

Common Mistake: Forgetting to close resources or not using try-with-resources. Unclosed connections leak database resources, eventually exhausting the connection pool and causing failures. Always use try-with-resources: try (Connection conn = ...; Statement stmt = ...) { } which auto-closes resources even if exceptions occur. Also, never concatenate user input into SQL — always use PreparedStatement with parameters.

Best Practices for JDBC

Use PreparedStatement for all parameterized queries (prevents SQL injection)
Use try-with-resources to ensure connections/statements/resultsets close
Use connection pooling in production (DataSource, never DriverManager directly)
Validate all input from users before putting in SQL
Use appropriate data types: java.sql.Date for dates, not String
Handle SQLExceptions appropriately: log, retry, or fail gracefully
Avoid the N+1 query problem: fetch related data in one query, not separate queries
Consider ORMs (Hibernate, JPA) for complex applications; JDBC for simple queries

Real-World Example: User Database Access

A user management application uses JDBC with a connection pool. When loading a user profile, it executes a PreparedStatement with the user ID as a parameter, processes the ResultSet to extract user data, and displays it. When updating a user, it uses PreparedStatement to safely incorporate form data. When creating a new user, it validates the email isn't duplicate by querying first, then inserts the new record. All database access is wrapped in try-with-resources blocks, automatically closing connections after use. The connection pool ensures efficient resource usage even with hundreds of concurrent users.

Quiz Check

Quick Knowledge Test

Loading quiz questions...