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.
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:
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:
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.