Statement
PreparedStatement
CallableStatement
1.Statement
Statement is an interface available in java.sql package.
Subclass of Statement interface is provided by Driver vendor. You can create the Statement object using the following methods of Connection interface.
public Statement createStatement();
public Statement createStatement(int,int);
public Statement createStatement(int,int,int);
You can call one of the following methods on Statement object to submit the SQL statement to Database.
public int executeUpdate(String sql);
public ResultSet executeQuery(String sql);
public boolean execute(String sql);
public int executeUpdate(String sql)
When you want to submit INSERT or UPDATE or DELETE SQL statements then use executeUpdate() method which returns the number of records inserted or updated or deleted.
public ResultSet executeQuery(String sql)
When you want to submit SELECT SQL statements the use executeQuery() method which returns the ResultSet object contains the records returned by the SELECT statement.
public boolean execute(String sql)
When you want to submit INSERT, UPDATE, DELETE, SELECT SQL statements then use execute() method which returns the boolean value.
If the returned value is true which means that SELECT SQL statement is submitted and the ResultSet object is created. Use the following method of statement to get the ResultSet object
public ResultSet getResultSet();
If the returned value is false which means that INSERT, UPDATE or DELETE SQL statement is submitted and the integer number is available which represents the number of records
inserted, updated or deleted. Use the following method of Statement to get the integer number available
public int getUpdateCount();
Using the single Statement object, you can submit any type of SQL statement and any number of SQL statements.
For example:
Statement st = con.createStatement();
String sql1 = "insert...";
String sql2 = "update...";
String sql3 = "delete...";
String sql4 = "select...";
boolean b1 = st.execute(sql1);
int x = st.executeUpdate(sql2);
int y = st.executeUpdate(sql3);
ResultSet rs = st.executeQuery(sql4);
When you submit the SQL statement using Statement object then SQL statement will compiled and executed every time.
Total time = req.time + compile time + execution time + res.time
= 5ms + 5ms + 5ms + 5ms =20ms.
1 SQL stmt = 20ms.
100 SQL stmts = 2000ms.
When you provide dynamic values for the SQL statement then you need to use concatination operator, Formatter or format() of String class etc to format the query.
int sid =101;
String name = "Shail";
String email= "shail@gmail.com"
long phone = 1234567890;
String sql ="insert into student values(" +sid+ ","+name+","+email+","+phone+")";
String sql = String.format("insert into student values(%d,'%s','%s',%d)",sid,name,email,phone);
Formatter fmt = new Formatter();
String sql = fmt.format("insert into student values(%d,'%s','%s',%d)",sid,name,email,phone).toString();
2.PreparedStatement
PreparedStatement is an interface available in java.sql package.
PreparedStatement is extending the Statement interface.
Subclass of PreparedStatement interface is provided by Driver vendor.
You can create the PreparedStatement object using the following methods of Connection interface.
public PreparedStatement prepareStatement(sql);
public PreparedStatement prepareStatement(sql,int,int);
public PreparedStatement prepareStatement(sql,int,int,int);
You can call one of the following methods on PreparedStatement object to submit the SQL statment to Database.
public int executeUpdate();
public ResultSet executeQuery();
public boolean execute();
Using the single public PreparedStatement prepareStatement(sql);object, you can submit only one SQL statement.
For example:
String sql = "insert...";
PreparedStatement ps = con.prepareStatement(sql);
int x = ps.executeUpdate();
When you submit the SQL statement using PreparedStatement object then SQL statement will be compiled only once first time and pre-compiled SQL statement wil be
executed every time.
Total time = req.time + compile time + execution time + res.time
= 5ms + 5ms + 5ms + 5ms =20ms.
First time = 20ms
Second time onwards = 5ms +0ms + 5ms + 5ms = 15ms.
100 SQL stmts =20ms+99*15ms
=20ms + 1485ms.
=1505ms.
PreparedStatement gives you the place holder mechanism for providing the data dynamically to the SQL statement. You need to use the "?" symbol for the placeholder.
To provide the value for placeholder, you need to invoke the setter method depending on the placeholder data type.
public void setInt(int paramIndex, int val);
public void setString(int paramIndex, String val);
public void setLong(int paramIndex, long val);
public void setDouble(int paramIndex, double val); etc
int sid =101;
String name = "Shail";
String email= "shail@gmail.com"
long phone = 1234567890;
String sql = "insert into student values(?,?,?)";
ps=con.prepareStatement(sql);
ps.setInt(1,sid);
ps.setString(2,name);
ps.setString(3,email);
ps.setLong(4,phone);
3.CallableStatement
CallableStatement is an interface available in java.sql package.
CallableStatement is extending PrepareStatement interface.
Subclass of CallableStatement interface is provided by the Driver vendor.
You can create the CallableStatement object using the following methods of Connection interface.
public CallableStatement prepareCall(sql);
public CallableStatement prepareCall(sql,int,int);
public CallableStatement prepareCall(sql,int,int,int);
You can call the following mmethod on CallableStatement object to submit the SQL statement to database.
public boolean execute();
Using the single CallableStatement object, you can submit call to only one procedure.
For example:
String sql "call p1(?,?)";
CallableStatement cs = con.prepareCall(sql);
cs.setInt(1,10);
cs.setInt(2,20);
cs.execute();
When you submit the call to stored procedure using CallableStatement object then pre-compiled stored procedure will be executed directly.
Total time = req.time + compile time + execution time + res.time
= 5ms + 0ms + 4*5ms + 5ms =30ms.
4 SQL stmts * 25 times.
=30*25.
=750.
CallableStatement gives you the placeholder mechanism for providing the data dynamically to the procedure parameters. You need to use the "?" symbol for the placeholder.
To provide the value for placeholder, you need to invoke the setter methods depending on the placeholder date type.
public void setInt(int paramIndex, int val);.
public void setString(int paramIndex, String val);
public void setLong(int paramIndex, long val);
public void setDouble(int paramIndex, double val); etc
You need to use the following method to specify the OUT parameter.
public void registerOutParameter(int parameterIndex,int sqlType);
sqlType is a constant from java.sql.Types class.
You need to use the following method to access the result of OUT parameter.
public int getInt(int paramIndex);
public String getString(int paramIndex);
public long getLong(int paramIndex);
public double getDouble(int paramIndex);
PreparedStatement
CallableStatement
1.Statement
Statement is an interface available in java.sql package.
Subclass of Statement interface is provided by Driver vendor. You can create the Statement object using the following methods of Connection interface.
public Statement createStatement();
public Statement createStatement(int,int);
public Statement createStatement(int,int,int);
You can call one of the following methods on Statement object to submit the SQL statement to Database.
public int executeUpdate(String sql);
public ResultSet executeQuery(String sql);
public boolean execute(String sql);
public int executeUpdate(String sql)
When you want to submit INSERT or UPDATE or DELETE SQL statements then use executeUpdate() method which returns the number of records inserted or updated or deleted.
public ResultSet executeQuery(String sql)
When you want to submit SELECT SQL statements the use executeQuery() method which returns the ResultSet object contains the records returned by the SELECT statement.
public boolean execute(String sql)
When you want to submit INSERT, UPDATE, DELETE, SELECT SQL statements then use execute() method which returns the boolean value.
If the returned value is true which means that SELECT SQL statement is submitted and the ResultSet object is created. Use the following method of statement to get the ResultSet object
public ResultSet getResultSet();
If the returned value is false which means that INSERT, UPDATE or DELETE SQL statement is submitted and the integer number is available which represents the number of records
inserted, updated or deleted. Use the following method of Statement to get the integer number available
public int getUpdateCount();
Using the single Statement object, you can submit any type of SQL statement and any number of SQL statements.
For example:
Statement st = con.createStatement();
String sql1 = "insert...";
String sql2 = "update...";
String sql3 = "delete...";
String sql4 = "select...";
boolean b1 = st.execute(sql1);
int x = st.executeUpdate(sql2);
int y = st.executeUpdate(sql3);
ResultSet rs = st.executeQuery(sql4);
When you submit the SQL statement using Statement object then SQL statement will compiled and executed every time.
Total time = req.time + compile time + execution time + res.time
= 5ms + 5ms + 5ms + 5ms =20ms.
1 SQL stmt = 20ms.
100 SQL stmts = 2000ms.
When you provide dynamic values for the SQL statement then you need to use concatination operator, Formatter or format() of String class etc to format the query.
int sid =101;
String name = "Shail";
String email= "shail@gmail.com"
long phone = 1234567890;
String sql ="insert into student values(" +sid+ ","+name+","+email+","+phone+")";
String sql = String.format("insert into student values(%d,'%s','%s',%d)",sid,name,email,phone);
Formatter fmt = new Formatter();
String sql = fmt.format("insert into student values(%d,'%s','%s',%d)",sid,name,email,phone).toString();
2.PreparedStatement
PreparedStatement is an interface available in java.sql package.
PreparedStatement is extending the Statement interface.
Subclass of PreparedStatement interface is provided by Driver vendor.
You can create the PreparedStatement object using the following methods of Connection interface.
public PreparedStatement prepareStatement(sql);
public PreparedStatement prepareStatement(sql,int,int);
public PreparedStatement prepareStatement(sql,int,int,int);
You can call one of the following methods on PreparedStatement object to submit the SQL statment to Database.
public int executeUpdate();
public ResultSet executeQuery();
public boolean execute();
Using the single public PreparedStatement prepareStatement(sql);object, you can submit only one SQL statement.
For example:
String sql = "insert...";
PreparedStatement ps = con.prepareStatement(sql);
int x = ps.executeUpdate();
When you submit the SQL statement using PreparedStatement object then SQL statement will be compiled only once first time and pre-compiled SQL statement wil be
executed every time.
Total time = req.time + compile time + execution time + res.time
= 5ms + 5ms + 5ms + 5ms =20ms.
First time = 20ms
Second time onwards = 5ms +0ms + 5ms + 5ms = 15ms.
100 SQL stmts =20ms+99*15ms
=20ms + 1485ms.
=1505ms.
PreparedStatement gives you the place holder mechanism for providing the data dynamically to the SQL statement. You need to use the "?" symbol for the placeholder.
To provide the value for placeholder, you need to invoke the setter method depending on the placeholder data type.
public void setInt(int paramIndex, int val);
public void setString(int paramIndex, String val);
public void setLong(int paramIndex, long val);
public void setDouble(int paramIndex, double val); etc
int sid =101;
String name = "Shail";
String email= "shail@gmail.com"
long phone = 1234567890;
String sql = "insert into student values(?,?,?)";
ps=con.prepareStatement(sql);
ps.setInt(1,sid);
ps.setString(2,name);
ps.setString(3,email);
ps.setLong(4,phone);
3.CallableStatement
CallableStatement is an interface available in java.sql package.
CallableStatement is extending PrepareStatement interface.
Subclass of CallableStatement interface is provided by the Driver vendor.
You can create the CallableStatement object using the following methods of Connection interface.
public CallableStatement prepareCall(sql);
public CallableStatement prepareCall(sql,int,int);
public CallableStatement prepareCall(sql,int,int,int);
You can call the following mmethod on CallableStatement object to submit the SQL statement to database.
public boolean execute();
Using the single CallableStatement object, you can submit call to only one procedure.
For example:
String sql "call p1(?,?)";
CallableStatement cs = con.prepareCall(sql);
cs.setInt(1,10);
cs.setInt(2,20);
cs.execute();
When you submit the call to stored procedure using CallableStatement object then pre-compiled stored procedure will be executed directly.
Total time = req.time + compile time + execution time + res.time
= 5ms + 0ms + 4*5ms + 5ms =30ms.
4 SQL stmts * 25 times.
=30*25.
=750.
CallableStatement gives you the placeholder mechanism for providing the data dynamically to the procedure parameters. You need to use the "?" symbol for the placeholder.
To provide the value for placeholder, you need to invoke the setter methods depending on the placeholder date type.
public void setInt(int paramIndex, int val);.
public void setString(int paramIndex, String val);
public void setLong(int paramIndex, long val);
public void setDouble(int paramIndex, double val); etc
You need to use the following method to specify the OUT parameter.
public void registerOutParameter(int parameterIndex,int sqlType);
sqlType is a constant from java.sql.Types class.
You need to use the following method to access the result of OUT parameter.
public int getInt(int paramIndex);
public String getString(int paramIndex);
public long getLong(int paramIndex);
public double getDouble(int paramIndex);
