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 to retrieve the contents of a ResultSet from last to first in JDBC?
ResultSet object
Certain SQL queries (especially SELECT) returns tabular data, In JDBC the object of the java.sql.ResultSet interface holds the tabular data returned by the methods that execute the statements which quires the database (executeQuery() method of the Statement interface in general).
ResultSet Cursor/pointer
The ResultSet object has a cursor/pointer which points to the current row. Initially this cursor is positioned before first row.
There are two types of result sets namely, forward only and, bidirectional. By default the ResultSet we get by the executeQuery() method is of the type forward only. Using this you can traverse/move the cursor only forward direction.
Bidirectional ResultSet
A bi-directional ResultSet object is the one whose cursor moves in both forward and backward directions. The createStatement() method of the Connection interface has a variant which accepts two integer values representing the result set type and the concurrency type.
Statement createStatement(int resultSetType, int resultSetConcurrency)
To create a bi-directional result set you need to pass the type as ResultSet.TYPE_SCROLL_SENSITIVE or ResultSet.TYPE_SCROLL_INSENSITIVE, along with the concurrency, to this method as −
//Creating a Statement object Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
Then, if you invoke executeQuery() method using this statement it returns a ResultSet object which is bi-directional.
Printing the contents from last
The previous() method of the ResultSet interface moves the pointer of this ResultSet object to the previous row, from the current position.
This method returns a boolean value specifying whether the ResultSet object contains more rows.
If there are no rows previous to its current position this method returns false, else it returns true.
The Getter methods of the ResultSet interface (getInt(), getString() etc..) accepts the column index and returns the value of the specified column in the current row.
Using these methods, you can retrieve the contents of a ResultSet object from first to last.
while(rs.previous()) {
System.out.print("Brand: "+rs.getString("Mobile_Brand")+", ");
System.out.print("Sale: "+rs.getString("Unit_Sale"));
System.out.println("");
}Let us create a table with name mobile_sales in MySQL database using CREATE statement as shown below −
CREATE TABLE mobile_sales ( mobile_brand VARCHAR(255), unit_sale INT );
Now, we will insert 11 records in mobile_sales table using INSERT statements −
insert into mobile_sales values('Iphone', 3000);
insert into mobile_sales values('Samsung', 4000);
insert into mobile_sales values('Nokia', 5000);
insert into mobile_sales values('Vivo', 1500);
insert into mobile_sales values('Oppo', 900);
insert into mobile_sales values('MI', 6400);
insert into mobile_sales values('MotoG', 4360);
insert into mobile_sales values('Lenovo', 4100);
insert into mobile_sales values('RedMI', 4000);
insert into mobile_sales values('MotoG', 4360);
insert into mobile_sales values('OnePlus', 6334);Following example establishes connection with the database and retrieves the contents of the table mobile_sales from last to first.
Example
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BidirectionalResultSet {
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/TestDB";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Creating a Statement object
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//Retrieving the data
ResultSet rs = stmt.executeQuery("select * from mobile_sales");
//Moving the cursor to the end of the ResultSet
rs.afterLast();
System.out.println("Contents of the table");
while(rs.previous()) {
System.out.print("Brand: "+rs.getString("Mobile_Brand")+", ");
System.out.print("Sale: "+rs.getString("Unit_Sale"));
System.out.println("");
}
}
}Output
Connection established...... Contents of the table Brand: Vivo, Sale: 1500 Brand: Nokia, Sale: 5000 Brand: Samsung, Sale: 4000 Brand: IPhone, Sale: 3000
446 Views
