Oracle CRUD Tutorials in Java: A Step-by-Step Guide

Oracle Database is a powerful, enterprise-level relational database management system (RDBMS). In this tutorial, we will guide you through the process of performing CRUD (Create, Read, Update, Delete) operations in Oracle using Java. We’ll provide step-by-step instructions and practical examples with detailed explanations.

Prerequisites

Before you begin, make sure you have the following prerequisites in place:

Java Development Kit (JDK): Ensure you have Java installed on your system. You can download it from the official Oracle website or use an open-source alternative like OpenJDK.
Integrated Development Environment (IDE): Choose an IDE like Eclipse, IntelliJ IDEA, or NetBeans to write and manage your Java code.
Oracle Database: You need to have Oracle Database installed and running. You can download Oracle Database Express Edition (XE) for free from the official Oracle Database XE website for development purposes.
Oracle JDBC Driver: You’ll need the Oracle JDBC driver to connect to the Oracle Database from your Java application. Download the Oracle JDBC driver JAR file and include it in your project’s classpath.

Step 1: Create an Oracle Database

Let’s start by creating an Oracle database where we will perform CRUD operations. This example uses Oracle Database XE:

Install Oracle Database XE by following the installation instructions provided on the Oracle website.
During installation, set the system and sys passwords. Make a note of these passwords as they will be used to connect to the database.
Open SQL*Plus or any SQL client for Oracle and connect using the system user:
```
sqlplus system/your_password_here
```

Create a new table in the database. For example, let’s create a table called employees:

CREATE TABLE employees (
    id NUMBER(5) PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    age NUMBER(3)
);

Step 2: Set Up a Java Project

Open your preferred IDE and create a new Java project.
Add the Oracle JDBC driver dependency to your project’s build file (e.g., Maven’s pom.xml or Gradle’s build.gradle).

For Maven, add the following dependency (replace ${oracle.version} with the specific version of the Oracle JDBC driver you downloaded):
```
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>${oracle.version}</version>
</dependency>
```
For Gradle, add the following dependency:
```
implementation group: 'com.oracle.database.jdbc', name: 'ojdbc8', version: '${oracle.version}'
```

Step 3: Perform CRUD Operations

Create Operation (Insert)

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

public class OracleCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "system";
        String password = "your_system_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String insertQuery = "INSERT INTO employees (id, first_name, last_name, age) VALUES (?, ?, ?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
            preparedStatement.setInt(1, 1);
            preparedStatement.setString(2, "John");
            preparedStatement.setString(3, "Doe");
            preparedStatement.setInt(4, 30);
            preparedStatement.executeUpdate();

            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code inserts a new employee record into the employees table in the Oracle Database.

Read Operation (Select)

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

public class OracleCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "system";
        String password = "your_system_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String selectQuery = "SELECT * FROM employees";
            PreparedStatement preparedStatement = connection.prepareStatement(selectQuery);
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                int age = resultSet.getInt("age");

                System.out.println("ID: " + id + ", Name: " + firstName + " " + lastName + ", Age: " + age);
            }

            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code retrieves and prints all employee records from the employees table.

Update Operation (Update)

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

public class OracleCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "system";
        String password = "your_system_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String updateQuery = "UPDATE employees SET age = ? WHERE first_name = ? AND last_name = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(updateQuery);
            preparedStatement.setInt(1, 31);
            preparedStatement.setString(2, "John");
            preparedStatement.setString(3, "Doe");
            preparedStatement.executeUpdate();

            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code updates the age of the employee named “John Doe” in the employees table.

Delete Operation (Delete)

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

public class OracleCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "system";
        String password = "your_system_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String deleteQuery = "DELETE FROM employees WHERE first_name = ? AND last_name =