Recent Pos

Working with JDBC ResultSet object


⇒ The ResultSet object is a JDBC driver object which holds a database cursor pointing to its current row of data. Initially the ResultSet cursor is positioned before the first row. The next method of ResultSet object moves the cursor to the next row, and because the ResultSet next() method returns false when there are no more rows in the object, it can be used in a while loop to iterate through the ResultSet.

⇒ Basically the JDBC Statement and its child object’s executeQuery() method returns the ResultSet object.

The executeQuery(String sql) method:

⇒ This method of Statement is used to execute the SELECT query.

⇒ This method returns java.sql.ResultSet type of object.

DB cursor:

⇒ The DB cursor (Current set of records) is a buffer where the result of the SELECT query execution is started.

jdbc resultset example

⇒ When the SELECT query is given to the database server to query, it requires to read the described data from the database and store it into the buffer. This buffer store is referred as DB cursor, on successfully doing all these the database server returns Cursor_id to the client.

⇒ In case of working with JDBC the executeQuery() method of Statement object will request DB server for executing the SELECT query on receiving the cursor_id it creates a new object of ResultSet setting the Cursor_id to it thereafter the ResultSet object is returned to client. The above diagram shows the process.

Till now we understood that the ResultSet object is to represent the database Cursor. Hence we want to use the ResultSet object to read the data from the database cursor.

⇒ The following two steps are involved in using ResultSet object:

Step 1: Move the row pointer

Step 2: Read the column value

Moving the row pointer:

⇒ The ResultSet object supports the following method to move the row pointer.

1. boolean next()

-This method will move the row pointer to the next row from the current position.

-This method will return false if the pointer move to after last row.

Note: Initially when the ResultSet object is created the row position will be at Before First Row.

2. boolean previous()

3. boolean first()

4. boolean last()

5. boolean absolute()

6. boolean relative()

7. void beforeFirst()

8. void afterLast()

Note: The above 7 methods are available to use only with Scrollable ResultSet.

Reading the column values:

⇒ The following methods of ResultSet object can be used to read the column value.

This returns the value of the given column at the current row pointer in the form of String. We are passing current cursor’s column name as argument.

Same as above but returning the value in the form of double.

⇒ Similarly we have get method for all the datatypes supported by Java.

Note: The column name used with these methods are the names as per the Cursor table, not the actual table in the database.

⇒ Alternatively we can use the following methods by passing column index number to get the column value:

⇒ Similarly for all the datatypes supported by Java.

Note: The col_index starts from 1.

⇒ The col_index is as per the cursor_table not the actual table

Now we have seen two option to read column value:

1. Using column name

2. Using column index

⇒ It is recommended to use col_index instead of col_name, because using col_name will increase the overhead as it has to internally find the col_index and use the index for getting value which is an overhead to the machine.

Sample internal code of ResultSet:

Example application – Requirement:
⇒ We want to write a program to read all the records from the database table and print them in a table format on standard o/p console.


<-Previous: Factory Design Pattern Implementation Inside DAO Example | | Next->Value Object or Data Transfer Object (DTO) design pattern

1 Comment

Post a Comment