JDBC Java Data Base Connectivity

 

🧩 Introduction to JDBC (Java Database Connectivity)

🔷 What is JDBC?

JDBC is an API (Application Programming Interface) in Java that enables Java programs to interact with relational databases using SQL.

• It allows developers to perform operations like:

  • Connecting to a database

  • Executing SQL queries

  • Retrieving data

  • Updating and deleting records

JDBC is part of the Java SE platform and is included in the java.sql and javax.sql packages.

📚 JDBC Overview

• JDBC acts as a bridge between a Java application and a database.

• It uses drivers to connect to databases like MySQL, Oracle, PostgreSQL, SQLite, etc.

• It supports CRUD operations (Create, Read, Update, Delete).

🧱 Types of JDBC Drivers

There are four types of JDBC drivers:

Type	Name	Description
Type  1	JDBC-ODBC Bridge            Driver	Translates JDBC to ODBC API (obsolete in Java 8+)
Type 2	Native-API Driver	Converts JDBC calls into native database calls (requires                 native  libraries)
Type 3	Network Protocol                 Driver	Sends JDBC calls to a middleware server that                                 communicates with the DB
Type 4	Thin Driver (Pure Java)	Converts JDBC directly to database protocol (no native code,          most preferred)

Type 4 (Thin Driver) is most commonly used today.

✅ Steps to Develop JDBC Applications

1. Import JDBC packages

2. Register the driver

3. Establish the connection

4. Create a Statement or PreparedStatement

5. Execute SQL queries

6. Process the ResultSet

7. Close the connection

🧩 Common JDBC Components

Component	Description
DriverManager	Manages JDBC drivers and database connections
Connection	Interface to establish a connection to the database
Statement	Executes SQL queries (static SQL)
PreparedStatement	Precompiled SQL statement (dynamic and parameterized)
ResultSet	Represents the result of a query
SQLException	Exception class for handling database access errors

🔌 Connection Establishment

Example: Connecting to MySQL

import java.sql.*;

public class DBConnect {
    public static void main(String[] args) {
        try {
            // 1. Load the driver (optional for newer versions)
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. Create a connection
            Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/studentdb", "root", "password");

            System.out.println("Connected Successfully!");

            // 3. Close the connection
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

---

🧮 SQL Fundamentals with JDBC

1. Creating and Executing Basic SQL Queries

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM students");

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

---

📥 Working with ResultSet

• rs.next(): Moves the cursor to the next row

• rs.getInt(columnName): Gets int from column

• rs.getString(columnIndex): Gets String by index

Example:

ResultSet rs = stmt.executeQuery("SELECT * FROM students");
while (rs.next()) {
    int id = rs.getInt(1);
    String name = rs.getString("name");
    System.out.println(id + " - " + name);
}

---

🛠️ Performing CRUD Operations with JDBC

🔹 CREATE (Insert)

PreparedStatement ps = con.prepareStatement("INSERT INTO students VALUES (?, ?)");
ps.setInt(1, 101);
ps.setString(2, "Alice");
ps.executeUpdate();

🔹 READ (Select)

PreparedStatement ps = con.prepareStatement("SELECT * FROM students");
ResultSet rs = ps.executeQuery();

🔹 UPDATE

PreparedStatement ps = con.prepareStatement("UPDATE students SET name=? WHERE id=?");
ps.setString(1, "Bob");
ps.setInt(2, 101);
ps.executeUpdate();

🔹 DELETE

PreparedStatement ps = con.prepareStatement("DELETE FROM students WHERE id=?");
ps.setInt(1, 101);
ps.executeUpdate();

---

✅ Best Practices

• Use PreparedStatement instead of Statement for security (prevents SQL injection)

• Always close resources: ResultSet, Statement, Connection

• Use try-with-resources to handle automatic closing

try (Connection con = DriverManager.getConnection(...);
     PreparedStatement ps = con.prepareStatement(...)) {
    // do operations
}

---

📦 Tools You Can Use

• MySQL/MariaDB/PostgreSQL as RDBMS

• XAMPP for MySQL setup

• Eclipse/IntelliJ as IDE

• MySQL Connector/J (JDBC driver)