java.lang java.io and java.nio java.util java.math java.sql java.time java.text java.lang.annotation java.util.logging java.util.regex

java.sql package

Introduction to java.sql package

JDBC Basics

Connection and DataSource(Demonstration with mysql database)

Statement and PreparedStatement(Demonstration with mysql database)

ResultSet(Demonstration with mysql database)

Batch Processing(Demonstration with mysql database)

Transactions(Demonstration with mysql database)

CallableStatement(Demonstration with mysql database)

Metadata(Demonstration with mysql database)

RowSet(Demonstration with mysql database)

Savepoint(Demonstration with mysql database)

Handling SQL Exceptions

Connecting JDBC Application with Different Databases

Oracle Database

MySQL Database

PostgresSQL Database

SQLite Database

SQLServer Database

H2 Database

MongoDB Database

Practice Programs on JDBC with Oracle

By / 21 August 2026 / 6 minutes of reading

Program 1: Establishing a Connection with Oracle

This program establishes a connection between a Java application and Oracle Database using the Oracle JDBC Thin Driver.

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

public class OracleConnectionExample {

    public static void main(String[] args) {

        String url =
            "jdbc:oracle:thin:@localhost:1521/XEPDB1";

        String username = "system";
        String password = "password";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, username, password);

            System.out.println(
                "Connected to Oracle Database successfully.");

            con.close();

        } catch (SQLException e) {
            System.out.println(
                "Connection failed: " +
                e.getMessage());
        }
    }
}	
Execution and Output:

PS D:\test> javac -classpath ".;D:\test\ojdbc11.jar" OracleConnectionExample.java
PS D:\test> java -classpath ".;D:\test\ojdbc11.jar" OracleConnectionExample
Connected to Oracle Database successfully.Code language: CSS (css)

Program 2: Creating an Employee Table

This program uses the JDBC Statement interface to create an employee table in Oracle Database.

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

public class CreateEmployeeTable {

    public static void main(String[] args) {

        String url =
            "jdbc:oracle:thin:@localhost:1521/XEPDB1";

        String username = "system";
        String password = "password";

        String sql =
            "CREATE TABLE employee (" +
            "employee_id NUMBER PRIMARY KEY, " +
            "employee_name VARCHAR2(100), " +
            "department VARCHAR2(50), " +
            "salary NUMBER(10,2))";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, username, password);

            Statement stmt =
                con.createStatement();

            stmt.executeUpdate(sql);

            System.out.println(
                "Employee table created successfully.");

            stmt.close();
            con.close();

        } catch (SQLException e) {
            System.out.println(
                "Error: " + e.getMessage());
        }
    }
}
Execution and Output:

PS D:\test> javac -classpath ".;D:\test\ojdbc11.jar" CreateEmployeeTable.java
PS D:\test> java -classpath ".;D:\test\ojdbc11.jar" CreateEmployeeTable
Employee table created successfully.Code language: CSS (css)

Program 3: Inserting Records Using PreparedStatement

This program inserts employee records into the Oracle table using PreparedStatement. Parameterized SQL makes the program more flexible and avoids constructing SQL statements by string concatenation.

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

public class OraclePreparedStatementExample {

    public static void main(String[] args) {

        String url =
            "jdbc:oracle:thin:@localhost:1521/XEPDB1";

        String username = "system";
        String password = "password";

        String sql =
            "INSERT INTO employee " +
            "(employee_id, employee_name, department, salary) " +
            "VALUES (?, ?, ?, ?)";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, username, password);

            PreparedStatement ps =
                con.prepareStatement(sql);

            ps.setInt(1, 101);
            ps.setString(2, "Ravi");
            ps.setString(3, "IT");
            ps.setDouble(4, 50000);

            ps.executeUpdate();

            System.out.println(
                "Employee inserted successfully.");

            ps.close();
            con.close();

        } catch (SQLException e) {
            System.out.println(
                "Error: " + e.getMessage());
        }
    }
}
Execution and Output:

PS D:\test> javac -classpath ".;D:\test\ojdbc11.jar" OraclePreparedStatementExample.java
PS D:\test> java -classpath ".;D:\test\ojdbc11.jar" OraclePreparedStatementExample
Employee inserted successfully.Code language: CSS (css)

Program 4: Retrieving Records Using ResultSet

This program retrieves employee records from Oracle Database using Statement and ResultSet.

import java.sql.*;

public class OracleResultSetExample {

    public static void main(String[] args) {

        String url =
            "jdbc:oracle:thin:@localhost:1521/XEPDB1";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "system", "password");

            Statement stmt =
                con.createStatement();

            ResultSet rs =
                stmt.executeQuery(
                    "SELECT * FROM employee");

            System.out.println(
                "Employee Details");
            System.out.println(
                "-------------------------");

            while (rs.next()) {

                System.out.println(
                    rs.getInt("employee_id") + "  " +
                    rs.getString("employee_name") + "  " +
                    rs.getString("department") + "  " +
                    rs.getDouble("salary"));
            }

            rs.close();
            stmt.close();
            con.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
Execution and Output:

PS D:\test> javac -classpath ".;D:\test\ojdbc11.jar" OracleResultSetExample.java
PS D:\test> java -classpath ".;D:\test\ojdbc11.jar" OracleResultSetExample
Employee Details
-------------------------
101  Ravi  IT  50000.0Code language: CSS (css)

Program 5: Updating Records Using PreparedStatement

This program updates the salary of an employee using a parameterized SQL query.

import java.sql.*;

public class OracleUpdateExample {

    public static void main(String[] args) {

        String url =
            "jdbc:oracle:thin:@localhost:1521/XEPDB1";

        String sql =
            "UPDATE employee " +
            "SET salary = ? " +
            "WHERE employee_id = ?";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "system", "password");

            PreparedStatement ps =
                con.prepareStatement(sql);

            ps.setDouble(1, 60000);
            ps.setInt(2, 101);

            int rows =
                ps.executeUpdate();

            System.out.println(
                rows + " record updated.");

            ps.close();
            con.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
Execution and Output:

PS D:\test> javac -classpath ".;D:\test\ojdbc11.jar" OracleUpdateExample.java
PS D:\test> java -classpath ".;D:\test\ojdbc11.jar" OracleUpdateExample
1 record updated.Code language: CSS (css)

Program 6: Deleting Records Using PreparedStatement

This program deletes an employee record from the Oracle database using PreparedStatement.

import java.sql.*;

public class OracleDeleteExample {

    public static void main(String[] args) {

        String url =
            "jdbc:oracle:thin:@localhost:1521/XEPDB1";

        String sql =
            "DELETE FROM employee " +
            "WHERE employee_id = ?";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "system", "password");

            PreparedStatement ps =
                con.prepareStatement(sql);

            ps.setInt(1, 101);

            int rows =
                ps.executeUpdate();

            System.out.println(
                rows + " record deleted.");

            ps.close();
            con.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
Execution and Output:

PS D:\test> javac -classpath ".;D:\test\ojdbc11.jar" OracleDeleteExample.java
PS D:\test> java -classpath ".;D:\test\ojdbc11.jar" OracleDeleteExample
1 record deleted.Code language: CSS (css)

Program 7: Performing Batch Insert Using PreparedStatement

This program inserts multiple employee records into Oracle using JDBC batch processing. addBatch() collects multiple operations and executeBatch() executes them together.

import java.sql.*;

public class OracleBatchExample {

    public static void main(String[] args) {

        String url =
            "jdbc:oracle:thin:@localhost:1521/XEPDB1";

        String sql =
            "INSERT INTO employee " +
            "(employee_id, employee_name, department, salary) " +
            "VALUES (?, ?, ?, ?)";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "system", "password");

            PreparedStatement ps =
                con.prepareStatement(sql);

            ps.setInt(1, 201);
            ps.setString(2, "Arun");
            ps.setString(3, "IT");
            ps.setDouble(4, 45000);
            ps.addBatch();

            ps.setInt(1, 202);
            ps.setString(2, "Priya");
            ps.setString(3, "HR");
            ps.setDouble(4, 42000);
            ps.addBatch();

            ps.setInt(1, 203);
            ps.setString(2, "Kiran");
            ps.setString(3, "Finance");
            ps.setDouble(4, 48000);
            ps.addBatch();

            int[] result =
                ps.executeBatch();

            System.out.println(
                result.length +
                " records processed in batch.");

            ps.close();
            con.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
Execution and Output:

PS D:\test> javac -classpath ".;D:\test\ojdbc11.jar" OracleBatchExample.java
PS D:\test> java -classpath ".;D:\test\ojdbc11.jar" OracleBatchExample
3 records processed in batch.Code language: CSS (css)

Program 8: Using Transactions with Commit and Rollback

This program performs multiple operations inside a transaction. If all operations are successful, commit() saves them permanently. If an error occurs, rollback() cancels the transaction.

import java.sql.*;

public class OracleTransactionExample {

    public static void main(String[] args) {

        String url =
            "jdbc:oracle:thin:@localhost:1521/XEPDB1";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "system", "password");

            con.setAutoCommit(false);

            PreparedStatement ps =
                con.prepareStatement(
                    "UPDATE employee " +
                    "SET salary = salary + ? " +
                    "WHERE employee_id = ?");

            ps.setDouble(1, 5000);
            ps.setInt(2, 201);
            ps.executeUpdate();

            ps.setDouble(1, 3000);
            ps.setInt(2, 202);
            ps.executeUpdate();

            con.commit();

            System.out.println(
                "Transaction committed successfully.");

            ps.close();
            con.close();

        } catch (SQLException e) {

            System.out.println(
                "Transaction failed.");

            e.printStackTrace();
        }
    }
}
Execution and Output:

PS D:\test> javac -classpath ".;D:\test\ojdbc11.jar" OracleTransactionExample.java
PS D:\test> java -classpath ".;D:\test\ojdbc11.jar" OracleTransactionExample
Transaction committed successfully.Code language: CSS (css)

Program 9: Using Savepoint in Oracle Transaction

This program demonstrates how a savepoint can be created during a transaction. The transaction can roll back only the operations performed after the savepoint.

import java.sql.*;

public class OracleSavepointExample {

    public static void main(String[] args) {

        String url =
            "jdbc:oracle:thin:@localhost:1521/XEPDB1";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "system", "password");

            con.setAutoCommit(false);

            Statement stmt =
                con.createStatement();

            stmt.executeUpdate(
                "UPDATE employee " +
                "SET salary = salary + 2000 " +
                "WHERE employee_id = 201");

            System.out.println(
                "First update completed.");

            Savepoint savepoint =
                con.setSavepoint("AfterFirstUpdate");

            System.out.println(
                "Savepoint created.");

            stmt.executeUpdate(
                "UPDATE employee " +
                "SET salary = salary + 5000 " +
                "WHERE employee_id = 202");

            System.out.println(
                "Second update completed.");

            con.rollback(savepoint);

            System.out.println(
                "Rolled back to savepoint.");

            con.commit();

            System.out.println(
                "Transaction committed.");

            stmt.close();
            con.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
Execution and Output:

PS D:\test> javac -classpath ".;D:\test\ojdbc11.jar" OracleSavepointExample.java
PS D:\test> java -classpath ".;D:\test\ojdbc11.jar" OracleSavepointExample
First update completed.
Savepoint created.
Second update completed.
Rolled back to savepoint.
Transaction committed.Code language: CSS (css)

Program 10: Calling an Oracle Stored Procedure Using CallableStatement

This program demonstrates how Java can call a stored procedure in Oracle using CallableStatement.

Create the Oracle Procedure

Run this SQL in Oracle:

CREATE OR REPLACE PROCEDURE get_employee_name (
    p_employee_id IN NUMBER,
    p_employee_name OUT VARCHAR2
)
AS
BEGIN
    SELECT employee_name
    INTO p_employee_name
    FROM employee
    WHERE employee_id = p_employee_id;
END;
/Code language: JavaScript (javascript)
import java.sql.*;

public class OracleCallableStatementExample {

    public static void main(String[] args) {

        String url =
            "jdbc:oracle:thin:@localhost:1521/XEPDB1";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "system", "password");

            CallableStatement cs =
                con.prepareCall(
                    "{call get_employee_name(?, ?)}");

            // IN parameter
            cs.setInt(1, 201);

            // OUT parameter
            cs.registerOutParameter(
                2, Types.VARCHAR);

            // Execute procedure
            cs.execute();

            // Retrieve OUT parameter
            String employeeName =
                cs.getString(2);

            System.out.println(
                "Employee Name: " +
                employeeName);

            cs.close();
            con.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
Execution and Output:

PS D:\test> javac -classpath ".;D:\test\ojdbc11.jar" OracleCallableStatementExample.java
PS D:\test> java -classpath ".;D:\test\ojdbc11.jar" OracleCallableStatementExample
Employee Name: ArunCode language: CSS (css)
PREVIOUS
NEXT

Related Posts

Scroll to Top