Java Database Connectivity (JDBC)

---

    

---

    

Java Database Connectivity (JDBC)
Java Database Connectivity (JDBC) is an API that allows Java applications to interact with relational databases like MySQL, PostgreSQL, SQL Server, and Oracle.

1. Steps to Connect Java with a Database
1️⃣ Load the JDBC Driver
2️⃣ Establish a Connection
3️⃣ Create a Statement
4️⃣ Execute SQL Queries
5️⃣ Process the Results
6️⃣ Close the Connection

2. Setting Up JDBC Environment
2.1 Download and Add JDBC Driver
Each database requires a specific JDBC driver.

MySQL → Download MySQL Connector/J

PostgreSQL → Download PostgreSQL JDBC

SQL Server → Download SQL Server JDBC

👉 Adding JDBC to Java Project:

In Eclipse/IntelliJ, add the JAR file to the project's Build Path.

3. JDBC Example: Connecting to MySQL
3.1 Create a Database and Table
Before running the Java code, create a database and table in MySQL.

CREATE DATABASE TestDB;

USE TestDB;



CREATE TABLE users (

    id INT PRIMARY KEY AUTO_INCREMENT,

    name VARCHAR(50),

    email VARCHAR(50)

);

3.2 Java Code to Connect to MySQL

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;



public class JDBCConnection {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/TestDB"; // Database URL

        String user = "root"; // Database username

        String password = ""; // Database password (leave empty if none)



        try {

            // Load the MySQL JDBC driver

            Class.forName("com.mysql.cj.jdbc.Driver");



            // Establish connection

            Connection conn = DriverManager.getConnection(url, user, password);



            if (conn != null) {

                System.out.println("Connected to the database!");

            }



            // Close the connection

            conn.close();

        } catch (ClassNotFoundException | SQLException e) {

            e.printStackTrace();

        }

    }

}

✅ Output:


Connected to the database!
4. Executing SQL Queries in Java
4.1 Insert Data into Database

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;



public class InsertData {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/TestDB";

        String user = "root";

        String password = "";



        try (Connection conn = DriverManager.getConnection(url, user, password)) {

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

            PreparedStatement pstmt = conn.prepareStatement(sql);



            pstmt.setString(1, "John Doe");

            pstmt.setString(2, "john@example.com");



            int rowsInserted = pstmt.executeUpdate();

            if (rowsInserted > 0) {

                System.out.println("Data inserted successfully!");

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}

✅ Output:


Data inserted successfully!

4.2 Retrieve Data from Database

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;



public class RetrieveData {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/TestDB";

        String user = "root";

        String password = "";



        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            String sql = "SELECT * FROM users";

            PreparedStatement pstmt = conn.prepareStatement(sql);

            ResultSet rs = pstmt.executeQuery();



            while (rs.next()) {

                int id = rs.getInt("id");

                String name = rs.getString("name");

                String email = rs.getString("email");



                System.out.println(id + " | " + name + " | " + email);

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}

✅ Output:


1 | John Doe | john@example.com
4.3 Update Data in Database

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;



public class UpdateData {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/TestDB";

        String user = "root";

        String password = "";



        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            String sql = "UPDATE users SET email = ? WHERE name = ?";

            PreparedStatement pstmt = conn.prepareStatement(sql);



            pstmt.setString(1, "newemail@example.com");

            pstmt.setString(2, "John Doe");



            int rowsUpdated = pstmt.executeUpdate();

            if (rowsUpdated > 0) {

                System.out.println("Data updated successfully!");

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}

✅ Output:


Data updated successfully!
4.4 Delete Data from Database

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;



public class DeleteData {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/TestDB";

        String user = "root";

        String password = "";



        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            String sql = "DELETE FROM users WHERE name = ?";

            PreparedStatement pstmt = conn.prepareStatement(sql);



            pstmt.setString(1, "John Doe");



            int rowsDeleted = pstmt.executeUpdate();

            if (rowsDeleted > 0) {

                System.out.println("Data deleted successfully!");

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}

✅ Output:


Data deleted successfully!

5. JDBC Best Practices
✔ Use try-with-resources to automatically close connections.
✔ Use PreparedStatement instead of Statement to prevent SQL Injection.
✔ Use Connection Pooling (HikariCP, Apache DBCP) for better performance.
✔ Close Connections, Statements, and ResultSets to avoid memory leaks.

6. Summary
✅ JDBC API allows Java applications to interact with relational databases.
✅ Steps: Load driver → Establish connection → Execute SQL queries → Process results.
✅ Operations: Insert, Retrieve, Update, Delete data using PreparedStatement.
✅ Best Practices: Use connection pooling, prevent SQL injection, close resources properly.

---

    Date: 2025-03-28 00:00:00.000000