java examples
Home Java Examples Resources Java Interview Questions
Brainpower SEO

How to retrieve values from ResultSet?

Here is another example how to read data from a ResultSet returned by a executing an SQL query to a table in database.

package com.javacoderanch.example.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ResultSetExample {
	public static void main(String[] args) throws Exception
	{
		Connection connection = getConnection();
		try {
			String query = "SELECT id, title, publisher, year, price FROM books";
			PreparedStatement ps = connection.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				// Read values using column name
				String id = rs.getString("id");
				String title = rs.getString("title");
				String publisher = rs.getString("publisher");
				
				// Read values using column index
				int year = rs.getInt(4);
				float price = rs.getFloat(5);
				
				System.out.printf("%s. %s, %s, %d, %fn", id, title, publisher, year, price);
			}
		} finally {
			closeConnection(connection);
		}
	}
	
	private static Connection getConnection() throws Exception {
		Connection connection = null;
		Class.forName("com.mysql.jdbc.Driver");
		connection = DriverManager.getConnection("jdbc:mysql://localhost/bookstore", "root", "");
		return connection;
	}
	
	private static void closeConnection(Connection connection) throws SQLException {
		if (connection != null && !connection.isClosed()) {			
			connection.close();
		}
	}
}

The code above can be read as the following: create a connection to the database, see the getConnection() method. Create a PreparedStatement to execute a query to get some date from the BOOKS table.

After statement execution you'll have a ResultSet object, the iterate all the data in the ResultSet you need to call the next() method. When no other record to read it return false. The ResultSet object rs also provides some methods to read value of the fields, the name of the method is corresponded to the type of data stored on each field of the table.

To read data using this methods (eg. getString(), getInt(), getFloat(), etc) you can either use the column name or the column index of the field read in the statement.