oracle__skills/db/appdev/java-oracle-jdbc/sql.md

JDBC SQL and PL/SQL

Overview

Use this skill when executing SQL or PL/SQL from Java with Oracle JDBC. It covers bind variables, named binds, DML, batching, stored procedures, functions, OUT parameters, and REF CURSOR results.

For connection URL setup, start with JDBC Connections.

Bind Variables

Always use PreparedStatement with ? placeholders for user values. Do not concatenate user input into SQL strings.

String sql = "SELECT last_name, salary FROM employees WHERE department_id = ? AND salary > ?";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setInt(1, 60);
    pstmt.setDouble(2, 5000.0);

    try (ResultSet rs = pstmt.executeQuery()) {
        while (rs.next()) {
            System.out.printf("%s: %.2f%n",
                    rs.getString("last_name"),
                    rs.getDouble("salary"));
        }
    }
}

Named Binds

Oracle JDBC supports named binds through OraclePreparedStatement.

import oracle.jdbc.OraclePreparedStatement;

String sql = "SELECT last_name FROM employees WHERE employee_id = :id";

try (OraclePreparedStatement pstmt =
        (OraclePreparedStatement) conn.prepareStatement(sql)) {
    pstmt.setIntAtName("id", 100);

    try (ResultSet rs = pstmt.executeQuery()) {
        if (rs.next()) {
            System.out.println(rs.getString(1));
        }
    }
}

DML

Manage transaction boundaries explicitly for DML-heavy code.

conn.setAutoCommit(false);

String sql = "UPDATE employees SET salary = ? WHERE employee_id = ?";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setDouble(1, 9500.0);
    pstmt.setInt(2, 100);

    int rowsUpdated = pstmt.executeUpdate();
    conn.commit();

    System.out.println("Rows updated: " + rowsUpdated);
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

Batch Inserts

Use addBatch and executeBatch for bulk inserts or updates.

conn.setAutoCommit(false);

String sql = "INSERT INTO employees (employee_id, last_name, department_id) VALUES (?, ?, ?)";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    int[][] data = {{201, 60}, {202, 20}, {203, 10}};
    String[] names = {"Alice", "Bob", "Carol"};

    for (int i = 0; i < data.length; i++) {
        pstmt.setInt(1, data[i][0]);
        pstmt.setString(2, names[i]);
        pstmt.setInt(3, data[i][1]);
        pstmt.addBatch();
    }

    int[] results = pstmt.executeBatch();
    conn.commit();
}

Fetch Size

For large result sets, tune fetch size to reduce round trips.

try (PreparedStatement pstmt = conn.prepareStatement(
        "SELECT employee_id, last_name FROM employees")) {
    pstmt.setFetchSize(1000);

    try (ResultSet rs = pstmt.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getInt("employee_id"));
        }
    }
}

Stored Procedure

try (CallableStatement cstmt = conn.prepareCall("{call hr.update_salary(?, ?)}")) {
    cstmt.setInt(1, 100);
    cstmt.setDouble(2, 9500.0);
    cstmt.execute();
    conn.commit();
}

Function with Return Value

try (CallableStatement cstmt = conn.prepareCall("{? = call hr.get_employee_count(?)}")) {
    cstmt.registerOutParameter(1, Types.INTEGER);
    cstmt.setInt(2, 60);
    cstmt.execute();

    int count = cstmt.getInt(1);
    System.out.println("Count: " + count);
}

OUT Parameters

try (CallableStatement cstmt = conn.prepareCall(
        "{call hr.get_employee(?, ?, ?)}")) {
    cstmt.setInt(1, 100);
    cstmt.registerOutParameter(2, Types.VARCHAR);
    cstmt.registerOutParameter(3, Types.NUMERIC);
    cstmt.execute();

    System.out.printf("%s: %.2f%n",
            cstmt.getString(2),
            cstmt.getDouble(3));
}

REF CURSOR

import oracle.jdbc.OracleTypes;

try (CallableStatement cstmt = conn.prepareCall(
        "{call hr.get_dept_employees(?, ?)}")) {
    cstmt.setInt(1, 60);
    cstmt.registerOutParameter(2, OracleTypes.CURSOR);
    cstmt.execute();

    try (ResultSet rs = (ResultSet) cstmt.getObject(2)) {
        while (rs.next()) {
            System.out.println(rs.getString("last_name"));
        }
    }
}

Dynamic SQL Safety

Bind variables protect values, not SQL identifiers. If user input controls table names, column names, sort directions, or other SQL structure, validate it against a whitelist before building the SQL string.

if (!ALLOWED_TABLES.contains(userInputTable)) {
    throw new IllegalArgumentException("Unsupported table");
}

String sql = "SELECT * FROM " + userInputTable + " WHERE employee_id = ?";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setInt(1, employeeId);
    try (ResultSet rs = pstmt.executeQuery()) {
        // use rs
    }
}

Best Practices

• Use PreparedStatement for SQL with values.
• Use try-with-resources for connections, statements, and result sets.
• Disable autoCommit for DML-heavy code and commit intentionally.
• Roll back failed transactions before returning the connection to a pool.
• Use addBatch and executeBatch for bulk operations.
• Use typed getters such as getString, getInt, and getTimestamp when the expected type is known.
• Set fetchSize for large query result sets.

Common Mistakes

Mistake	Problem	Fix
Statement with string concatenation	SQL injection	Use PreparedStatement
Not closing ResultSet or Statement	Connection and cursor leaks	Use try-with-resources
autoCommit=true in batch jobs	Commits per row and slows the job	Use setAutoCommit(false) and batch commit
getObject() everywhere	Type mismatch surprises	Use typed getters where practical
Not setting fetchSize	Excessive round trips on large results	Use stmt.setFetchSize(...)

Oracle Version Notes (19c vs 26ai)

• Oracle 23ai and 26ai JSON Relational Duality Views are queryable through standard JDBC.
• The 23ai and 26ai VECTOR data type is supported by the 23.x/26ai JDBC driver line.
• Keep SQL examples compatible with the target database version, especially when using newer data types or JSON features.

Related Skills

• JDBC Connections
• JDBC Pooling and Production
• SQL Injection Avoidance
• Transaction Management
• Java Oracle JDBC Overview

Sources

• Oracle AI Database JDBC Developer's Guide 26ai
• Oracle JDBC Downloads