JAVA: Result Set

<< JAVA: More on JDBC

JAVA: Meta Data >>

Web Design & Development CS506

Lesson 16

Result Set

This handout will familiarize you with another technique of inserting, updating & deleting rows. Before

moving on, first we look at ResultSet.

ResultSet

A ResultSet contains the results of the SQL query

Represented by a table with rows and columns

Maintains a cursor pointing to its current row of data.

Initially the cursor positioned before the row (0).

First row has index 1

124

Web Design & Development CS506

-The ResultSet is implicitly closed when the associated Statement object executes a new query or

closed by method call.

Updatable and/or Scrollable ResultSet

It is possible to produce ResultSet objects that are scrollable and/or updatable (since JDK 1.2)

With the help of such ResultSet, it is possible to move forward as well as backward with in

RestultSetobject.

Another advantage is, rows can be inserted, updated or deleted by using updatable ResultSet object.

Creating Updatable & Scrollable ResultSet

The following code fragment, illustrates how to make a ResultSet object that is scrollable and updatable.

String sql = "SELECT * FROM Person";

PreparedStatement pStmt =

con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_U

PDATABLE);

ResultSet rs = pStmt.executeQuery( );

Two constants have been used of ResultSet class for producing a ResultSet rs that is scrollable, will not

show changes made by others and will be updatable

Useful ResultSet's Methods (cont.)

The methods discussed in this section can only be used with updatable/scrollable ResultSet object.

. previous( )

-Moves the cursor to the previous row in the ResultSet object, if available -Returns true if

cursor is on a valid row, false it is off the result set. -Throws exception if result type is

TYPE_FORWARD_ONLY.

Example Code 16.1: Use of previous( ), next( ) & various getters methods

The ResultSetEx.java shows the use of previous, next and getters methods. We are using the same Person

table of PersonInfo database, the one we had created earlier in this example and later on.

// File ResultSetEx.java

import java.sql.*;

public class ResultSetEx {

public static void main (String args[ ]) {

try {

//Step 2: load driver

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//Step 3: define the connection URL

String url = "jdbc:odbc:personDSN";

//Step 4: establish the connection

Connection con = DriverManager.getConnection(url);

//Step 5: creating PrepareStatement by passing sql and

//ResultSet's constants so that the ResultSet that will

//produce as a result of executing query will be

//scrollable & updatable

String sql = "SELECT * FROM Person";

PreparedStatement pStmt = con.prepateStatement(sql,

ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_UPDATABLE);

//Step 6: execute the query

ResultSet rs = pStmt.executeQuery();

// moving cursor forward i.e. first row

rs.next( );

// printing column "name" value of current row (first)

System.out.println("moving cursor forward");

String name = rs.getString("Name");

System.out.println(name);

125

Web Design & Development CS506

// moving cursor forward i.e. on to second row

rs.next( );

// moving cursor backward i.e to first row

rs.previous( );

// printing column "name" value of current row (first)

System.out.println("moving cursor forward");

name = rs.getString("Name");

System.out.println(name);

//Step 8: close the connection

con.close();

}catch(Exception sqlEx){

System.out.println(sqlEx);

}

} // end main

} // end class

Compile & Execute:

The sample output is given below:

Useful ResultSet's Methods (cont.)

absolute(int)

-Moves the cursor to the given row number in the ResultSetobject.

-If given row number is positive, moves the cursor forward with respect to beginning of the result set.

-If the given row number is negative, the cursor moves to the absolute row position with respect to the

end of the result set.

-For example, calling absolute(-1) positions the cursor on the last row; calling absolute(-2) moves the

cursor to next-to-last row, and so on.

-Throws Exception if ResultSet type is TYPE_FORWARD_ONLY

updaters (for primitives, String and Object)

-Used to update the column values in the current row or in insert row (discuss later)

-Do not update the underlying database

-Each update method is overloaded; one that takes column name while other takes column index. For

example String updater are available as:

updateString(String columnName, String value)

updateString(String columnIndex, String value)

updateRow( )

-Updates the underlying database with new contents of the current row of this ResultSetobject

Example Code 16.2: Updating values in existing rows

The following code example updates the Name column in the second row of the ResultSet object rs and

126

Web Design & Development CS506

then uses the method updateRow to update the Person table in database.

This code is the modification of the last one. Changes made are shown in bold face.

// File ResultSetEx.java

import java.sql.*;

public class ResultSetEx {

public static void main (String args[ ]) {

try {

//Step 2: load driver

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//Step 3: define the connection URL

String url = "jdbc:odbc:personDSN";

//Step 4: establish the connection

Connection con = DriverManager.getConnection(url);

//Step 5: create PrepareStatement by passing sql and

// ResultSet appropriate fields

String sql = "SELECT * FROM Person";

PreparedStatement pStmt = con.prepateStatement(sql,

ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_UPDATABLE);

//Step 6: execute the query

ResultSet rs = pStmt.executeQuery();

// moving cursor to second row

rs.absolute(2);

// update address column of 2 row in rs

rs.updateString("Address", "model town");

// update the row in database

rs.updateRow( );

//Step 8: close the connection

con.close();

}catch(Exception sqlEx){

System.out.println(sqlEx);

}

} // end main

} // end class

Compile & Execute

Given below are two states of Person table. Notice that address of 2 row is updated. Person table:

Before execution

127

Web Design & Development CS506

Person table: After execution

Useful ResultSet's Methods (cont.)

. moveToInsertRow(int)

-An updatable resultset object has a special row associate with it i.e.

insert row -Insert row a buffer, where a new row may be constructed by

calling updater methods. -Doesn't insert the row into a result set or into a

databse.

-For example, initially cursor is positioned on the first row as shown in the diagram.

-By calling moveToInsertRow( ), the cursor

128

Web Design & Development CS506

129

Web Design & Development CS506

ResultSet rs = pStmt.executeQuery();

// moving cursor to insert row

rs.moveToInsertRow();

// updating values in insert row

rs.updateString( "Name" , "imitiaz" );

rs.updateString( "Address" , "cantt" );

rs.updateString( "phoneNum" , "9201211" );

// inserting row in resultset & into database

rs.insertRow( );

//Step 8: close the connection

con.close();

}catch(Exception sqlEx){

System.out.println(sqlEx);

}

} // end main

} // end class

Compile & Execute

Given below are two states of Person table. Note that after executing program, a newly added row is

present.

Person table: Before execution

Person table: After execution

Useful ResultSet's Methods (cont.)

. last( ) & first( )

130

Web Design & Development CS506

the ResultSet is TYPE_FORWARD_ONLY

. getRow( )

-Returns the current row number

-As mentioned earlier, the first row has index 1 and so on.

. deleteRow( )

-Deletes the current row from this ResultSet object and from the underlying database.

-Throws exception if the cursor is on the insert row.

Example Code 16.4: Deleting existing row

The given below example code shows the usage of last( ), getRow( ) and deleteRow( ) method.

This code is also the modification of the last one. Changes made are shown in bold face.

// File ResultSetEx.java

import java.sql.*;

public class ResultSetEx {

public static void main (String args[ ]) {

try {

//Step 2: load driver

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//Step 3: define the connection URL

String url = "jdbc:odbc:personDSN";

//Step 4: establish the connection

Connection con = DriverManager.getConnection(url);

//Step 5: create PrepareStatement by passing sql and

// ResultSet appropriate fields

String sql = "SELECT * FROM Person";

PreparedStatement pStmt = con.prepateStatement(sql,

ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_UPDATABLE);

//Step 6: execute the query

ResultSet rs = pStmt.executeQuery();

// moves to last row of the resultset

rs.last();

// retrieving the current row number

int rNo = rs.getRow();

System.out.println("current row number" + rNo);

// delete current row from rs & db i.e. 4 because

// previously we have called last() method

rs.deleteRow( );

//Step 8: close the connection

con.close();

}catch(Exception sqlEx){

System.out.println(sqlEx);

}

} // end main

} // end class

Compile & Execute

The first diagram shows the Person table before execution. Person table: Before execution

131

Web Design & Development CS506

Execution program from command prompt will result in displaying current row number on console. This

can

confirmed

from

following

diagram.

After execution, the last row (4) is deleted from ResultSet as well as from database. The Person table is

shown after execution

Person table: After execution

References:

Entire material for this handout is taken from the book JAVA A Lab Course by Umair Javed. This

material is available just for the use of VU students of the course Web Design and Development and not for

any other commercial purpose without the consent of author.

132

Table of Contents: