Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can you move the cursor in scrollable result sets in JDBC?
In JDBC there are two scrollable ResultSets namely, scrollable sensitive and, scrollable insensitive.
In the TYPE_SCROLL_INSENSITIVE ResultSet the cursor moves in forward or backward directions. This type of ResultSet is insensitive to the changes that are made in the database i.e. the modifications done in the database are not reflected in the ResultSet.
Which means if we have established a connection with a database using JDBC program and retrieved a ResultSet holding all the records in a table named SampleTable. Meanwhile if we have added some more records to the table (after retrieving getting the ResultSet), these recent changes will not be reflected in the ResultSet object we previously obtained.
In the TYPE_SCROLL_INSENSITIVE ResultSet the cursor moves in forward or backward directions. This type of ResultSet is sensitive to the changes that are made in the database i.e. the modifications done in the database are reflected in the ResultSet.
Which means if we have established a connection with a database using JDBC program and retrieved a ResultSet holding all the records in a table named SampleTable. Meanwhile if we have added some more records to the table (after retrieving the ResultSet), these recent changes will be reflected in the ResultSet object we previously obtained.
Either scrollable sensitive or, scrollable insensitive both result sets are bi-directional i.e. you can move them in both forward and backward directions.
You can create a Statement object that returns a Scrollable result set, using the createStatement() method as −
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
You can traverse through the contents of a ResultSet object in forward direction using the next() method and, you can traverse its contents in reverse direction using forward() method.
Let us create a table with name cricketers_data in MySQL database using CREATE statement as shown below −
CREATE TABLE MyPlayers( ID INT, First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255), PRIMARY KEY (ID) );
Now, we will insert 7 records in cricketers_data table using INSERT statements −
insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');We have connected to the database Using a JDBC program and Retrieved the records of the above table into a ResultSet object and traversed through it in forward and reverse directions using the next() and previous() methods.
Example
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ScrollableResultset {
public static void main(String args[])throws Exception {
//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//Getting the connection
String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Creating a Statement object
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
//Query to retrieve the contents of the cricketers_data table
String query = "select * from cricketers_Data";
//Executing the query
ResultSet rs = stmt.executeQuery(query);
//Contents of the ResultSet in forward direction
System.out.println("Contents of the ResultSet in forward direction");
while (rs.next()) {
System.out.print("id: "+rs.getInt(1)+", ");
System.out.print("First name: "+rs.getString(2)+", ");
System.out.print("Last name: "+rs.getString(3)+", ");
System.out.print("Year of birth: "+rs.getDate(4)+", ");
System.out.print("Place of birth: "+rs.getString(5)+", ");
System.out.print("Country: "+rs.getString(6));
System.out.println(" ");
}
System.out.println(" ");
//Contents of the ResultSet in reverse direction
System.out.println("Contents of the ResultSet in reverse direction");
rs.afterLast();
while (rs.previous()) {
System.out.print("id: "+rs.getInt(1)+", ");
System.out.print("First name: "+rs.getString(2)+", ");
System.out.print("Last name: "+rs.getString(3)+", ");
System.out.print("Year of birth: "+rs.getDate(4)+", ");
System.out.print("Place of birth: "+rs.getString(5)+", ");
System.out.print("Country: "+rs.getString(6));
System.out.println(" ");
}
}
}Output
Connection established...... Contents of the ResultSet in forward direction id: 1, First name: Shikhar, Last name: Dhawan, Year of birth: 1981-12-05, Place of birth: Delhi, Country: India id: 2, First name: Jonathan, Last name: Trott, Year of birth: 1981-04-22, Place of birth: CapeTown, Country: SouthAfrica id: 3, First name: Kumara, Last name: Sangakkara, Year of birth: 1977-10-27, Place of birth: Matale, Country: Srilanka id: 4, First name: Virat, Last name: Kohli, Year of birth: 1988-11-05, Place of birth: Mumbai, Country: India id: 5, First name: Rohit, Last name: Sharma, Year of birth: 1987-04-30, Place of birth: Nagpur, Country: India id: 6, First name: Ravindra, Last name: Jadeja, Year of birth: 1988-12-06, Place of birth: Nagpur, Country: India id: 7, First name: James, Last name: Anderson, Year of birth: 1982-06-30, Place of birth: Burnley, Country: England Contents of the ResultSet in reverse direction id: 7, First name: James, Last name: Anderson, Year of birth: 1982-06-30, Place of birth: Burnley, Country: England id: 6, First name: Ravindra, Last name: Jadeja, Year of birth: 1988-12-06, Place of birth: Nagpur, Country: India id: 5, First name: Rohit, Last name: Sharma, Year of birth: 1987-04-30, Place of birth: Nagpur, Country: India id: 4, First name: Virat, Last name: Kohli, Year of birth: 1988-11-05, Place of birth: Mumbai, Country: India id: 3, First name: Kumara, Last name: Sangakkara, Year of birth: 1977-10-27, Place of birth: Matale, Country: Srilanka id: 2, First name: Jonathan, Last name: Trott, Year of birth: 1981-04-22, Place of birth: CapeTown, Country: SouthAfrica id: 1, First name: Shikhar, Last name: Dhawan, Year of birth: 1981-12-05, Place of birth: Delhi, Country: India
405 Views
