Sign In
Create Account
Follow Us on Twitter
Subscribe on Youtube
Earlier I showed how to connect to databases using MySQL. This tutorial is going to go into more depth about the different things you can do with the ResultSet object. A ResultSet object is used to store return values from a SELECT query. You can then iterate over these results. However, the class provides several different methods that allow you to iterate in different ways.
Let us create a table. I made a test database and created a people table with these fields:
id
name
age
date
CREATE TABLE `test`.`people` ( `id` INT NOT NULL , `name` INT( 30 ) NOT NULL , `age` TINYINT NOT NULL , `date` DATE NOT NULL ) ENGINE = MYISAM ;
Now let us insert some data into the table.
INSERT INTO `test`.`people` (`id`, `name`, `age`, `date`) VALUES ('1', 'James', '15', '2009-08-11'), ('2', 'Joe', '8', '2009-08-29');
This inserts 2 records into the table.
For this tutorial I am going to use the same database connection class that I used last time. I am going to make a small modification so we can have more flexiblity with our result set.
The new database class:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class db {
private Connection conn;
private static Statement s;
private String sUser;
private String sPass;
private String sDB;
/**
*
* @param sUsername The user name used to connect to the database.
* @param sPassword The password used to connect to the database.
* @param sDB The name of the database to use.
*/
public db(String sUsername, String sPassword, String sDB) {
this.sPass = sPassword;
this.sUser = sUsername;
this.sDB = sDB;
createConn();
}
/**
* Create a connection to the database.
*/
private void createConn() {
// create Connection
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
// a connection is used to connect to a database ;)
// you need a database URL with the database name, username, password and database
this.conn = DriverManager.getConnection("jdbc:mysql:///" + this.sDB, this.sUser, this.sPass);
s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE); // this statement is used to execute queries
} catch (SQLException e) {
errors.sqlError(e);
} catch (Exception e) {
errors.error(e);
}
}
public ResultSet results(String sQuery) throws SQLException {
// This method is used to execute SQL statements that return a result.
// such as SELECT
ResultSet r = null;
r = s.executeQuery(sQuery);
return r;
}
public void update(String sQuery) throws SQLException {
/**
* This method is used with statements that do not return a result.
* Such as insert, delete and update.
*/
s.executeUpdate(sQuery);
}
/**
* Closes the database connection.
* @throws java.sql.SQLException
*/
public void close() {
// close the database connection
// explicity clear the resources
// instead of waiting for mysql
// to close the connection
try {
s.close();
conn.close();
} catch (SQLException ex) {
errors.sqlError(ex);
}
}
}
The only thing I changed was the createConn method. I changed the statement object so it creates Result Sets so we can use the ResultSet to update the database and so we can iterate back and forth through the results.
s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE); // this statement is used to execute queries
The first constant TYPE_SCROLL_INSENSITIVE means that we can use methods like back and first and other methods to move back and forth through the results.
Connecting to the database
db dbConn = new db("username","pwd","db");
You'll need to replace those values in the constructor call with the appropriate information for your server.
Issuing the Query
We are going to send a select query to select all the data from the people table.
String sQuery = "SELECT * FROM people";
Now, we need to make a Result Set object to hold the results.
try {
ResultSet rs = dbConn.results(sQuery);
} catch (SQLException ex) {
}
The first thing we are going to do is just iterate and print the results to the output. You saw code like this in the last tutorial.
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id") + " Name: " + rs.getString("Name") + " Registered on: " + rs.getDate("date"));
}
The rs.next method returns false when the iterator is positioned after the result set.
The output:
Quote
ID: 2 Name: Joe Registered on: 2009-08-29
ID: 1 Name: James Registered on: 2009-08-11
ID: 1 Name: James Registered on: 2009-08-11
Now let us output things in the reverse order. We don't even have to reissue the query. Since we made the result set scrollable we can just scroll backwards. The iterator is already at the end of the result set. If it wasn't we could move it to the end by calling:
rs.afterLast();
This would position the cursor just after the last item. If you want to be positioned on the last row. You can use rs.last(). Similar methods include: rs.beforeFirst() and rs.first(). These methods position the cursor before the first row and on the first row respectively.
Now let us output items in the reverse order.
while (rs.previous()) {
System.out.println("ID: " + rs.getInt("id") + " Name: " + rs.getString("Name") + " Date: " + rs.getDate("date"));
}
The previous methods moves to the previous row. It will return false if the cursor is positioned before the firstItem.
Output:
Quote
ID: 1 Name: James Date: 2009-08-11
ID: 2 Name: Joe Date: 2009-08-29
ID: 2 Name: Joe Date: 2009-08-29
Absolute Movement
Using the rs.absolute method you can indicate what row you want to move to.
Example:
rs.absolute(3);
This method, moves to the 3rd row of the result set. The method will return false if the cursor is positioned before or after the result set.
Updating the databse
We also made the Result Set updatable so we can update the database by calling methods.
Consider the case where we want to change all the names to uppercase.
rs.beforeFirst();
while (rs.next()) {
rs.updateString("Name", rs.getString("Name").toUpperCase());
rs.updateRow();
System.out.println(rs.getString("Name"));
}
For this code to work, the select query must select all the primary keys from the table. You can make any changes you want in the Result Set but they are not updated in the database until you call rs.updateRow().
This provides a quick and cleaner way to update databases. Update statements can become really messy when the user is inputting all the values. You will have a ugly string with lots of joining strings.
Back to top
