Java Result Sets

[chili5]

Result Sets

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:

ID: 2 Name: Joe Registered on: 2009-08-29
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:

ID: 1 Name: James Date: 2009-08-11
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.

[Jordan]

Excellent read! +rep

[WingedPanther]

Very cool! +rep

This is similar to what I do with Delphi and databases.

[BlaineSch]

With desktop apps like this, would it be possible to reverse it somehow and get the db user/pass?

In update() can you simply return the thing instead of assigning it to a variable that you return?

I realize your not doing this for rep but...

You must spread some Reputation around before giving it to chili5 again.

Haha, I just realized you have the most rep in CodeCall!

[chili5]

Very cool! +rep

This is similar to what I do with Delphi and databases.

What do you do with Delphi? It would be interesting to see how other languages do it.

Yes, you can easily reverse this to get the password. Though the user running the program would presumably know the password? Since the user would have to install there own database server.

Not sure what you mean regarding the update methods?

[arnes99]

Hi chili5, I made a topic on a Java forum but since nobody is answering I thought you might know this:
How do I take data from ResultSet object and place it into a JTable object?
I know how to create JTable object which has two arguments data + header.
Data is some two-dimensional array of some type. Header is just a one-dimensional array. How do I somehow convert data from ResultSet to two-dimensional array so I can use it to create a JTable object? I'm just starting to learn database access and GUI so you would help alot. Thanx!

[chili5]

You have to create your own table mode. I was gonna write about it; but never did have time. I found this a pain to get working but it really isn't that complicated.

import java.util.ArrayList;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.TableModel;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CustomerSearchModel implements TableModel {

    /**
     * List of event listeners. These listeners wait for something to happen
     * with the table so that they can react. This is a must!
     */
    private ArrayList<TableModelListener> listeners = new ArrayList<TableModelListener>();
    /**
     * Holds the data in the table.
     */
    private Object[][] data;
    
    /**
     * The names of all the columns in the table. 
     */
    private String[] columnNames = new String[]{"ID"};
    

    private int nRows, nCols;

    public CustomerSearchModel() {
        
         data = new Object[15][1]; // assuming your table has 15 rows and 1 column
        String sQuery = "SELECT * FROM table";
         addData(sQuery);
    }

    /**
     * Adds a listener to the list that is notified each time a change to the
     * data model occurs.
     *
     * @param l
     *                      the TableModelListener
     */
    @Override
    public void addTableModelListener(TableModelListener l) {
        if (listeners.contains(l)) {
            return;
        }
        listeners.add(l);
    }

    /**
     * Returns the most specific superclass for all the cell values in the
     * column. This is used by the JTable to set up a default renderer and
     * editor for the column.
     *
     * @param columnIndex
     *                      the index of the column
     * @return the common ancestor class of the object values in the model.
     */
    @Override
    public Class<?> getColumnClass(int columnIndex) {
        switch (columnIndex) {
            case 0:
                return Integer.class;
        }
        if (columnIndex >= 1 && columnIndex <= 9) {
            return String.class;
        }
        return null;
    }

    /**
     * Returns the number of columns in the model. A JTable uses this method to
     * determine how many columns it should create and display by default.
     *
     * @return the number of columns in the model
     * @see #getRowCount
     */
    @Override
    public int getColumnCount() {
        return this.columnNames.length;
    }

    /**
     * Returns the name of the column at columnIndex. This is used to initialize
     * the table's column header name. Note: this name does not need to be
     * unique; two columns in a table can have the same name.
     *
     * @param columnIndex
     *                      the index of the column
     * @return the name of the column
     */
    @Override
    public String getColumnName(int columnIndex) {
        return columnNames[columnIndex];
    }

    /**
     * Returns the number of rows in the model. A JTable uses this method to
     * determine how many rows it should display. This method should be quick,
     * as it is called frequently during rendering.
     *
     * @return the number of rows in the model
     * @see #getColumnCount
     */
    @Override
    public int getRowCount() {
        return data.length;
    }

    /**
     * Returns the value for the cell at columnIndex and rowIndex.
     *
     * @param rowIndex
     *                      the row whose value is to be queried
     * @param columnIndex
     *                      the column whose value is to be queried
     * @return the value Object at the specified cell
     */
    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        return data[rowIndex][columnIndex];
    }

    /**
     * Returns true if the cell at rowIndex and columnIndex is editable.
     * Otherwise, setValueAt on the cell will not change the value of that cell.
     *
     * @param rowIndex
     *                      the row whose value to be queried
     * @param columnIndex
     *                      the column whose value to be queried
     * @return true if the cell is editable
     * @see #setValueAt
     */
    @Override
    public boolean isCellEditable(int rowIndex, int columnIndex) {
        

        return false;
    }

    /**
     * Removes a listener from the list that is notified each time a change to
     * the data model occurs.
     *
     * @param l
     *                      the TableModelListener
     */
    @Override
    public void removeTableModelListener(TableModelListener l) {
        listeners.remove(l);
    }

    /**
     * Sets the value in the cell at columnIndex and rowIndex to value.
     *
     * @param value
     *                      the new value
     * @param rowIndex
     *                      the row whose value is to be changed
     * @param columnIndex
     *                      the column whose value is to be changed
     * @see #getValueAt
     * @see #isCellEditable
     */
    @Override
    public void setValueAt(Object value, int rowIndex, int columnIndex) {
        data[rowIndex][columnIndex] = value;
        TableModelEvent event = new TableModelEvent(this, rowIndex,
                rowIndex, columnIndex, TableModelEvent.UPDATE);
        /*
         * Table has been changed, you must inform listeners about that, because
         * you won't see any change otherwise.
         */
        for (TableModelListener l : listeners) {
            l.tableChanged(event);
        }
    }


    public void addData(String sQuery) {
        try {
            ResultSet rs = null;
            int nRows;
            
            rs = menu.dbConn.results(sQuery);
            if (!rs.next()) {
                return; // no results in the database
            }
            rs.last();
            nRows = rs.getRow();
            rs.first();

            // add the data from the database to the table.
            // start the indices at 1 because the indices in the result set
            // start at 1. The table indices start 0 which is why we subtract one.
            for (int x = 1; x <= nRows; x++) {
                for (int y = 1; y <= nCols; y++) {
                    if (y == 1) {
                        // the first field is an integer.
                        data[x-1][y-1] = rs.getInt(1);
                    } 
                }
                rs.next(); // go to the next row in the result set
            }
            rs.close();
        } catch (SQLException ex) {
            errors.sqlError(ex);
        }
    }
}

You have to apply a table model similar to that, to your table. That is what I used to do this. I don't remember if you can just add that to your table and it will work but it should.

Send a PM with more detailed info, if you need more help.

[arnes99]

Send a PM with more detailed info, if you need more help.

It seems you set your profile so that you can't receive messages.
Here's my try which doesn't work:

import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestTable {
	public static void main(String[] args) {
		String[] header = new String[]{"Name", "Last Name",
				"Phone"};
		Object[][] data = null;
		Connection con = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql:///test",
			        "username", "password");
			if(!con.isClosed()){
				String sqlUpit = "SELECT * FROM someTable";
				Statement s = con.createStatement();
				ResultSet rs = s.executeQuery(sqlUpit);
				int numRows;
				if(!rs.next()){
					return;
				}
				rs.last();
				numRows = rs.getRow();
				rs.first();
				data = new Object[numRows][3];
				
				for(int i = 0; i < numRows; i++){
					for(int j = 0; j < 3; j++){
						data[i][j] = rs.getString(j + 1);
					}
					rs.next();
				}
			}
			con.close();
		}
		catch(ClassNotFoundException ex){
			System.err.println("Exception: " + ex.getMessage());
		}
		catch(SQLException ex){
			System.err.println("Exception: " + ex.getMessage());
		}
		JTable table = new JTable(data, header);
		JFrame frame = new JFrame("Database2Table");
		frame.getContentPane().add(new JScrollPane(table));		
		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frame.setSize(500, 350);
		frame.setVisible(true);
	}
}

Here are the errors:

Exception: com.mysql.jdbc.Driver
Exception in thread "main" java.lang.NullPointerException
at javax.swing.JTable$1.getRowCount(Unknown Source)
at javax.swing.JTable.getRowCount(Unknown Source)
at javax.swing.plaf.basic.BasicTableUI.createTableSiz e(Unknown Source)
at javax.swing.plaf.basic.BasicTableUI.getPreferredSi ze(Unknown Source)
at javax.swing.JComponent.getPreferredSize(Unknown Source)
at javax.swing.ScrollPaneLayout.layoutContainer(Unkno wn Source)
at java.awt.Container.layout(Unknown Source)
at java.awt.Container.doLayout(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validate(Unknown Source)
at java.awt.Window.show(Unknown Source)
at java.awt.Component.show(Unknown Source)
at java.awt.Component.setVisible(Unknown Source)
at java.awt.Window.setVisible(Unknown Source)
at TestTable.main(TestTable.java:72)
Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException
at javax.swing.JTable$1.getRowCount(Unknown Source)
at javax.swing.JTable.getRowCount(Unknown Source)
at javax.swing.plaf.basic.BasicTableUI.createTableSiz e(Unknown Source)
at javax.swing.plaf.basic.BasicTableUI.getPreferredSi ze(Unknown Source)
at javax.swing.JComponent.getPreferredSize(Unknown Source)
at javax.swing.ScrollPaneLayout.layoutContainer(Unkno wn Source)
at java.awt.Container.layout(Unknown Source)
at java.awt.Container.doLayout(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validateTree(Unknown Source)
at java.awt.Container.validate(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilter s(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(U nknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarch y(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Eclipse IDE doesn't show any errors during writing of code and I can start this little program but it shows these errors in the console. However, the program is running but no sign of GUI. My database table is a simple 4 column table but I chose to use only last three without id column.
Any idea?

[chili5]

Your code works for me. I think the problem is that you don't have the mysql connector set up correctly.

Have a look at this: Installing and Configuring MySQL Database and Connector/J JDBC Driver on Microsoft Windows and try following that and see if that fixes your problem.

Your code runs perfectly on my computer.

package test;
import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
	public static void main(String[] args) {
		String[] header = new String[]{"Name", "Last Name",
				"Phone"};
		Object[][] data = null;
		Connection con = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql:///test",
			        "root", "james");
			if(!con.isClosed()){
				String sqlUpit = "SELECT * FROM someTable";
				Statement s = con.createStatement();
				ResultSet rs = s.executeQuery(sqlUpit);
				int numRows;
				if(!rs.next()){
					return;
				}
				rs.last();
				numRows = rs.getRow();
				rs.first();
				data = new Object[numRows][3];

				for(int i = 0; i < numRows; i++){
					for(int j = 0; j < 3; j++){
						data[i][j] = rs.getString(j + 2);
					}
					rs.next();
				}
			}
			con.close();
		}
		catch(ClassNotFoundException ex){
			System.err.println("Exception: " + ex.getMessage());
		}
		catch(SQLException ex){
			System.err.println("Exception: " + ex.getMessage());
		}
		JTable table = new JTable(data, header);
		JFrame frame = new JFrame("Database2Table");
		frame.getContentPane().add(new JScrollPane(table));
		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frame.setSize(500, 350);
		frame.setVisible(true);
	}
}

[arnes99]

Your code works for me. I think the problem is that you don't have the mysql connector set up correctly.

You are totally right. Ižcan't believe I forgot to setup my mysql connector. Works good! Thank you for your help!