What are the types of JDBC Statements available?

Once a connection is obtained using JDBC, we can interact with the database to store the data or to fetch the data from Database. The JDBC Statement interface defines the methods and properties that enable you to send SQL or PL/SQL commands and receive data from your database.

There are 3 types of JDBC Statements
1) Statement
2) PreparedStatement
3) CallableStatement

Statement:
1) A statement is an interface available in java.sql package.
2) You can create the statement using the following methods of Connection interface.

  • Public statement createStatement()
  • public Statement createStatement(int, int)
  • public Statement createStatement(int, int, int)

3) After creating the statement object, you can call one of the following methods to submit the SQL statement to the Database.
a) public int executeUpdate(String sql)
b) public boolean execute(String sql)
c) public ResultSet executeQuery(String sql)
4) 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.
5) 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).

Method to get the result object with Statement object
public ResultSet getResultSet().

Method to get the number of records affected with Statement object
public int getUpdateCount().

6) When you want to submit select SQL, the statement then uses executeQuery() method which returns the number of records fetched by a select statement in terms of ResultSet object.

7) Using the single statement object, you can submit any type of SQL statement and any m=number of SQL statements.

Example:
Statement st= com.createStatement();

String sql1=”insert…”;
String sql2=”update….”;
String sql3=”delete…”;
String sql4=”select…”;
boolean b1=st.execute(sql1);
int y= st.executeUpdate(sql3);
When you submit the SQL statement using statement object then SQL statement will be complied and executed every time.
Total time= req..time + compile time + exec time +res.time = 5ms +5ms + 5ms + 5ms =20 ms.
1 SQL stmt= 20ms.
100 times= 2000ms.
If you are providing dynamic values for the query then you need to use concatenation operator, Formator or StringBuffer, etc to format the query.
If you are providing the value that format is database dependent (Maybe date) then you need to provide depending on Database.

For MySQL:
CREATE TABLE xadmin( id INT PRIMARY KEY, NAME VARCHAR(20), email VARCHAR(50), phone LONG, fee FLOAT, dob DATE);

FOR Oracle:
CREATE TABLE xadmin( id INT PRIMARY KEY, NAME VARCHAR(20), email VARCHAR(50), phone number(10), fee FLOAT, dob DATE);

Example program Using execute() method.

package com.xadmin.b13.jdbc;
import com.xadmin.b13.jdbc.util.JDBCUtil;

public class Xadmin
 {
 public static void main(String args[])
  {
   Connection con=null;
   Statement st =null;
   ResultSet rs=null;
   try
    {
    if(args.length !=1)
     {
     System.out.println(“Provide the QUERY as CLA”);
     System.exit(0);
     }
    con = JDBC.Util.getMySQLConnection();
    String qry = args[0];
    st=con.createStatement();
    boolean res= st.execute(qry);
    System.out.println(res);
    if(res)
    {
    rs=st.getResultSet();
    while(rs.next())
     {
     int id= rs.getInt(1);
     String name= rs.getString(2);
     System.out.println(id + “\t” name);
     }
  }
   else
    {
    int no= st.getUpdateCount();
    System.outprintln(“No of Record Affected: ”+no);
    }
    catch(SQLException e)
     {
     e.printStackTrace();
     }
    finally
    {
    JDBCUtil.cleanup(rs, st, con);
    }
  }
}

PreparedStatement
1) PreparedStatement is an interface available in java.sql.package and extends Statement interface.
2) You can create the PreparedStatement using the following methods of Connection interface.

  • Public PreparedStatement prepareStatement(sql);
  • Public PreparedStatement prepareStatement(sql, int, int);
  • Public PreparedStatement prepareStatement(sql, int, int, int);

3) After creating the PreparedStatement object, you can call one of the following methods to submit the SQL Statement to Database.

  • Public int executeUpdate()
  • public boolean execute()
  • public ResultSet executeQuery()

4) Using the single preparedStement Object, you can submit only one SQL Statement.

Example:
String sql=”insert…..”;
PreparedStatement ps=con.prepareStatement(sql)
int x= ps.executeUpdate();

5) when you submit the SQL Statement using PreparedStatement object then SQL Statement will be compiled only once, first time and pre-compile SQL, statement will be executed every time.

Total time= req.time + compile time +exec time + res.time
= 5ms +5ms+ 5ms= 20ms
First time-> 1 SQL Stmt=20ms.
Next onwards –> 5 ms +0 ms +5 ms +5 ms = 15ms.
101 times= 20ms + 150 ms => 1520.

6) PreparedStatement gives you the place holder mechanism for providing the data dynamically to the query. You need to use a symbol for a place holder

7) To provide the value of place holder you need to invoke the following method depending on the type of the value for a place holder.
Public void setX(int paramIndex, X val)
X can be int, String, Long, Float, Date, etc

8) If you want to specify the date type value then create the object of java.sql.Date type and invokes the following method
public void setDate( int paramIndex, Date dt)

Example using preparedStatement object with SQL Insert

String sql=”insert into xadmin values(?,?,?,?,?,?)”;
ps=con.prepareStatement(sql);
ps.setInt(1,id);
ps.setString(2,nm);
ps.setString(3,eml);
ps.setLong(4,phn);
ps.setString(5, fee);
ps.setDate(6, dt);

Example using preparedStatement object with SQL select

Connection con=null;
PreparedStatement st=null;
ResultSet rs=null;
try
{
con=JDBCUtil.getMySQLConnection();
String sql=”select *from xadmin;
st=con.prepareStatement(sql);
rs=st.st.executeQuery();

CallableStatement:

1) CallableStatement is an interface available in java.sql.package and extends PreparedStatement interface.

2) You can create the CallableStatement using the following methods of Connection interface.

  • CallableStatement prepareCall(String)
  • CallableStatement prepareCall(String, int, int)
  • CallableStatement prepareCall(String, int, int, int)

3) After creating the CallableStatement object, you can call one of the following methods to submit the SQL Statement to Database.

  • Int executeUpdate()
  • boolean execute()
  • ResultSet executeQuery()

4) Stored procedure is pre-compiled procedure .ie, when you create the procedure then that procedure will be compiled and stored in data base memory. When you make call to the procedure then that pre-compiled procedure will be executed directly.

5) Using the single CallableStatement object, you can make call to only one stored procedure. Ex:
String sql=”call p1(?,?)”;
CallableStatement cs=con.prepareCall(sql);
cs.setInt(1,10);
cs.setInt(2,20);
int x=cs.executeUpdate();

6) Use stored procedures when you want to run some logic in darabase.

With Stored Procedure using CallableStatement.
Total time= req.time +compile time + exec time + res.time
= 5 ms+ 0ms + 20ms+ 5ms = 30 ms.
101 times–> 3030ms
with SQL using PS.
Total time= req.time + compile timne + exec time + res.time
= 5 ms + 0ms + 20ms + 5ms = 30 ms.
with 4 SQL
4 SQL’s -> 4 *20ms = 80ms ( with Statement)
-> 4 * 15 ms= 60ms
101 times = 80 ms +6000 ms=> 6080 ms.

7) CallableStatement gives you the place holder mechanism.

Conclusion:
In this article, we learned about the different types of JDBC- Statement with proper explanation of each type and all the methods present inside it with example. Please feel free to drop a comment in a below comment box, if you have any doubt regarding the topic or you want to share more information about the topic. Happy Learning!

 

Leave a Reply

Your email address will not be published. Required fields are marked *