Types of Statements in JDBC

Last Updated : 03 Jan, 2025

In Java, the Statement interface in JDBC (Java Database Connectivity) is used to create and execute SQL queries in Java applications. JDBC provides three types of statements to interact with the database:

Statement
Prepared Statement
Callable Statement

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();

Implementation: Once the Statement object is created, there are three ways to execute it.

execute(String SQL): It is used to executes any SQL statements (like SELECT, INSERT, UPDATE or DELETE). If the ResultSet object is retrieved, then it returns true else false is returned.
executeUpdate(String SQL): It is used to executes SQL statements (like INSERT, UPDATE or DELETE). It returns the number of rows affected by the SQL statement.
ResultSet executeQuery(String SQL): It is used to executes the SELECT query. It returns a ResultSet object that contains the data retrieved by the query.

Example:

Java

// Java Program illustrating Create Statement in JDBC
import java.sql.*;

public class Geeks {
    public static void main(String[] args) {
        try {
          
            // Load the driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish the connection
            Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/world", "root", "12345");

            // Create a statement
            Statement st = con.createStatement();

            // Execute a query
            String sql = "SELECT * FROM people";
            ResultSet rs = st.executeQuery(sql);

            // Process the results
            while (rs.next()) {
                System.out.println("Name: " + rs.getString("name") +
                                   ", Age: " + rs.getInt("age"));
            }

            // Close resources
            rs.close();
            st.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

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

Output of Create Statement

2. Prepared Statement

A PreparedStatement represents a precompiled SQL statement that can be executed multiple times. It accepts parameterized SQL queries, with ? as placeholders for parameters, which can be set dynamically.

Illustration:

Considering in the people database if there is a need to INSERT some values, SQL statements such as these are used:

INSERT INTO people VALUES ("Ayan",25);
INSERT INTO people VALUES("Kriya",32);

To do the same in Java, one may use Prepared Statements and set the values in the ? holders, setABC() of a prepared statement is used as shown:

String query = "INSERT INTO people(name, age)VALUES(?, ?)";
PreparedStatement pstmt = con.prepareStatement(query);
// where pstmt is an object name
pstmt.setString(1,"Ayan");
ptstmt.setInt(2,25);

Implementation: Once the PreparedStatement object is created, there are three ways to execute it:

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

// Java Program illustrating Prepared Statement in JDBC
import java.sql.*;

import java.util.Scanner;

class Geeks {

    public static void main(String[] args) {
      
        // try block to check for exceptions
        try {

            // Loading drivers using forName() method
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Scanner class to take input from user
            Scanner sc = new Scanner(System.in);

            System.out.println(
                "What age do you want to search?? ");

            // Reading age an primitive datatype from user
            // using nextInt() method
            int age = sc.nextInt();

            // Registering drivers using DriverManager
            Connection con = DriverManager.getConnection(
                "jdbc:mysql:///world", "root", "12345");

            // Create a statement
            PreparedStatement ps = con.prepareStatement(
                "select name from world.people where age = ?");

            // Execute the query
            ps.setInt(1, age);
            ResultSet res = ps.executeQuery();

            // Condition check using next() method
            // to check for element
            while (res.next()) {

                // Print and display elements(Names)
                System.out.println("Name : "
                                   + res.getString(1));
            }

        }

        // Catch block to handle database exceptions
        catch (SQLException e) {

            // Display the DB exception if any
            System.out.println(e);
        }

        // Catch block to handle class exceptions
        catch (ClassNotFoundException e) {

            // Print the line number where exception occurred
            // using printStackTrace() method if any
            e.printStackTrace();
        }
    }
}

Output:

Output of Prepared Statement

3. Callable Statement

A CallableStatement is used to execute stored procedures in the database. Stored procedures are precompiled SQL statements that can be called with parameters. They are useful for executing complex operations that involve multiple SQL statements.

Syntax:

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

{call ProcedureName(?, ?)}: Calls a stored procedure named ProcedureName with placeholders ? for input parameters.

Methods to Execute:

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

// Java Program illustrating 
// Callable Statement in JDBC
import java.sql.*;

public class Geeks {

    public static void main(String[] args) {
      
        // Try block to check if any exceptions occur
        try {
          
            // Load and register the driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish a connection
            Connection con = DriverManager
              .getConnection("jdbc:mysql:///world", "root", "12345");

            // Create a CallableStatement
            CallableStatement cs = 
              con.prepareCall("{call GetPeopleInfo()}");

            // Execute the stored procedure
            ResultSet res = cs.executeQuery();

            // Process the results
            while (res.next()) {
              
                // Print and display elements (Name and Age)
                System.out.println("Name : " + res.getString("name"));
                System.out.println("Age : " + res.getInt("age"));
            }

            // Close resources
            res.close();
            cs.close();
            con.close();
        } 
        // Catch block for SQL exceptions
        catch (SQLException e) {
            e.printStackTrace();
        } 
        // Catch block for ClassNotFoundException
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

Output:

Output for Callable Statement

Explanation:

This Java code demonstrates how to use a CallableStatement in JDBC to execute a stored procedure.
It connects to a MySQL database and prepares a CallableStatement to call a stored procedure named peopleinfo with two parameters.
After executing the procedure, it runs a SELECT query to retrieve and display all records from the people table.
Exception handling is included to manage potential SQL and class loading errors.

Java Memory Management

budhirajachinab99

Improve

Article Tags :

Practice Tags :

Java

Types of Statements in JDBC

1. Statement

2. Prepared Statement

3. Callable Statement

Similar Reads

Java Overview

Java Basics

Java Flow Control

Java Methods

Java Arrays

Java Strings

Java OOPs Concepts

Java Interfaces

Java Collections

Thank You!

What kind of Experience do you want to share?