Developer's Daily Java Education
  front page | java | perl | unix | DevDirectory
   
Front Page
Java
Education
Pure Java
Articles
   
 
JDBC 103: Creating and executing an SQL query with JDBC
 

Introduction

In our first article we demonstrated how to connect your Java applets and applications to standard SQL databases like Oracle, Informix, Sybase, and others.  In our examples we showed how to connect to two different databases --  Mini SQL (mSQL), and Interbase -- just so you can see how the code changes when you switch from one database to another.

In this article we'll take the next step -- we'll show you how to create and execute an SQL statement in your Java code.
 

Our sample database

Before getting into our SQL statements, you need to know what our database table looks like.  In all of our examples, we're going to be accessing a database named Demo.  In today's example, we're going to be accessing a database table named Customers, that's contained in the Demo database.

Here's what the Customers database 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 query a JDBC database

Today we'll show you that querying an SQL database with JDBC is often a simple three step process.  The three steps are:

    1. Create a ResultSet object.
    2. Execute the 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 the query.


 
Creating a valid SQL query

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

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

Okay, 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 code necessary to create and execute our query:

JDBC returns query results in a ResultSet object, so we first create the object rs to hold our results.  This creates the object, but at this point no query has been defined.  In the second statement we specify the desired SQL query and send it to the SQL database.
 

Reading the results

After we execute the query, how do we read the results? Fortunately, JDBC has made 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:

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: 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.
 
 
The Query1.java program

 The full source code for the Query1.java program we created is shown in Listing 1.
 

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

    import java.sql.*;

    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());
            }
        }
    }
 
Listing 1: 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 the source code

If you're interested, you can click here to download the source code for the Query1.java program.  You can test the 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.
 

Resources mentioned in this article

Here are a few links to resources we mentioned in this article:

 

What's Related


Copyright 1998-2008 DevDaily Interactive, Inc.
All Rights Reserved.