Java JDBC ResultSet Example (original) (raw)

In this example, we are going to demonstrate how to use Java JDBC 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.

You can also check this tutorial in the following video:

Java JDBC Tutorial – video

1. Why we use ResultSet interface

A ResultSet is a table of data representing a database result set, which is usually generated by executing a statement that queries the database. A ResultSet object maintains a cursor pointing to its current row of data, initially positioned before the first row. The ResultSet interface provides getter methods for retrieving column values from the current row. Values can be retrieved using the column name or the index number of the column. The usual syntax to get a ResultSet is as shown below:

Connection conn = DriverManager.getConnection(database specific URL); Statement stmt = conn.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability); ResultSet rSet = stmt.executeQuery("query to execute passed as String"); while(rSet.next()){ // Fetch the results and perform operations ... }

A ResultSet is obtained after executing a query. Connection and Statement will need to be created before execution.

As shown above, the attributes that are set while creating a ResultSet are:

1. Type (resultSetType): This indicates how a cursor in a result set can iterate. Possible values are TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE.
2. Concurrency (resultSetConcurrency): This indicates a constant for concurrency mode and can take these values – CONCUR_READ_ONLY, CONCUR_UPDATABLE.
3. Holdability (resultSetHoldability): To indicate whether cursor to hold or close after commit. Possible values are CLOSE_CURSORS_AT_COMMIT, HOLD_CURSORS_OVER_COMMIT.

2. Categories in ResultSet

ResultSet has methods that can be categorized under 3 categories:

• Navigational methods – to navigate through the result set. Sample methods are beforeFirst(), afterLast(), first(), last(), next(), previous().
• Get methods – contains methods to inquire about current position of result set like getRow(), isAfterLast(), isBeforeFirst(), isFirst(), isLast().
• Update methods – contains a method refreshRow() to refresh value with the latest values.

3. Technologies used

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

4. 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.

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:

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.

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:

Let’s get to the code then!

ResultSetExample.java

Output

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:

to this:

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

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.

6. Download the source code

This was an example of how to use JDBC ResultSet in Java.

Last updated on Jul. 06th, 2020

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.