15.2 SQL Execution: `Statement`, `PreparedStatement`, and `CallableStatement`

Oracle Java SE 17 Developer Certified Professional 1Z0-829

Track your progress as you read

Finished sections get a check mark.

1 Introduction: The 1Z0-829 Exam

About the Java SE 17 Developer (1Z0-829) Exam

2 Chapter 1: Java Fundamentals & Language Basics

1.1 Primitive Types, Literals, Wrapper Classes, and Type Promotion/Casting 1.2 Local Variable Type Inference (`var`) Rules and Scoping 1.3 Operators, Precedence, Evaluation Order, and Short-Circuit Evaluation 1.4 Text Blocks, String Immutability, and StringBuilder APIs

3 Chapter 2: Program Flow & Decision Constructs

2.1 Conditional Branching with if/else and the Ternary Operator 2.2 Switch Statements and Switch Expressions 2.3 Iteration Constructs, Loop Control, and Branching Labels

4 Chapter 3: Object-Oriented Design & Class Mechanics

3.1 Class Declarations, Field/Method Signatures, and Access Modifiers 3.2 Constructors, Overloading, Initialization Order, and Garbage Collection 3.3 Inheritance, Method Overriding, Polymorphism, and `super`/`this` 3.4 Abstract Classes, Static Members, and Object Methods (`equals`, `hashCode`, `toString`)

5 Chapter 4: Advanced OOP: Interfaces, Records & Sealed Types

4.1 Interface Architecture: Default, Static, and Private Methods 4.2 Enumerations: Constants, Constructors, Methods, and Custom Bodies 4.3 Records: Canonical/Compact Constructors, Components, and Immutability 4.4 Sealed Classes and Interfaces: `permits`, Hierarchy Constraints, and Modifiers

6 Chapter 5: Pattern Matching & Nested Types

5.1 Pattern Matching for `instanceof` and Flow Scoping Mechanics 5.2 Nested Types: Member Inner, Static Nested, Local, and Anonymous Classes 5.3 Defensive Programming, Immutability Patterns, and Validation

7 Chapter 6: Generics, Collections & Arrays

6.1 Generic Classes, Interfaces, Methods, and Type Erasure 6.2 Bounded Type Parameters and Wildcards (PECS Rule) 6.3 Collections Framework: List, Set, Queue, and Deque APIs 6.4 The Map Interface, Sorting, Comparable, and Comparator 6.5 Arrays: Declaration, Initialization, Multidimensional Arrays, and the Arrays Utility Class

8 Chapter 7: Functional Programming & Lambda Expressions

7.1 Functional Interfaces and Lambda Syntax Rules 7.2 Standard Functional Interfaces in `java.util.function` 7.3 Method References: Static, Bound, Unbound, and Constructor References

9 Chapter 8: The Stream API

8.1 Stream Lifecycle: Creation, Pipeline Architecture, and Lazy Evaluation 8.2 Intermediate Stream Operations: Filtering, Mapping, Sorting, and Slicing 8.3 Terminal Stream Operations: Reduction, Matching, Finding, and Collecting 8.4 Primitive Streams (`IntStream`, `LongStream`, `DoubleStream`) and Numeric Statistics

10 Chapter 9: Advanced Streams & Collectors

9.1 The Optional Class: Safe Null Handling, Transformation, and Chaining 9.2 The Collectors API: Downstream Reductions, Grouping, and Partitioning 9.3 Parallel Streams: Fork/Join Pool, Spliterators, and Concurrency Pitfalls

11 Chapter 10: Exception Handling

10.1 The Java Exception Hierarchy: Checked vs. Unchecked Exceptions and Errors 10.2 Exception Constructs: `try-catch-finally`, Multi-Catch, and Rethrowing 10.3 Try-With-Resources, `AutoCloseable`, Suppressed Exceptions, and Custom Exceptions

12 Chapter 11: Date/Time API & Localization

11.1 Date and Time Core Types: `LocalDate`, `LocalTime`, `LocalDateTime` 11.2 Timestamps, Intervals, and Durations: `Instant`, `Period`, and `Duration` 11.3 Time Zones, Daylight Saving Time (DST) Offsets, and `ZonedDateTime` 11.4 Internationalization and Localization: `Locale`, `ResourceBundle`, and Formatters 11.5 Formatting and Parsing Messages, Numbers, Percentages, and Localized Dates

13 Chapter 12: Java Platform Module System (JPMS)

12.1 Module Architecture, `module-info.java`, and Module Paths 12.2 Module Directives: `requires`, `requires transitive`, `exports`, and Qualified Exports 12.3 Reflection, Deep Encapsulation, `opens`, and Open Modules 12.4 Services in JPMS (`uses`, `provides...with`), Migration Paths, and Tooling (`jlink`, `jdeps`) 12.5 Compiling and Packaging Code: Non-Modular JARs, Modular JARs, and Migration Strategies

14 Chapter 13: Concurrency & Multithreading

13.1 Thread Fundamentals, `Runnable`, `Callable`, and Thread Lifecycle 13.2 Memory Visibility, `volatile`, Intrinsic Locks, and Synchronization 13.3 The Concurrency Framework: `ExecutorService`, Thread Pools, and `Future` 13.4 Concurrent Collections, Atomic Variables, Explicit Locks, and `CompletableFuture`

15 Chapter 14: Java I/O and NIO.2

14.1 Classical I/O: Byte and Character Streams, Readers, Writers, and Buffering 14.2 Object Serialization, `Serializable`, `transient`, and `serialVersionUID` 14.3 NIO.2 Architecture: `Path`, `Paths`, and Basic `Files` Operations 14.4 Advanced NIO.2: Directory Walking, Streams (`Files.lines`, `walk`), and `WatchService`

16 Chapter 15: Accessing Databases with JDBC

15.1 JDBC Architecture, Drivers, `DriverManager`, and `DataSource` Connections 15.2 SQL Execution: `Statement`, `PreparedStatement`, and `CallableStatement` 15.3 `ResultSet` Navigation, Data Types, and Transaction Management (Commit, Rollback, Savepoints)

17 Chapter 16: Annotations & the Java Logging API

16.1 Standard Annotations: @Override, @FunctionalInterface, @Deprecated, @SuppressWarnings, and @SafeVarargs 16.2 Java Logging API Basics: Logger, Levels, Handlers, and Configuration

15.2 SQL Execution: `Statement`, `PreparedStatement`, and `CallableStatement`

Key Takeaways Explain This Topic AI

The JDBC statement hierarchy consists of three interfaces: Statement (dynamic uncompiled SQL), PreparedStatement (precompiled parameterized SQL), and CallableStatement (stored procedure execution).

PreparedStatement prevents SQL injection by separating SQL syntax from user-supplied literal data using '?' parameter placeholders, while also enabling query plan caching on the database engine.

SQL execution methods have specific return contracts: executeQuery() returns a ResultSet (SELECT only), executeUpdate() returns an int (INSERT/UPDATE/DELETE/DDL), and execute() returns a boolean indicating whether the first result is a ResultSet.

Parameter placeholders in PreparedStatement and CallableStatement use 1-based indexing; attempting to set or retrieve index 0 throws a runtime SQLException.

CallableStatement handles stored procedure output by registering OUT and INOUT parameters before execution using registerOutParameter(index, java.sql.Types).

Last updated: October 2026

SQL Execution: Statement , PreparedStatement , and CallableStatement

Once a database connection is established, the application interacts with the database engine by sending SQL commands. The JDBC API provides three primary statement interfaces organized in an inheritance hierarchy: +-----------------------+

• | java.sql.Statement |
• +-----------------------+
• ^
• |
• +------------------------------+
• | java.sql.PreparedStatement |
• +------------------------------+
• ^
• |
• +------------------------------+
• | java.sql.CallableStatement |
• +------------------------------+

Statement : Created via conn.createStatement() . Used for executing simple, static SQL queries without parameters. Susceptible to SQL injection if used with dynamic string concatenation.

PreparedStatement : Created via conn.prepareStatement(sql) . Extends Statement . Precompiles SQL statements with parameter placeholders ( ? ), providing security against SQL injection and performance gains through database query plan caching.

CallableStatement : Created via conn.prepareCall(sql) . Extends PreparedStatement . Used to invoke database stored procedures and stored functions supporting IN , OUT , and INOUT parameters.

Statement vs. PreparedStatement and SQL Injection

Consider a user login authentication query implemented with a standard Statement : // VULNERABLE IMPLEMENTATION - NEVER USE IN PRODUCTION!

• String user = request.getParameter("user"); // Attacker inputs: admin' --
• String pass = request.getParameter("pass");
• String sql = "SELECT * FROM users WHERE username = '" + user + "' AND password = '" + pass + "'";

• try (Statement stmt = conn.createStatement();
• ResultSet rs = stmt.executeQuery(sql)) {
• if (rs.next()) {

// User successfully authenticated without knowing the password!

• }
• }

Because the raw SQL string is concatenated, the attacker's input alters the logical syntax of the query. The resulting command executes as: SELECT * FROM users WHERE username = 'admin' --' AND password = '...'

The -- comment operator truncates the remainder of the query, bypassing authentication entirely.

The PreparedStatement Defense

PreparedStatement neutralizes SQL injection by transmitting the SQL template and the user values across separate protocol channels or escaping them internally: String sql = "SELECT * FROM users WHERE username = ? AND password = ?";

• try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
• pstmt.setString(1, user); // Bound strictly as literal data
• pstmt.setString(2, pass); // Cannot alter the parsed query tree
• try (ResultSet rs = pstmt.executeQuery()) {
• if (rs.next()) {
• // Safe authentication
• }
• }
• }

When conn.prepareStatement(sql) is invoked, the database engine compiles the SQL string into an executable query plan. When setString() is called, the value is treated strictly as a literal data parameter, making syntax hijacking impossible.

Note

PreparedStatement also yields significant performance advantages in batch processing. When executing the same SQL statement repeatedly with varying arguments, the database reuses the precompiled query plan rather than repeatedly parsing, validating, and optimizing the SQL.

The Three Core Execution Methods

The JDBC statement interfaces provide three distinct execution methods. Choosing the incorrect execution method results in a runtime SQLException .

Execution Method Valid SQL Statements Return Type Contract & Behavior

executeQuery() SELECT queries only ResultSet Returns a tabular ResultSet . Throws SQLException if invoked on DML ( INSERT , UPDATE , DELETE ) or DDL ( CREATE , DROP ).

executeUpdate() DML ( INSERT , UPDATE , DELETE ) or DDL ( CREATE , ALTER , DROP ) int Returns the number of database rows affected by DML, or 0 for DDL statements. Throws SQLException if invoked on a SELECT .

execute() Any SQL statement (dynamic queries or multi-result calls) boolean Returns true if the first result is a ResultSet ; returns false if the first result is an update count or there are no results.

Working with execute()

The generic execute() method is used when the type of SQL statement is unknown at compile time or when executing commands that produce multiple result sets: boolean isResultSet = stmt.execute(sqlString);

• if (isResultSet) {
• try (ResultSet rs = stmt.getResultSet()) {
• while (rs.next()) {
• System.out.println("Data: " + rs.getString(1));
• }
• }
• } else {
• int updateCount = stmt.getUpdateCount();
• System.out.println("Rows modified: " + updateCount);
• }

Warning

The Overloaded Method Exam Trap Notice that Statement defines executeQuery(String sql) , executeUpdate(String sql) , and execute(String sql) . Because PreparedStatement extends Statement , it inherits these methods. However, on a PreparedStatement , you must invoke the zero-argument versions: pstmt.executeQuery() , pstmt.executeUpdate() , and pstmt.execute() . Passing a SQL string to a PreparedStatement execution method (e.g., pstmt.executeQuery(sql) ) overrides the precompiled statement or throws a runtime SQLException on compliant drivers!

Parameter Binding Rules and 1-Based Indexing

In PreparedStatement , parameters are designated with positional question mark placeholders ( ? ). The JDBC API provides strongly typed setter methods for binding values:

• setInt(int parameterIndex, int x)
• setLong(int parameterIndex, long x)
• setDouble(int parameterIndex, double x)
• setString(int parameterIndex, String x)
• setDate(int parameterIndex, java.sql.Date x)
• setNull(int parameterIndex, int sqlType) (e.g., java.sql.Types.VARCHAR )
• The 1-Based Indexing Rule

Unlike standard Java collections and arrays, which are 0-indexed, all JDBC parameter indices are 1-based : String sql = "UPDATE employees SET salary = ?, department = ? WHERE emp_id = ?";

• try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
• pstmt.setDouble(1, 85000.50); // 1st placeholder: salary
• pstmt.setString(2, "ENGINEERING"); // 2nd placeholder: department
• pstmt.setInt(3, 1042); // 3rd placeholder: emp_id

• int rowsAffected = pstmt.executeUpdate();
• System.out.println("Updated rows: " + rowsAffected);
• }

If code attempts to bind index 0 (e.g., pstmt.setInt(0, 1042); ) or an index exceeding the count of placeholders (e.g., pstmt.setInt(4, 1042); ), the driver throws a runtime java.sql.SQLException: Parameter index out of range .

CallableStatement and Stored Procedures

Database stored procedures and functions encapsulate business logic inside the database engine. JDBC accesses them using CallableStatement via standard SQL escape syntax: {call procedure_name(?, ?, ...)}

{? = call function_name(?, ...)}

Stored procedures support three parameter modes:

IN : Supplies input data from Java to the stored procedure.

OUT : Returns output data from the stored procedure to Java.

INOUT : Supplies input data and receives updated output data through the same parameter.

Registering OUT Parameters

Before executing a CallableStatement , any OUT or INOUT parameter must be registered with its corresponding SQL data type from java.sql.Types . After execution, the values are retrieved using getter methods: // Stored procedure signature: calculate_bonus(IN emp_id INT, OUT bonus DOUBLE, INOUT rating VARCHAR)

String callSql = "{call calculate_bonus(?, ?, ?)}";

• try (CallableStatement cstmt = conn.prepareCall(callSql)) {
• // Parameter 1: IN parameter (emp_id)
• cstmt.setInt(1, 1042);

// Parameter 2: OUT parameter (bonus) - Must be registered before execution!

cstmt.registerOutParameter(2, java.sql.Types.DOUBLE);

// Parameter 3: INOUT parameter (rating) - Bound as input AND registered as output!

• cstmt.setString(3, "EXCELLENT");
• cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);

• // Execute the stored procedure
• cstmt.execute();

• // Retrieve the OUT results using 1-based parameter indices
• double bonus = cstmt.getDouble(2);
• String finalRating = cstmt.getString(3);

• System.out.println("Bonus: $" + bonus + ", Final Rating: " + finalRating);
• }

Warning

If you attempt to invoke a getter method (such as cstmt.getDouble(2) ) without first registering the parameter via registerOutParameter() , the JDBC driver throws a SQLException indicating an unregistered output parameter.