Types of Statements in JDBC

In JDBC (Java Database Connectivity), Statement objects are used to send SQL commands to a database and process the results. JDBC provides various types of statements to execute SQL queries efficiently, tailored to the application’s specific needs.

There are three main types of statements in JDBC:

1. Statement

A Statement object is used for general-purpose access to databases and is useful for executing static SQL statements at runtime.

Syntax:

Statement statement = connection.createStatement();

Execution Methods

• execute(String sql): Executes any SQL (SELECT, INSERT, UPDATE, DELETE). Returns true if a ResultSet is returned.
• executeUpdate(String sql): Executes DML (INSERT, UPDATE, DELETE). Returns number of rows affected.
• executeQuery(String sql): Executes SELECT queries. Returns a ResultSet.

Example: Java Program illustrating Statement in JDBC

Output: Name and age are as shown for random inputs.

👁 Output of Create Statement

2. Prepared Statement

A PreparedStatement is a precompiled SQL statement. It supports parameters (?) which can be set dynamically, making it faster and safer than Statement.

Syntax:

String query = "INSERT INTO people(name, age) VALUES (?, ?)";

PreparedStatement pstmt = con.prepareStatement(query);

Execution Methods

• execute(): This returns a boolean value and executes a static SQL statement that is present in the prepared statement object.
• executeQuery(): This returns a ResultSet from the current prepared statement.
• executeUpdate(): This returns the number of rows affected by the DML statements such as INSERT, DELETE and more that is present in the current Prepared Statement.

Example: Java Program illustrating Prepared Statement in JDBC

Output: 

👁 Output of Prepared Statement

3. Callable Statement

A CallableStatement is used to execute stored procedures in the database. Stored procedures are precompiled SQL logic stored on the server, often used for complex operations.

Syntax:

CallableStatement cstmt = con.prepareCall("{call ProcedureName(?, ?)}");

Execution Methods

• execute(): Executes the stored procedure and returns a boolean indicating whether the result is a ResultSet (true) or an update count (false).
• executeQuery(): Executes a stored procedure that returns a ResultSet.
• executeUpdate(): Executes a stored procedure that performs an update and returns the number of rows affected.

Example: Java Program illustrating Callable Statement in JDBC

Output: 

👁 Output for Callable Statement

Difference Between Statement, PreparedStatement and CallableStatement