• JDBC is an API that allows Java applications to connect and work with databases (MySQL, Oracle, PostgreSQL).
• JDBC is available in java.sql package (Connection, Statement, PreparedStatement, ResultSet).
• Common tasks: connect DB, execute SQL, read ResultSet, perform CRUD.

1) JDBC Architecture

• Java Application → uses JDBC API.
• JDBC Driver → converts Java calls to DB-specific calls.
• Database → executes SQL and returns results.
• Main JDBC steps:
  • Load driver (optional in modern JDBC)
  • Create Connection
  • Create Statement / PreparedStatement
  • Execute query/update
  • Process ResultSet
  • Close resources

2) Connecting Java with MySQL

• Need MySQL JDBC Driver: mysql-connector-j.
• URL format: jdbc:mysql://host:port/dbname
• Example: jdbc:mysql://localhost:3306/school_db

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

class DbConnectDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/school_db";
        String user = "root";
        String pass = "";

        try (Connection con = DriverManager.getConnection(url, user, pass)) {
            System.out.println("Connected to MySQL successfully!");
        } catch (Exception e) {
            System.out.println("Connection error: " + e.getMessage());
        }
    }
}

3) Statement vs PreparedStatement

• Statement:
  • Used for simple SQL queries.
  • Less secure (risk of SQL injection).
• PreparedStatement:
  • Uses placeholders ? and pre-compiles SQL.
  • More secure + faster for repeated queries.

import java.sql.*;

class StatementDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/school_db";
        String user = "root";
        String pass = "";

        try (Connection con = DriverManager.getConnection(url, user, pass);
             Statement st = con.createStatement()) {

            ResultSet rs = st.executeQuery("SELECT id, name FROM students LIMIT 5");
            while (rs.next()) {
                System.out.println(rs.getInt("id") + " " + rs.getString("name"));
            }

        } catch (Exception e) {
            System.out.println("SQL error: " + e.getMessage());
        }
    }
}

import java.sql.*;

class PreparedStatementDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/school_db";
        String user = "root";
        String pass = "";

        try (Connection con = DriverManager.getConnection(url, user, pass)) {

            String sql = "SELECT id, name FROM students WHERE id = ?";
            PreparedStatement ps = con.prepareStatement(sql);

            ps.setInt(1, 1); // set value for ?

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

            rs.close();
            ps.close();

        } catch (Exception e) {
            System.out.println("SQL error: " + e.getMessage());
        }
    }
}

4) ResultSet Handling

• ResultSet holds data returned by SELECT query.
• Methods: next(), getInt(), getString(), getDouble(), etc.
• Column access by index or column name.

import java.sql.*;

class ResultSetDemo {
    public static void main(String[] args) {
        try (Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/school_db", "root", "")) {

            PreparedStatement ps = con.prepareStatement(
                    "SELECT id, name, email FROM students LIMIT 3");

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                int id = rs.getInt(1);          // by index
                String name = rs.getString(2);
                String email = rs.getString("email"); // by column name
                System.out.println(id + " | " + name + " | " + email);
            }

            rs.close();
            ps.close();

        } catch (Exception e) {
            System.out.println("ResultSet error: " + e.getMessage());
        }
    }
}

5) CRUD Operations (Create, Read, Update, Delete)

5.1) CREATE (INSERT)

• Use executeUpdate() for INSERT/UPDATE/DELETE (returns affected rows).

import java.sql.*;

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

        try (Connection con = DriverManager.getConnection(url, user, pass)) {

            String sql = "INSERT INTO students(name, email) VALUES (?, ?)";
            PreparedStatement ps = con.prepareStatement(sql);

            ps.setString(1, "Sourav");
            ps.setString(2, "souravshu562@gmail.com");

            int rows = ps.executeUpdate();
            System.out.println("Inserted rows: " + rows);

            ps.close();

        } catch (Exception e) {
            System.out.println("Insert error: " + e.getMessage());
        }
    }
}

5.2) READ (SELECT)

• Use executeQuery() for SELECT.

import java.sql.*;

class SelectDemo {
    public static void main(String[] args) {
        try (Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/school_db", "root", "")) {

            PreparedStatement ps = con.prepareStatement(
                    "SELECT id, name FROM students WHERE email = ?");

            ps.setString(1, "souravshu562@gmail.com");

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

            rs.close();
            ps.close();

        } catch (Exception e) {
            System.out.println("Select error: " + e.getMessage());
        }
    }
}

5.3) UPDATE

• Update records using WHERE condition.

import java.sql.*;

class UpdateDemo {
    public static void main(String[] args) {
        try (Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/school_db", "root", "")) {

            PreparedStatement ps = con.prepareStatement(
                    "UPDATE students SET name = ? WHERE email = ?");

            ps.setString(1, "Sourav Sahu");
            ps.setString(2, "souravshu562@gmail.com");

            int rows = ps.executeUpdate();
            System.out.println("Updated rows: " + rows);

            ps.close();

        } catch (Exception e) {
            System.out.println("Update error: " + e.getMessage());
        }
    }
}

5.4) DELETE

• Delete record carefully using WHERE condition.

import java.sql.*;

class DeleteDemo {
    public static void main(String[] args) {
        try (Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/school_db", "root", "")) {

            PreparedStatement ps = con.prepareStatement(
                    "DELETE FROM students WHERE email = ?");

            ps.setString(1, "souravshu562@gmail.com");

            int rows = ps.executeUpdate();
            System.out.println("Deleted rows: " + rows);

            ps.close();

        } catch (Exception e) {
            System.out.println("Delete error: " + e.getMessage());
        }
    }
}

6) SQL Exception Handling (SQLException)

• SQLException provides detailed DB error info.
• Useful methods: getMessage(), getErrorCode(), getSQLState().

import java.sql.*;

class SqlExceptionDemo {
    public static void main(String[] args) {
        try (Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/school_db", "root", "")) {

            Statement st = con.createStatement();
            st.executeQuery("SELECT * FROM table_not_exists");

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("Error Code: " + e.getErrorCode());
            System.out.println("SQL State: " + e.getSQLState());
        }
    }
}

7) Quick Notes

• Prefer PreparedStatement for security (prevents SQL injection).
• Use try-with-resources to auto-close Connection/Statement/ResultSet.
• executeQuery() → SELECT, executeUpdate() → INSERT/UPDATE/DELETE.
• Handle SQLException properly for debugging and safe error messages.