What are Batch updates and ResultSet in JDBC

Batch updates
When you want to submit multiple types of SQL statements and No. Of SQL statements to the Database at a time then use Batch Updates.
Without batch Updates Using Statement:
Total time= request time + compile time +execute time + response time
Total time to execute 1 Query = 5ms + 5ms + 5ms + 5 ms = 20ms
For 5 Query = 5 * 20 = 100ms

Without batch Updates Using PreparedStatement(Query Already Complied)
Total time= request time + compile time +execute time + response time
Total time to execute 1 Query = 5ms + 0ms + 5ms +5ms=15ms
For 5 Query = 5*15=75ms

With Batch Update using Statement
Total time=request time + compile time +execute time + response time
For 5 Query= 5ms + 5*5ms +5*5ms + 5ms
= 5ms + 25ms + 25ms+ 5ms
= 60ms

With Batch Update using PreparedStatement
Total time= request time + compile time +execute time + response time
For 5 Query= 5ms + 0ms+ 5*5ms +5ms
= 5ms + 0ms + 25ms + 5ms
= 35ms

Example for Batch Updates with Statement.

The method used to add the query in Batch: public void addBatch(String sql);
The method used to clear the batches: public void clearBatch();
The method used to submit the Queries as a batch: public int[] executeBatch();

String sql1=”insert in to customers..”;
String sql2=”update customers..”;
String sql3=”delete from customers..”;
st= con.createStatement();
st.addBatch(sql1);
st.addBatch(sql2);
st.addBatch(sql3);
Int x[] = st.executeBatch();

Example for Batch Updates with PreparedStatement.
Method to add the Query in Batch: public void addBatch();
Method to submit the queries as a batch: public int[] executeBatch()

Package com.xadmin.b13.jdbc;
import java.sql.*;

public class xadminJdbc
{
public static void main(String[] args)
{
Connection con=null;
PreparedStatement ps=null;
try
{
con= JDBCUtil.getOracleConnection();
ps= con.prepareStatement(“insert into xadmindb(id, name, email) values(?,?,?)”);
for(int i=1; i<=5;i++)
{
int id=500 +i;
String name = “ashish” + i;
String email = “ashish”+i+”@xadmin.org”;
ps.setInt(1,id);
ps.setString(2,name);
ps.setString(3,email);
ps.addBatch();
}

int res[] = ps.executeBatch();
for(int i=0;i<res.length;i++)
{
System.out.println(“Res:”+res[i]);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
JDBCUtil.cleanup(ps, con);
}
}
}

Can we submit a select statement using a batch update?
Ans: No, because if the second result set will be available then the first result set object will not be used.

Can we use a batch update with CallableStatement?
Ans: No.

What is ResultSet in JDBC?

ResultSet is an interface available in java.sql package. ResultSet object will be created by using the following method of Statement: ResultSet rs=st.executeQuery(sql); Method of preparedStatement or CallableStatement: ResultSet rs= ps.executeQuery();

When ResultSet object is created the ResultSet point initially Points to before to first record. To move the ResultSet Pointer, you can use next() method (rs.next()).rs.next() method do the following things:

  1. checks whether the next record is available or not.
  2. If the next record is available then
    1. moves the pointer to next record
    2. return true.
  3. If the next record is not available then
    1. moves the pointer to next record
    2. returns false
  4. when ResultSet pointer is pointing one record then you can access the column value of that using getXX() methods.

For Example:
getInt(1) or getInt(“cid”);
getString(2) or getString(“cname”);
getLong(3) or getLong(“ph”)
etc

Consider the following case:
I have a table called xadmin with 10 columns. Like xid, name, age, sex, phone, address, etc. Now I am writing the select statement as select xid, name, age from xadmin. Here, RS will have only 3 columns as per select statement
xid=rs.getInt(1);
name=rs.getString(2);
age=rs.getInt(3);

Types of ResultSets:
Depending on the scrollability, you can deivide the ResultSets in to Two types.

  1. Forward-Only ResultSets
  2. Scrollable ResultSets

1) Forward-Only ResultSets:-
When ResultSet is forward-only you can move pointer only on the forward direction and only once.
By default:

st= con.createStatement();
rs=st.executeQuery(sql);

ps=con.prepareStatement(sql);
rs=ps.executeQuery()

you can specify as
st=con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCURR_READ_ONLY);
rs=st.executeQuery(sql);

ps=con.prepareStatement(sql,  ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCURR_READ_ONLY);
rs=ps.executeQuery()

In the above code, ResultSet created is the forward-only resultset. You can use the following methods on Forward-only resultset.

  • next()
  • getXXX(int)
  • getXXX(String)
  • isBeforeFirst()
  • isFirst()
  • isAfterLast()
  • getRow()
  • getRow()
  • getResultType()

2) Scrollable ResultSets:-
when ResultSet is Scrollable then you can move the pointer in any direction(forward or reverse) and any number of times.
You can specify as:

st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCURR_READ_ONLY);
rs=st.executeQuery(sql);
ps=con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCURR_READ_ONLY);
rs=ps.executeQuery()

In above code, ResultSet created is the Scrollable resultset. You can use the following methods on Scrollable resultset.

  • next()
  • getXXX(int)
  • getXXX(String)
  • previous()
  • first()
  • last()
  • beforeFirst()
  • afterLast()
  • isFirst()
  • isLast()
  • isBeforeFirst()
  • isAfterLast()
  • absolute()
  • relative()

Types of ResultSets
Depending on the updateability of ResultSets, you can divide the ResultSets into two types:

  1. Read-only ResultSets or Static ResultSets
  2. Updateable ResultSets or Dynamic ResultSets

1). Read-only ResultSet:
When ResultSet is read-only then you can just access the data from ResultSet object by calling getter methods. You canot do the following operation:

  • insert records into ResultSet
  • update the records of ResultSet
  • Delete the records from ResultSet

By Default:

st=con.createStatement();
rs=st.executeQuery(sql);
ps=con.prepareStatement(sql);
rs=ps.executeQuery()
In above code, ResultSet created is the Forward-only resultSet and read-only result set.

You can specify as:

st=con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCURR_READ_ONLY);
rs=st.executeQuery(sql);
ps=con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCURR_READ_ONLY);
rs=ps.executeQuery()

In this code also, ResultSET created is the Forward-only resultset and resd-only result set.

2)Updatable ResultSets:
When ResultSet is updatable then you can do the followjng opearations on ResultSet object.

  1. get the data from ResultSet
  2. insert records in to ResultSet
  3. update the records of ResultSet
  4. delete the records from ResultSet

    Note: When ResultSet is Updatable then it must be scrollable.

You can specify as:

  • st=con.createStatement(ResultSet.TYPE_SCORLL_SENSITIVE, ResultSet.CONCURR_UPDATABLE);
  • rs=st.executeQuery(sql);
  • ps=con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE , ResultSet.CONCURR_UPDATABLE);
  • rs=ps.executeQuery()

In this code, ResultSet created is the Scrollable and Updatable resultset.

What is ResultSet Holdability?
When you are managing the transaction, you have to call explicitly commit() or rollback() at the end of every transaction. You can decide whether to keep or close the result set object at the end of Transactions. For this, you can specify the required Constant.

A) Contants related to Scrollability:
TYPE_FORWARD_ONLY(*default) 1003
TYPE_SCROLL_INSENSITIVE 1004
TYPE_SCROLL_SENSITIVE 1005
With Statement: public int getResultSetType()

B) Constants related to Updatability
CONCURR_READ_ONLY(*default) 1007
CONCURR_UPDATABLE 1008
With Statement:Public int getResultSetConcurrency()

C) Constants realted to Holdability:
HOLD_CURSORS_OVER_COMMIT(*default) 1
CLOSE_CURSORS_AT_COMMIT 2
With Statement: public int getResultSetHoldability()

For Statement
createStatement()
createSatement(int, int) A B
createSatement(int, int, int) A B C

For PreparedStatement:
prepareStatement(String)
prepareStatement(String, int, int) A B
prepareStatement(String, int , int, int) A B C

What is DatabaseMetaData

DatabaseMetaData is an interface available in java.sql.package. DatabaseMetaData is used to get the information about your database. ie. You canfind whether database is supporting the required features or not. You can create the DatabaseMetaData object as follows:
DatabaseMetaData dbmd=con.getMetaData();

What is ResultSetMetaData
ResultSetMetaData is an interface available in java.sql.package. ResultSetMetaData is used to get the information about your ResultSet object. You can create the ResultSetMetaData object as follows:
ResultSetMetaData rsmd= rs.getMetaData();

What is RowSet?
This interface is extending the ResultSet interface. RowSet functionality is almost the same as ResultSet.

RowSet Types:
Connected RowSet: JdbcRowSet is the example of connected Rowset. In this, the connection is required until the life of the Rowset object.

Disconnected RowSet: Other types of Rowsets are the example of Disconnected Rowset. In this, after accessing the data from the database the connection will be closed. And without connection, you can access the data from the  Rowset object.
You can Serialize this type of RowSet to transfer the data from one machine to another machine.

Types of RowSets

  1. JdbcRowSets
  2. CachedRowSets
  3. WebRowSets
  4. JoinRowSets

ResultSet vs RowSet

ResultSet

RowSet

ResultSet object is used to store records returned by select SQL Statement RowSet object is also used to store records returned by Select SQL statement
ResultSet object can be created as follows:

  • con=D.M.getConnection(url, un, pw);
  • st=con.createStatement();
  • rs=st.executeQuery(sql);
RowSet object can be created as follows.
RowSet jrs=new JdbcRowSetImpl();
jrs.setUrl(url);
jrs.setUsername(un);
jrs.setPassword(pw);
jrs.setCommand(sql);
jrs.execute();
By default ResultSets are forward-only and read-only By Default RowSets are scrollable and updatable
ResultSets are connection oriented objects i.e. As long as connection is available. You can access the ResultSet data.

Once a connection is closed, ResultSet also will be closed automatically.

RowSets are connectionless objects. ie. You can access the RowSet data without Connection.
ResultSet objects are not eligible for Serialization. RowSet objects are eligible for Serialization.

 Conclusion:
In this article, we learned about the Batch updates, ResultSet, RowSet, and their types. Please feel free to drop a comment in the below comment box if you find any doubt about the topic or you want to share more information about the topic.

 

Leave a Reply

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