Loading...

CRUD Operations with JDBC

JDBC (Java Database Connectivity) is the standard API in Java for connecting to and interacting with relational databases. It allows Java programs to perform CRUD (Create, Read, Update, Delete) operations on a database.

In this section, we’ll look at how to perform CRUD operations using JDBC.

1. Setting Up JDBC

To connect to a database using JDBC, you need to:

  1. Download and include the JDBC driver for the specific database you’re working with (e.g., MySQL, PostgreSQL, SQLite).
  2. Establish a connection to the database using the JDBC URL, username, and password.
  3. Execute SQL queries using statements (Statement, PreparedStatement, etc.) and retrieve results.

Before starting, make sure to import the required JDBC classes:

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

2. Example Database Setup

We will assume that you have a database named company with a table employees defined as follows:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    department VARCHAR(100)
);

3. Establishing a Connection to the Database

Before performing any CRUD operations, we need to establish a connection to the database using the JDBC driver.

public class JDBCConnection {
    public static Connection connect() {
        String url = "jdbc:mysql://localhost:3306/company";
        String user = "root";
        String password = "password"; // replace with your actual password
        Connection connection = null;

        try {
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to the database.");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;
    }
}

4. CRUD Operations

Create Operation (INSERT)

To insert data into the employees table:

public class CreateEmployee {
    public static void main(String[] args) {
        Connection connection = JDBCConnection.connect();
        String insertSQL = "INSERT INTO employees (name, age, department) VALUES (?, ?, ?)";

        try (PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {
            preparedStatement.setString(1, "John Doe");
            preparedStatement.setInt(2, 30);
            preparedStatement.setString(3, "IT");

            int rowsInserted = preparedStatement.executeUpdate();
            if (rowsInserted > 0) {
                System.out.println("A new employee was inserted successfully.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation:

  • We use PreparedStatement to safely insert data into the database. This prevents SQL injection attacks.
  • The values for name, age, and department are set using setString and setInt.

Read Operation (SELECT)

To fetch data from the employees table:

public class ReadEmployee {
    public static void main(String[] args) {
        Connection connection = JDBCConnection.connect();
        String selectSQL = "SELECT * FROM employees";

        try (PreparedStatement preparedStatement = connection.prepareStatement(selectSQL);
             ResultSet resultSet = preparedStatement.executeQuery()) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String department = resultSet.getString("department");

                System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age + ", Department: " + department);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation:

  • PreparedStatement is used to prepare and execute the SQL query.
  • ResultSet is used to iterate over the result set and retrieve column values.

Update Operation (UPDATE)

To update an existing employee’s details:

public class UpdateEmployee {
    public static void main(String[] args) {
        Connection connection = JDBCConnection.connect();
        String updateSQL = "UPDATE employees SET age = ?, department = ? WHERE name = ?";

        try (PreparedStatement preparedStatement = connection.prepareStatement(updateSQL)) {
            preparedStatement.setInt(1, 31);
            preparedStatement.setString(2, "HR");
            preparedStatement.setString(3, "John Doe");

            int rowsUpdated = preparedStatement.executeUpdate();
            if (rowsUpdated > 0) {
                System.out.println("An existing employee's data was updated successfully.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation:

  • The UPDATE SQL query updates the employee’s age and department where the employee’s name matches “John Doe”.
  • PreparedStatement is used to set the new values for age and department.

Delete Operation (DELETE)

To delete an employee from the employees table:

public class DeleteEmployee {
    public static void main(String[] args) {
        Connection connection = JDBCConnection.connect();
        String deleteSQL = "DELETE FROM employees WHERE name = ?";

        try (PreparedStatement preparedStatement = connection.prepareStatement(deleteSQL)) {
            preparedStatement.setString(1, "John Doe");

            int rowsDeleted = preparedStatement.executeUpdate();
            if (rowsDeleted > 0) {
                System.out.println("An employee was deleted successfully.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation:

  • The DELETE SQL query deletes an employee with the name “John Doe”.
  • PreparedStatement is used to safely pass the name of the employee to be deleted.

5. Full CRUD Example

Below is a full example that integrates all CRUD operations into a single Java program:

import java.sql.*;

public class CRUDExample {
    public static void main(String[] args) {
        // Create operation
        createEmployee("Alice", 25, "IT");

        // Read operation
        readEmployees();

        // Update operation
        updateEmployee("Alice", 26, "HR");

        // Read operation after update
        readEmployees();

        // Delete operation
        deleteEmployee("Alice");

        // Final read operation
        readEmployees();
    }

    public static void createEmployee(String name, int age, String department) {
        String insertSQL = "INSERT INTO employees (name, age, department) VALUES (?, ?, ?)";
        try (Connection connection = JDBCConnection.connect();
             PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {
            
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, age);
            preparedStatement.setString(3, department);
            preparedStatement.executeUpdate();
            System.out.println("Employee created successfully: " + name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void readEmployees() {
        String selectSQL = "SELECT * FROM employees";
        try (Connection connection = JDBCConnection.connect();
             PreparedStatement preparedStatement = connection.prepareStatement(selectSQL);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String department = resultSet.getString("department");
                System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age + ", Department: " + department);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void updateEmployee(String name, int newAge, String newDepartment) {
        String updateSQL = "UPDATE employees SET age = ?, department = ? WHERE name = ?";
        try (Connection connection = JDBCConnection.connect();
             PreparedStatement preparedStatement = connection.prepareStatement(updateSQL)) {
            
            preparedStatement.setInt(1, newAge);
            preparedStatement.setString(2, newDepartment);
            preparedStatement.setString(3, name);
            preparedStatement.executeUpdate();
            System.out.println("Employee updated successfully: " + name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void deleteEmployee(String name) {
        String deleteSQL = "DELETE FROM employees WHERE name = ?";
        try (Connection connection = JDBCConnection.connect();
             PreparedStatement preparedStatement = connection.prepareStatement(deleteSQL)) {
            
            preparedStatement.setString(1, name);
            preparedStatement.executeUpdate();
            System.out.println("Employee deleted successfully: " + name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6. Conclusion

JDBC makes it easy to perform CRUD operations on a relational database from Java. By using PreparedStatement for all operations, we ensure that the code is safe from SQL injection attacks and that it works efficiently with different types of databases.

In the above example, we created a database connection, performed all four CRUD operations (Create, Read, Update, Delete), and handled the results using JDBC. You can expand this example further to include error handling, transaction management, and other advanced features.