Ilias Koutsakis

About Ilias Koutsakis

Ilias has graduated from the Department of Informatics and Telecommunications of the National and Kapodistrian University of Athens. He is interested in all aspects of software engineering, particularly data mining, and loves the challenge of working with new technologies. He is pursuing the dream of clean and readable code on a daily basis.

Java JDBC ResultSet Example

With this example we are going to demonstrate how to use ResultSet in order to get and manipulate data from a database. ResultSet is essentially a table, which contains all the information that should be returned from a specific query, as well as some essential metadata.

For the purposes of this article, we are going to assume that the database in use is MySQL, since it is one of the most well-known and beginner friendly databases out there.  In general, we are using:
 
 
 

  • MySQL 5.6
  • Eclipse Luna
  • Java 7

1. Creating a new project and adding the driver to the build path

First of all download the JDBC driver needed for your database. In our case, you will need the MySQL Connector, which can be found and downloaded here. Select the Platform Independent option, and download the zip file which contains, among others, the MySQL Connector jar file which will be added in the build path. Right now, the official version which will be downloaded will contain the mysql-connector-java-5.1.31-bin file (which is the file that should be added to the project).

The page that you should see, when you try to download MySQL Connector.

The page that you should see, when you try to download MySQL Connector.

 

Afterwards, you need to create a new Eclipse project and add the connector to the build path, by right-clicking on the project -> Build Path -> Add External Archives, as shown in the image below:

eclipse-buildpath

The path you should follow to add the jar, as instructed above.

After that, you can see that the jar is added in the build path under the Referenced Libraries section, in the project structure.

2. ResultSet Example

There are certain steps to be taken in order to use ResultSet in Java:

  1. Load the MySQL driver to your program.
  2. Create a Connection to the database.
  3. Make a query using a Statement.
  4. Get the ResultSet and manipulate the data as needed.

For this example we assume that we have a local database running, named “albums“, which contains a table named “the_classics”. The table contains the following:

nameartistyear
The Black AlbumMetallica1991
The White AlbumThe Beatles1968
Rock in RioIron Maiden2001

Let’s get to the code then!

ResultSetExample.java

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

public class ResultSetExample {

    public static void main(String[] args) {
        // The credentials that we need to have available for the connection to the database.
        String username = "myusername";
        String password = "mypassword";
        String databaseName = "albums";

        Connection connect = null;
        Statement statement = null;

        try {
            // Load the MySQL driver.
            Class.forName("com.mysql.jdbc.Driver");

            // Setup the connection to the database.
            // Take notice at the different variables that are needed here:
            //		1. The name of the database and its location (currently localhost)
            //		2. A valid username/password for the connection.
            connect = DriverManager.getConnection("jdbc:mysql://localhost/"
                    + databaseName + "?"
                    + "user=" + username
                    + "&password=" + password);

            // Create the statement to be used to get the results.
            statement = connect.createStatement();

            // Create a query to use.
            String query = "SELECT * FROM the_classics ORDER BY year";

            // Execute the query and get the result set, which contains
            // all the results returned from the database.
            ResultSet resultSet = statement.executeQuery(query);

            // We loop through the rows that were returned, and we can access the information
            // depending on the type of each column. In this case:
            //		Album: Varchar, so we use getString().
            //		Artist: Also Varchar, so getString() again.
            //		Year: Int, so we use getInt().
            // For other types of columns, such as boolean or Date, we use the appropriate methods.
            while (resultSet.next()) {
                System.out.println("Printing result...");

                // Now we can fetch the data by column name, save and use them!
                String albumName = resultSet.getString("name");
                String artist = resultSet.getString("artist");
                int year = resultSet.getInt("year");

                System.out.println("\tAlbum: " + albumName + 
                        ", by Artist: " + artist + 
                        ", released in: " + year);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // We have to close the connection and release the resources used.
            // Closing the statement results in closing the resultSet as well.
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                connect.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Output:

Printing result...
	Album: The White Album, by Artist: The Beatles, released in: 1968
Printing result...
	Album: The Black Album, by Artist: Metallica, released in: 1991
Printing result...
	Album: Rock in Rio, by Artist: Iron Maiden, released in: 2001

As stated above, the ResultSet class has different methods for acquiring different kinds of data, depending on the column type in the table. It is very important to know what kind of data you want to retrieve, because in case of a mismatch, an exception will be thrown. For example, in the above code, if we change this line:

String albumName = resultSet.getString("name");

to this:

int albumName = resultSet.getInt("name");

we will immediately get an exception when we try to retrieve the first result.

Printing result...
java.sql.SQLException: Invalid value for getInt() - 'The White Album'
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928)
	at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2821)
	at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2846)
	at ResultSetExample.main(ResultSetExample.java:51)

Generally, you need to be sure about the data types and use the right method. Some of the most common MySQL – Java relations are these:

  • Varchar -> getString(columnName): String
  • Char ->  getString(columnName): String
  • Integer  -> getInt(columnName): int
  • Float  -> getFloat(columnName): float
  • Boolean -> getBoolean(columnName): boolean
  • Date -> getDate(columnType): Date

In some cases, you can use the getString(columnName) method to get the String representation of numbers, like Integer or Float, but this course of action is better to be avoided.

3. Download the source code

This was an example of how to use ResultSet in Java.
You can download the eclipse project of this tutorial here: ResultSetExample.

Related Whitepaper:

Java Essential Training

Author David Gassner explores Java SE (Standard Edition), the language used to build mobile apps for Android devices, enterprise server applications, and more!

The course demonstrates how to install both Java and the Eclipse IDE and dives into the particulars of programming. The course also explains the fundamentals of Java, from creating simple variables, assigning values, and declaring methods to working with strings, arrays, and subclasses; reading and writing to text files; and implementing object oriented programming concepts. Exercise files are included with the course.

Get it Now!  

Examples Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Examples Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Examples Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.

Sign up for our Newsletter

20,709 insiders are already enjoying weekly updates and complimentary whitepapers! Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

As an extra bonus, by joining you will get our brand new e-books, published by Java Code Geeks and their JCG partners for your reading pleasure! Enter your info and stay on top of things,

  • Fresh trends
  • Cases and examples
  • Research and insights
  • Two complimentary e-books