Java JDBC - a SQL SELECT query example

Java SELECT query FAQ: Can you share an example of a SQL SELECT query using the standard JDBC syntax?

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

In this SELECT query tutorial we'll take JDBC to the next step -- we'll show you how to create and execute a SQL SELECT statement in your Java code.

JDBC SELECT query: Our sample database

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

In all of our examples in this tutorial series we're going to access a database named "Demo", and in these SELECT query examples we're going to be accessing 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
1001 Simpson Mr. Springfield 2001
1002 MacBeal Ms. Boston 2004
1003 Flinstone Mr. Bedrock 2003
1004 Cramden Mr. New York 2001

Table 1: Our sample Customers database table will contain these four sample records.

How to perform a JDBC SELECT query against a database

Querying a SQL database with JDBC is typically a simple 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 SELECT query.

Creating a valid SQL SELECT query

In today's example, we'll create a simple SQL SELECT query.  We'll keep the statement simple, like this:

SELECT Lname FROM Customers
WHERE Snum = 2001;

This statement returns each Lname (last name) record from our 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 our query:

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

In the first step, we create a Java Statement objection from our Connection object. 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. Our ResultSet object rs now contains the results from our database query.

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

After we execute our SQL query, how do we read the results? Fortunately, 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 reading loop like this:

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:

Simpson
Cramden

because these are the last names of the two customer records where Snum equals 2001.

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());
        }
    }
}

The source code for the Query1.java program shows how to query an SQL database for the information you want, using Java JDBC methods.

Download our example JDBC select program

If you're interested, you can download the Java source code for our Query1.java program.  You can test this JDBC example code on your own system, but note that you'll need to change the lines where we create our url and conn objects to reflect your own database configuration.

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.

There is a *lot* of newer Java and JDBC content on my blog, including these JDBC tips:

Wrong code!

hi Alvin,

as from your code snippet...

ResultSet rs = conn.createStatement();
st.executeQuery("select Lname from Customers where Snum = 2001");

dont u think this code is never going to execute...it wont compile first of all
because conn.createStatement() returs a Statement and not a ResultSet.
you will get a ResultSet only at line no 2 when you do st.executeQuery(String sql).

let me know your thoughts!

Regards

Rakesh

Thanks - ResultSet/Statement code corrected

I will say thank you, you found an 11-year-old bug. The code in the complete class was correct, but the snippet of code I showed earlier was wrong, and I just corrected it. Thanks again!

JDBC select

Thank you for your "JDBC Select" example. It was very helpful to me in learning Java and JDBC.

broken link

The link for : "If you're interested, you can click here to download the source code for the Query1.java program" is broken.

Peter

JDBC Query link fixed

Thanks for finding this broken link, it should be fixed now.

Thanks a lot...was very

Thanks a lot...was very helpful

Post new comment

The content of this field is kept private and will not be shown publicly.