XML and Java: Developing Web Applications, Second Edition

D.5 Submitting a Query

Once a connection is established, we can submit queries to the database. However, we first need to create a Statement object by calling the createStatement() method of the Connection object:

Statement stmt = con.createStatement();

Now we are ready to submit an SQL query. You may need to be familiar with SQL to do this. Call the executeQuery() method with an SQL statement as its only argument, and you get the Resultset object that contains the result. In this case, all records in the employee database are returned.

String SQLquery = "SELECT * FROM EMPLOYEE";
// Gets result of the query
ResultSet rs = stmt.executeQuery(SQLquery);

The class ResultSet defines a number of methods for accessing the result. The result set is basically a sequence of rows, over which we can iterate using the next() method. The result set maintains a cursor to remember the current row in the result set. A call of the next() method advances this cursor to the next row until the end of data, where next() returns null. Within the cursor row, you can access the value of each column by specifying either the index number of the column or the name of the column. The getXX methods, where XX represents data types such as Int and String, can be used to access each column.

The following code fragment shows how to retrieve the first column (EMPNO) and second column (FIRSTNAME) of the result set.

while (rs.next()) {
    String firstColumn  = rs.getString(1);
    String secondColumn = rs.getString(2);
    System.out.print(firstColumn);
    System.out.print(" " + secondColumn);
    System.out.print("\n");
}

Listing D.1 shows a sample program that accesses the employee table in the sample table.

Listing D.1 Program to access the employee table, appendixD/JDBCSample.java

package appendixD;

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

class JDBCSample {
    static {
        try {
            // Register the driver with DriverManager
            Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String argv[]) {
        try {
            // URL is jdbc:db2:dbname
            String url = "jdbc:db2:sample";
            // Userid and password are specified as
            // System properties
            String userid = System.getProperty("appendixD.userid");
            String password =
                            System.getProperty("appendixD.password");
            // Connects with default username and password
            Connection con = DriverManager.getConnection(url,
                                                         userid,
                                                         password);
            // Creates statement
            Statement stmt = con.createStatement();
            String SQLquery = "SELECT * FROM EMPLOYEE";
            // Gets result of the query
            ResultSet rs = stmt.executeQuery(SQLquery);
            // Displays Result
            while (rs.next()) {
                String firstColumn  = rs.getString(1);
                String secondColumn = rs.getString(2);
                System.out.print(firstColumn);
                System.out.print(" " + secondColumn);
                System.out.print("\n");
            }
            rs.close();
            stmt.close();
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }
}

Executing this program generates the following output.

R:\samples>java -DappendixD.userid="db2admin" -DappendixD.
password="db2admin" appendixD.JDBCSample
000010 SHILI
000020 MICHAEL
000030 SALLY
000050 JOHN
000060 IRVING
000070 EVA
000090 EILEEN
000100 THEODORE
000110 VINCENZO
000120 SEAN

This section introduced a program using JDBC. This program is quite simple but shows a typical pattern appearing in large applications. The next section describes other functions of JDBC by using more complex programs.