• JDBC statements are dedicated to submit the SQL query from JDBC Program to Database.
• Types of JDBC Statements:
a. Statement [java.sql.Statement [Interface]]
b. PreparedStatement [java.sql.PreparedStatement [Interface]]
c. CallableStatement [java.sql.CallableStatement [Interface]]
a. Java.sql.Statement is an Interface.
b. Sub class of java.sql.Statement interface provides by Driver Vendor.
c. We can create Statement type object by using following methods of java.sql.Connection Interface.
• public Statement createStatement ()
• public Statement createStatement (int , int)
• public Statement createStatement (int ,int, int)
d. After creating the Statement type object we can call following methods to submit the different SQL queries to the database.
o public int executeUpdate (String)
o public boolean execute (String)
o public ResultSet executeQuery (String)
Access Modifier: - public
Return Type: - int
Member Type: - Instance Method
Method Name: - executeUpdate
Method Parameter: - String
Functionality: - When we want to submit insert, update and delete SQL queries then we use executeUpdate() method and after successful submission it return numbers of record inserted, updated or deleted respectively.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JdbcStatement {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/tutorial?autoReconnect=true&useSSL=false";
Connection con = DriverManager.getConnection(url, "root", "root");
String sql1 = "insert into JtcStudent values(102,'Vivek Sareen','viveksareen@jtcindia.org','Noida')";
String sql2 = "insert into JtcStudent values(101,'rahul','rahul@jtcindia.org','Noida')";
String sql3 = "update JtcStudent set sname = 'Rahul' where sid = 101";
Statement statement = con.createStatement();
// Storing Record into Student Table.
int i = statement.executeUpdate(sql1);
if (i == 1) {
System.out.println("Record Inserted");
}
// Storing Record into Student Table.
i = statement.executeUpdate(sql2);
if (i == 1) {
System.out.println("Record Inserted");
}
// Updating a Record of Student Table.
i = statement.executeUpdate(sql3);
if (i == 1) {
System.out.println("Record Updated");
}
}
}
Access Modifier: - public
Return Type: - boolean
Member Type: - Instance Method
Method Name: - execute
Method Parameter: - String
Functionality: - When you want to submit insert, update, delete or select SQL Statements then use execute() method which returns the boolean value saying whether the ResultSet object is created or not(The SQL Statement is SELECT or not).
if return value is true which means that SELECT SQL statement is submitted and ResultSet object is created.
→ public ResultSet getResultSet()
If ResultSet type object is creates than using getResultSet() method we can collect the ResultSet object and process the data into the JDBC program as per our business logic.
→ public int getUpdateCount()
If return value false which means that INSERT, UPDATE , DELETE SQL
Statements are submitted and using getUpdateCount() method we can collect the numbers of effected rows.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Jtc3 {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/tutorial";
Connection con = DriverManager.getConnection(url, "root", "root");
Statement statement = con.createStatement();
Scanner scanner = new Scanner(System.in);
System.out.println("Enter SQL Query . ");
String sql = scanner.nextLine();
boolean b1 = statement.execute(sql);
if (b1 == true) {
ResultSet resultSet = statement.getResultSet();
while (resultSet.next()) {
System.out.println("Sid :- " + resultSet.getInt(1) + " | Sname : - " + resultSet.getString(2)
+ " | Semail : - " + resultSet.getString(3) + " | Scity :- " + resultSet.getString(4));
}
} else {
int i = statement.getUpdateCount();
System.out.println("Numbers of row effected :- " + i);
}
}
}
Access Modifier: - public
Return Type: - ResultSet
Member Type: - Instance Method
Method Name: - executeQuery
Method Parameter: - String
Functionality: - When you want to submit Select SQL query then use executeQuery() method which returns the number of records fetched by select statement in terms of ResultSet object.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Jtc4 {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/tutorial";
Connection con = DriverManager.getConnection(url, "root", "root");
Statement statement = con.createStatement();
String sql = "select * from JtcStudent";
ResultSet resultSet = statement.executeQuery(sql);
int count = 0;
while (resultSet.next()) {
count++;
System.out.println("Sid :- " + resultSet.getInt(1) + " | Sname : - " + resultSet.getString(2)
+ " | Semail : - " + resultSet.getString(3) + " | Scity :- " + resultSet.getString(4));
// System.out.println(resultSet.next());
}
if (count == 0) {
System.out.println("No Record Found");
}
}
}
e. Using same java.sql.Statement type object we can submit different Types of SQL query.
f. Using same java.sql.Statement type object we can submit multiple SQL queries.
Example :-
Statement st=con.createStatement();
String sql1="insert ....";
String sql2="update ....";
String sql3="delete ....";
String sql4="select ....";
booleab b1=st.execute(sql1);
int x=st.executeUpdate(sql2);
int y=st.executeUpdate(sql3);
ResultSet rs=st.executeQuery(sql4);
g. When we submit a SQL query to the database using java.sql.Statement interface type object than every time it compile and execute.
Total time = req.time + compile time + exec time + res.time = 5 ms+5 ms+5 ms+5 ms = 20 ms. 1 SQL Stmt = 20 ms. 100 times = 2000 ms.
h. 100 times = 2000ms. If you are providing dynamic values for the query then you need to use concatenation operator,Formatter or StringBuffer etc. to format the query.
i. If you are providing the value that format is database dependent (May be Date) then you need to provide depending on Database.