Java JDBC: A SQL SELECT query example

In the JDBC connection article I showed how to connect your Java applications to standard SQL databases like MySQL, SQL Server, Oracle, SQLite, and others using JDBC.  In those examples I showed how to connect to two different databases so you could see how little the code changes when you switch from one database to another.

In this SELECT query tutorial I'll take JDBC to the next step, showing how to create and execute a SQL SELECT statement in your Java code.

JDBC SELECT query: A sample database

Before looking at the SQL queries, let's take a quick look at our sample database.

In all of these examples I’ll access a database named "Demo", and in these SELECT query examples I’ll  access a database table named "Customers" that's contained in the Demo database.

Here's what the Customers table looks like:

Cnum	Lname	Salutation	City	Snum
101	Samuel	Mr.	Ejigbo	2010
102	Dawodu	Ms.	Isolo	2011
103	Okafor	Mrs.	Oshodi	2012
104	Ugochkwu	Miss.	Oke-Afa	2013

As you can see, the Customers table contains these four sample records. (I populated that data in my JDBC SQL INSERT tutorial.)

How to perform a JDBC SELECT query against a database

Querying a SQL database with JDBC is typically a three-step process:

1. Create a JDBC ResultSet object.
2. Execute the SQL SELECT query you want to run.
3. Read the results.

The hardest part of the process is defining the query you want to run, and then writing the code to read and manipulate the results of your SELECTquery.

Creating a valid SQL SELECT query

To demontrate this I’ll write this simple SQL SELECT query:

SELECT Lname FROM Customers
WHERE Snum = 2010;

This statement returns each Lname (last name) record from the Customers database, where Snum (salesperson id-number) equals 2001. In plain English, you might say "give me the last name of every customer where the salesperson id-number is 2001".

Now that we know the information we want to retrieve, how do we put this SQL statement into a Java program?  It's actually very simple. Here's the JDBC code necessary to create and execute this query:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT Lname FROM Customers WHERE Snum = 2010");

In the first step, I create a Java Statement objection from the Connectionobject. That's really just an intermediate step that lets us do what we want to do in the next step: Execute our query, and get a ResultSet object. The ResultSet object rs now contains the results from the database query. Now we can work with those results.

Reading the JDBC SELECT query results (i.e., a Java JDBC ResultSet)

After you execute the  SQL query, how do you read the results? Well, JDBC makes this pretty easy also.  In many cases, you can just use the next()method of the ResultSet object.  After the previous two lines, you might add a loop like this to read the results:

while (rs.next()) {
  String lastName = rs.getString("Lname");
  System.out.println(lastName + "\n");
}

This loop reads the last name returned in each record, and prints it to the screen using the normal System.out.println() method. In the case of our sample database, the printed results look like this:

Samuel

because these are the last names of the two customer records where Snumequals 201.

Notice that in this example all we're doing is printing our results.  In many JDBC applications, you'll probably want to do something else with the results, such as displaying them in a table or grid in a GUI applet or application.

Our JDBC SELECT query example program - Query1.java

The full source code for our example JDBC program (Query1.java) is shown in Listing 1.

//  Query1.java:  Query an mSQL database using JDBC. 

import java.sql.*;

/**
 * A JDBC SELECT (JDBC query) example program.
 */
class Query1 {
 
    public static void main (String[] args) {
        try {
            String url = "jdbc:msql://200.210.220.1:1114/Demo";
            Connection conn = DriverManager.getConnection(url,"","");
            Statement stmt = conn.createStatement();
            ResultSet rs;
 
            rs = stmt.executeQuery("SELECT Lname FROM Customers WHERE Snum = 2001");
            while ( rs.next() ) {
                String lastName = rs.getString("Lname");
                System.out.println(lastName);
            }
            conn.close();
        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
        }
    }
}

Conclusion

Querying an SQL database with JDBC is a simple three step process, once you know how to do it.  Just (1) create a ResultSet object, (2) execute the query, and then (3) read the results.