Java JDBC

JDBC (Java Database Connectivity) is a Java API that enables Java programs to connect to and interact with relational databases — such as MySQL, PostgreSQL, Oracle, or SQLite. Through JDBC, programs can execute SQL queries, insert, update, delete, and retrieve data stored in a database.

How JDBC Works

JDBC acts as a bridge between a Java application and a database. The application sends SQL commands through JDBC, and the database processes them and returns results.

Java Application
      ↓
JDBC API (java.sql)
      ↓
JDBC Driver (specific to the database vendor)
      ↓
Database (MySQL, PostgreSQL, etc.)

Core JDBC Interfaces

JDBC Setup Steps

1. Add the JDBC driver for the target database to the project (e.g., MySQL Connector/J).
2. Load the driver (optional in modern Java, but good to know).
3. Establish a connection using DriverManager.getConnection().
4. Create a Statement or PreparedStatement to run SQL.
5. Execute the query and process the ResultSet.
6. Close the resources to free memory and connections.

Connection URL Format (MySQL)

jdbc:mysql://hostname:port/databaseName

Example:

jdbc:mysql://localhost:3306/schooldb

Example Database – students Table

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    marks INT
);

Step 1 – Connecting to the Database

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnect {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/schooldb";
        String user = "root";
        String password = "yourpassword";

        try {
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to database successfully.");
            conn.close();
        } catch (SQLException e) {
            System.out.println("Connection failed: " + e.getMessage());
        }
    }
}

Step 2 – Inserting Data (Statement)

import java.sql.*;

public class InsertDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/schooldb";

        try (Connection conn = DriverManager.getConnection(url, "root", "yourpassword");
             Statement stmt = conn.createStatement()) {

            String sql = "INSERT INTO students (name, marks) VALUES ('Alice', 92)";
            int rowsAffected = stmt.executeUpdate(sql);
            System.out.println("Rows inserted: " + rowsAffected);

        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

Step 3 – Reading Data (SELECT Query)

import java.sql.*;

public class SelectDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/schooldb";

        try (Connection conn = DriverManager.getConnection(url, "root", "yourpassword");
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM students")) {

            System.out.println("ID | Name    | Marks");
            System.out.println("---|---------|------");

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int marks = rs.getInt("marks");
                System.out.printf("%-3d| %-8s| %d%n", id, name, marks);
            }

        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

Sample Output:

ID | Name    | Marks
---|---------|------
1  | Alice   | 92

Step 4 – Using PreparedStatement (Recommended)

PreparedStatement is preferred over Statement for queries with user input. It prevents SQL Injection attacks and improves performance when the same query is executed multiple times.

import java.sql.*;

public class PreparedStmtDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/schooldb";

        try (Connection conn = DriverManager.getConnection(url, "root", "yourpassword")) {

            // Insert using PreparedStatement
            String insertSQL = "INSERT INTO students (name, marks) VALUES (?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(insertSQL);

            pstmt.setString(1, "Bob");
            pstmt.setInt(2, 85);
            pstmt.executeUpdate();

            pstmt.setString(1, "Carol");
            pstmt.setInt(2, 78);
            pstmt.executeUpdate();

            System.out.println("Records inserted successfully.");
            pstmt.close();

        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

Step 5 – Updating Data

String updateSQL = "UPDATE students SET marks = ? WHERE name = ?";
PreparedStatement pstmt = conn.prepareStatement(updateSQL);
pstmt.setInt(1, 95);
pstmt.setString(2, "Alice");
int updated = pstmt.executeUpdate();
System.out.println("Updated rows: " + updated);

Step 6 – Deleting Data

String deleteSQL = "DELETE FROM students WHERE name = ?";
PreparedStatement pstmt = conn.prepareStatement(deleteSQL);
pstmt.setString(1, "Carol");
pstmt.executeUpdate();
System.out.println("Record deleted.");

Handling Transactions

By default, JDBC auto-commits every query. For operations that must succeed or fail together (like a bank transfer), auto-commit should be disabled and transactions managed manually.

try {
    conn.setAutoCommit(false);   // disable auto-commit

    // Deduct from Account A
    pstmt1.executeUpdate();

    // Add to Account B
    pstmt2.executeUpdate();

    conn.commit();   // both succeed – commit
    System.out.println("Transaction successful.");

} catch (SQLException e) {
    conn.rollback();   // one failed – undo all
    System.out.println("Transaction rolled back: " + e.getMessage());
}

Closing Resources

Always close JDBC resources (ResultSet, Statement, Connection) to prevent memory leaks. Use try-with-resources for automatic closing.

try (Connection conn = DriverManager.getConnection(url, user, pass);
     PreparedStatement pstmt = conn.prepareStatement(sql);
     ResultSet rs = pstmt.executeQuery()) {

    while (rs.next()) {
        // process results
    }
} // all resources auto-closed here

Summary

• JDBC allows Java programs to connect to and interact with relational databases.
• Core components: DriverManager, Connection, Statement, PreparedStatement, ResultSet.
• Use DriverManager.getConnection() to establish a database connection.
• PreparedStatement is preferred over Statement for security (prevents SQL injection) and performance.
• Use transactions (commit() and rollback()) for operations that must be atomic.
• Always close resources using try-with-resources or explicit close() calls.