The Statement object executes a normal SQL statement to update or query the database. The java.sql.Statement interface is a part of JDBC API that describes a standard abstraction for statement object which is implemented as part of JDBC driver.
A Statement object sends the required SQL statement as a query and then returns a set of results. We can use the createStatement() method of the connection object to obtain Statement object because we cannot create object of interface.
Exploring the Methods of Statement
We can create multiple objects by using a single connection object. The following method of the connection objects is used to retrieve a statement object:
CreateStatement():
It retrieves a statement that can produce a ResultSet of the Forward only type and read concurrency.
The following are some methods supported by Statement object:
1. public ResuItSet execute Query(String query):
It executes the given SQL select statement, which returns ResultSet object.
2. public int execute Update (String query):
It execute the given SQL non-select statement which may be an Insert, Update or Delete statement or an SQL statement which returns nothing, such as SQL DDL statement.
3. public boolean execute (String query):
This method is used for executing both select and non-select statements (Insert, Update, Delete) statement.
When the execute method is called, we need two helper methods to read the data output of command from the database.
(a) GetResuItSet(): This method is called when select operation is done on database.
(b) GetUpdateCount(): This method is called when non-select is done in database.
Steps to write JDBC program
Step-1 Load JDBC driver class into JVM
Syntax to load driver:
Class.forName (" fully qualified classname");
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
Step-2 Establish the connection with database
To obtain connection with database, a Java program asks for the same DriverManager class. By calling static method called getconnection(). While calling getconnection().
A Java program will pass three parameters:
I. URL
II. User name
III. Password
A URL is used to identify a driver’s register with DriverManager.Username and Password is used for authentication.
Syntax:
Connection cn = DriverManager.getconnection ("URL" ,"Username" ,"Password");
For Type-1:
Connection cn = DriverManager.getconnection("Jdbc:odbc:dsn-name","system", "manager");
Step-3 Create statement object (same for all driver)
Syntax:
Statement st = cn.createStatement(); // {cn= Connection Object}
Step-4 Execute the SQL queries
Syntax:
ResultSe trs = st.executeQuery("select * from emp");
Step-5 Print the result
Step-6 Close the connection
1. The executeQuery(String query) Method
It is used to execute select statement which returns ResultSet object.
Example
import java.sql.*;
public class Test {
public static void main(String args []) throws Exception {
//step-l load the driver (Type-l)
Class.forName ( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;
//step-2 get the connection
Connection cn=DriverManager.getConnection ("jdbc:odbc:dinesh","system"," manager") ;
//step-3 create Statement, object
Statement st=cn.createStatement();
//step-4 execute sql statement
ResultSet rs=st .executeQuery ("select * from emp");
//step-5 print the output
while(rs.next ()) {
int empno=rs.getInt(1);
String ename=rs.getString(2);
System.out .println ("Employee no "+empno+"Employee name "+ename);
}
//step-6 close connection and Statement and .Resultset
st.close ();
rs.close () ;
cn.close () ;
}
}
II. The executeUpdate(String query) Method
This method is executed for DDL or DML operation on database. It means non-select operation on database. When DDL operation is executed on database the result will be negative integer and for DML result is positive integer.
DML operation= Insert, Update, Delete
DDL operation= create, Truncate, drop, alter
Example
import java.sql.*;
public class CreateTable {
public static void main(String args[]) throws Exception {
//step-1 load the driver(Type - 2)
Class.forName ("oracle.jdbe.OracleDriver") ;
//step-2 get the connection
Connection cn=DriverManager.getConnection("jdbc:oracle:oci:@XE","system","manager");
//step-3 create Statement object
Statement st=cn.createStatement () ;
//step-4 execute sql statement
int k=st.executeUpdate("create table student(name varchar2(20),roll number (7) )");
if(k<0) {
System.out.println ("Table created");
}
else {
System.out.println("tahle is not created");
}
st.close();
cn.close();
}
}
Output:
Table created
• In the same way, perform Insert operation:
int k=stint.executeUpdate(“insert into student values(“sai,1″)”);
III. The execute(String query) Method
This method is used for executing both select and non-select operation.
boolean b=st.execute(“select * from emp”);
• If we execute select operation then return true.
boolean b=st.execute(“create table student(name varchar2(15),roll name(7))”);
• If we execute non-select operation then return false.
Example
import java.sql.* ;
public class Test2 {
public static void main(String args [ ]) throws Exception {
//step-l load the driver(Type-4)
Class.forName ("oracle.jdbc.driver.OracleDriver");
//Step-2 get the connection
Connection cn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system","manager");
//step-3 create Statement object
Statement st=cn.createStatement( ) ;
//step-4 execute sql statement
boolean b=st.execute("select * from emp");
if(b==true) {
System.out.println ("select operation done") ;
ResultSet rs=st.getResultSet ();
//step-5 print the output
while(rs.next( )) {
int k=rs.getInt(1);
String name=rs.getString (2) ;
System.out.println("ROll No "+k+" Name :" +name);
}
rs. close( ) ;
}
else {
System.out.println( "non-select operation is done");
int k=st.executeUpdateCount() ;
}
//step-6 close connection and Statement and ResultSet
st.close () ;
cn.close () ;
}
}
Limitation with Simple Statement Object
Framing SQL query with variable for simple statement object is a complex process.
If simple statement is used to execute same query multiple times with same or different values:
(a) The same query goes to database software multiple times.
(b) Same query will be parsed in database software multiple times.
(c) Same query will be executed in database software multiple times with same or different value.
(d) The same query output will be fetched out multiple times.
In the above operation (a) and (b) are unnecessary operations for multiple times on single query. But they cannot be avoided while working with simple Statement object. This makes database software perform unnecessary operations which may degrade the performance of the operation.
Dinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular