Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What are the types of statements in JDBC?
There are three types of statements in JDBC namely, Statement, Prepared Statement, Callable statement.
Statement
The Statement interface represents the static SQL statement. It helps you to create a general purpose SQL statements using Java.
Creating a statement
You can create an object of this interface using the createStatement() method of the Connection interface.
Create a statement by invoking the createStatement() method as shown below.
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}Executing the Statement object
Once you have created the statement object you can execute it using one of the execute methods namely, execute(), executeUpdate() and, executeQuery().
execute(): This method is used to execute SQL DDL statements, it returns a boolean value specifying whether the ResultSet object can be retrieved.
executeUpdate(): This method is used to execute statements such as insert, update, delete. It returns an integer value representing the number of rows affected.
executeQuery(): This method is used to execute statements that returns tabular data (example SELECT statement). It returns an object of the class ResultSet.
Prepared Statement
The PreparedStatement interface extends the Statement interface. It represents a precompiled SQL statement which can be executed multiple times. This accepts parameterized SQL quires and you can pass 0 or more parameters to this query.
Initially, this statement uses place holders “?” instead of parameters, later on, you can pass arguments to these dynamically using the setXXX() methods of the PreparedStatement interface.
Creating a PreparedStatement
You can create an object of the PreparedStatement (interface) using the prepareStatement() method of the Connection interface. This method accepts a query (parameterized) and returns a PreparedStatement object.
When you invoke this method the Connection object sends the given query to the database to compile and save it. If the query got compiled successfully then only it returns the object.
To compile a query, the database doesn’t require any values so, you can use (zero or more) placeholders (Question marks “?”) in the place of values in the query.
For example, if you have a table named Employee in the database created using the following query:
CREATE TABLE Employee(Name VARCHAR(255), Salary INT NOT NULL, Location VARCHAR(255));
Then, you can use a PreparedStatement to insert values into it as shown below.
//Creating a Prepared Statement String query="INSERT INTO Employee(Name, Salary, Location)VALUES(?, ?, ?)"; Statement pstmt = con.prepareStatement(query);
Setting values to the place holders
The PreparedStatement interface provides several setter methods such as setInt(), setFloat(), setArray(), setDate(), setDouble() etc.. to set values to the place holders of the prepared statement.
These methods accepts two arguments one is an integer value representing the placement index of the place holder and the other is an int or, String or, float etc… representing the value you need to insert at that particular position.
Once you have created a prepared statement object (with place holders) you can set values to the place holders of the prepared statement using the setter methods as shown below:
pstmt.setString(1, "Amit"); pstmt.setInt(2, 3000); pstmt.setString(3, "Hyderabad");
Executing the Prepared Statement
Once you have created the PreparedStatement object you can execute it using one of the execute() methods of the PreparedStatement interface namely, execute(), executeUpdate() and, executeQuery().
execute(): This method executes normal static SQL statements in the current prepared statement object and returns a boolean value.
executeQuery(): This method executes the current prepared statement and returns a ResultSet object.
executeUpdate(): This method executes SQL DML statements such as insert update or delete in the current Prepared statement. It returns an integer value representing the number of rows affected.
CallableStatement
The CallableStatement interface provides methods to execute stored procedures. Since the JDBC API provides a stored procedure SQL escape syntax, you can call stored procedures of all RDBMS in a single standard way.
Creating a CallableStatement
You can create an object of the CallableStatement (interface) using the prepareCall() method of the Connection interface.
This method accepts a string variable representing a query to call the stored procedure and returns a CallableStatement object.
A CallableStatement can have input parameters or, output parameters or, both. To pass input parameters to the procedure call you can use place holder and set values to these using the setter methods (setInt(), setString(), setFloat()) provided by the CallableStatement interface.
Suppose, you have a procedure name myProcedure in the database you can prepare a callable statement as:
//Preparing a CallableStatement
CallableStatement cstmt = con.prepareCall("{call myProcedure(?, ?, ?)}");Setting values to the input parameters
You can set values to the input parameters of the procedure call using the setter methods.
These accept two arguments one is an integer value representing the placement index of the input parameter and the other is an int or, String or, float etc… representing the value you need to pass an input parameter to the procedure.
Note: Instead of index you can also pass the name of the parameter in String format.
cstmt.setString(1, "Raghav"); cstmt.setInt(2, 3000); cstmt.setString(3, "Hyderabad");
Executing the Callable Statement
Once you have created the CallableStatement object you can execute it using one of the execute() method.
cstmt.execute();
9K+ Views
