Data Connectivity Resource Center is brought to you in partnership with:

Masoud Kalali has a software engineering degree and has been working on software development projects since 1998. He has experience with a variety of technologies (.NET, J2EE, CORBA, and COM+) on diverse platforms (Solaris, Linux, and Windows). His experience is in software architecture, design, and server-side development. Masoud has published several articles at Java.net and Dzone. He has authored multiple refcards, published by Dzone, including but not limited to Using XML in Java, Java EE Security and GlassFish v3 refcardz. He is one of the founder members of NetBeans Dream Team and a GlassFish community spotlighted developer. Recently Masoud's new book, GlassFish Security has been published which covers GlassFish v3 security and Java EE 6 security. Masoud's main area of research and interest includes service-oriented architecture and large scale systems' development and deployment and in his leisure time he enjoys photography, mountaineering and camping. Masoud's can be followed at his Twitter account. Masoud has posted 82 posts at DZone. You can read more from them at their website. View Full User Profile

The ABCs of JDBC, Part 2 - ResultSets

05.03.2010
| 15188 views |
  • submit to reddit

Last week, Daniel Rubio provided an overview of the JDBC API. In this week's installment of our JDBC FAQ series, we examine the JDBC ResultSet object and provide an overview of some of the key methods that comprise it.

Read the other parts in DZone's JDBC FAQ series:

 

What is a ResultSet ?

A ResultSet is a tabular structure containing the dataset returned by executing a query statement. The structure contains both the data itself and a fair deal of metadata describing the containing data. Metadata includes column types, column names, along with other information.

A ResultSet object is read-only and forward-only by default meaning that we can only iterate from the beginning of the ResultSet toward its end and not vice-versa. The other default feature specifies that we can only read data from the ResultSet and not update, insert or delete data when the ResultSet is created using the default construction parameters.

The ResultSet object provides the necessary methods to:

  • iterate over the containing rows both forward and backward
  • read or update each column content
  • insert new rows into the ResultSet and finalyy into the underlying table
  • delete rows from the ResultSet and the underlaying table.
  • Read the metadata associated with the  ResultSet
  • jump between rows by providing the absolute row number or a relative difference.


How does one get column names for rows returned in a ResultSet ?

We can use the ResultSet metadata to get column names. For example assuming that we have the ResultSet we created in the first question, we can use the following snippet to get the column name for the first column in the ResultSet .

ResultSetMetaData rsmd = rs.getMetaData(); 
String value = rsmd.getColumnLabel(1); 



We can use the rsmd.getColumnCount() method to get the column count and then use a loop to retrieve all column names and use them to create a JTable data model or whatever is necessary.


How do I retrieve a whole row of data at once, instead of calling an individual  ResultSet.getX.X.X method for each column?

It is not possible to retrieve a whole row of a ResulSet in one call when using JDBC. The only way to retrieve a row's data from the result set is by means of the getter methods to retrieve the value of each column of the current row.



There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a ResultSet?

There is no way to find the number of rows in a ResultSet and even if such a method was present, the result would be inaccurate because the setFetchSize and setMaxRows methods would affect the number rows present in the ResultSet .

Best way to get number of rows for a particular statement is executing another query, applying count function on the main query.

 

What does setFetchSize() do?

We can use the SetFetchSize method to change the amount of data blocks or rows that should be carried to the client from the server after executing a statement. This method may act differently on different drivers coming from different vendors.  The  SetFetchSize default is vendor dependent and requires that you consult the vendor’s documentation to see what is its default behavior.

Both ResultSet and Statement have the SetFetchSize method. When used in the Statement all of the ResultSets returned by that Statement will have the same fetch size.

 

What is the difference between setMaxRows(int) and SetFetchSize(int)? Can either reduce processing time?

The  setMaxRows method of the ResultSet specifies how many rows a ResultSet can contain at a time. Any number of rows more than the specified number will be dropped and won’t load into the ResultSet .

Different vendors use different strategies to implement these methods. The default for  setMaxRows is 0 which means all rows will be included in the ResultSet .

So the differences are in the side that method invocation affects: the setMaxRows affects the client side JDBC object while the setFetchSize affects how the database returns the ResultSet data.

 

How can I update a ResultSet programmatically?

When we say updating we may simply mean updating a record, inserting a record or deleting a record from a ResultSet and then accepting the change to the underlying table. Before getting in to the sample code we must know that all conditions applied to creating an updatable ResultSet apply here as well.

To update a record in the ResultSet we need to move the cursor over the record and then use the setXXX methods to update the columns. For example:

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("SELECT name ,lastName ,sn FROM table_1");

rs.absolute(5); //moving the cursor to the first row
rs.updateString("name", “john”); // update the name field of the current row
rs.updateRow(); //persist the change


We may iterate over the ResultSet and update the records or simply revive the updates from a JTable change.

To delete a record, we just need to move the cursor to record we want to delete and invoke the deleteRow method. For example:

rset.absolute(5); 
rset.deleteRow(); 


To insert a row we can use the following procedure to insert a row into the ResultSet and expect it to affect the underlying table.

rset.moveToInsertRow(); // prepare a new row


You can then use the updateXXX methods to set the value of each column in the ResultSet , for example:


rset.updateString(1, "John"); 
rset.updateString(2, "Doe"); 
rset.updateString(3, "sn15308798");


Finally, you can invoke the insertRow method to make the changes permanent:

rset.insertRow();  

 

How do I create an updatable ResultSet?

An updatable ResultSet allows us to update the ResultSet programmatically. Although almost all drivers support the updatable ResultSet, it is actually an optional feature for the JDBC driver vendors to implement the capability. To get an updateable ResultSet you need to set the concurrency type of the  ResultSet to   ResultSet.CONCUR_UPDATABLE when you create one. For example the following snippet shows how we can create one.

try {
Statement stmt = connection.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT name ,lastName ,sn FROM table_1");
}
catch (SQLException e) {
}


If a driver does not support the updatable ResultSet it will return a normal read-only ResultSet instead.

Some conditions apply to the updatable ResultSet which include:

  • The SELECT statement must have a single underlying table; no joins and no aggregation functions are allowed. This is similar to select for update clause in plain SQL.
  • The PK of the underlying table must be present in the ResultSet so it can propagate the changes to the correct row in the table.


Note that creating an updatable ResultSet has its overhead and we should only create one when we require it. Passing no parameter when we create a ResultSet means that we want a read-only ResultSet.


If you are curious to know some of the other types of ResultSets and the meaning of each of the CreateStatement method parameters, then following section  is for you:

The first argument in CreateStatement indicates the type of the ResultSet object :

  1. TYPE_FORWARD_ONLY: The ResultSet will only allow the cursor to go forward.
  2. TYPE_ SCROLL_INSENSITIVE: A ResultSet of this type does not reflect changes that are made to it while it is  open.
  3. TYPE_SCROLL_SENSITIVE: Any change made to the ResultSet while it is open will be reflected in the ResultSet itself.


The second argument in CreateStatement indicates whether the ResultSet is updatable or not. The following values are acceptable for this parameter:

  1. CONCUR_READ_ONLY: A  ResultSet of this type is readonly
  2. CONCUR_UPDATABLE :  We can update the resultset created with this value for the mentioned parameter.



How can I move the cursor in a scrollable ResultSet ?

A ResultSet created without passing the scrollability parameter will be a forward only ResultSet meaning that we can not navigate backward in the list or rows inside the ResultSet .

The following snippet creates a scrollable, updatable ResultSet.

try {
Statement stmt = connection.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT name, lastName, sn FROM table_1");
}
catch (SQLException e) {
}


We can iterate forward in this ResultSet using the following snippet:

while (rs.next()) {
String name = rs.getString("name");
String lastName = rs.getString ("lastName");
String sn = rs.getString(“sn”);
System.out.println(name + " " + lastName + “ ” +sn);
}


To iterate backward we can use the following snippet:

while (rs.previous()) {
String name = rs.getString("name");
String lastName = rs.getString ("lastName");
String sn = rs.getString(“sn”);
System.out.println(name + " " + lastName + “ ” +sn);
}



This snippet works provided that we are at the end of the ResultSet and we want to iterate back to the first row.  The following table shows a list of methods which helps us further utilize the scrollability:



Like updatability, the scrollability of a ResultSet imposes some overhead. If you don’t need to move backward in the ResultSet it is probably better to use the forward-only ResultSet.


Why can't I invoke the ResultSet methods afterLast and beforeFirst when the method next works?

The most probable case is that you are using a driver which does not support a scrollable ResultSet or the ResultSet is not created as a forward only ResultSet .

AttachmentSize
Figure1.png80.69 KB
Published at DZone with permission of its author, Masoud Kalali.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Gervais Blaise replied on Tue, 2010/05/04 - 2:00am

I don't know why, maybe it is a myth but I have in my brain a sentence who said "Modifying table values from a ResultSet is bad".

Oleg Mazurashu replied on Tue, 2010/05/04 - 3:47am

Hi. Thank you for this post :)

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.