[ Previous part | Simple programming examples ]

Example: Using JDBC classes to create and populate a table (part 2 of 2)

Use the following as an example for your program. The example includes detailed explanations about key lines in the code. You can view the detailed explanations in the following ways:

Note: Read the Code example disclaimer for important legal information.

//////////////////////////////////////////////////////////////////////////////////
//
// JDBCQuery example.  This program uses the IBM Toolbox for Java JDBC driver to
// query a table and output its contents.
//
// Command syntax:
//    JDBCQuery system collectionName tableName
//
// For example,
//    JDBCQuery MySystem qiws qcustcdt
//
// This source is an example of IBM Toolbox for Java JDBC driver.
//
//////////////////////////////////////////////////////////////////////////////////

import java.sql.*;

public class JDBCQuery
{



    // Format a string so that it has the specified width.
    private static String format (String s, int width)
    {
        String formattedString;

        // The string is shorter than specified width,
        // so we need to pad with blanks.
        if (s.length() < width) {
            StringBuffer buffer = new StringBuffer (s);
            for (int i = s.length(); i < width; ++i)
                buffer.append (" ");
            formattedString = buffer.toString();
        }

        // Otherwise, we need to truncate the string.
        else
            formattedString = s.substring (0, width);

        return formattedString;
    }



    public static void main (String[] parameters)
    {
        // Check the input parameters.
        if (parameters.length != 3) {
            System.out.println("");
            System.out.println("Usage:");
            System.out.println("");
            System.out.println("   JDBCQuery system collectionName tableName");
            System.out.println("");
            System.out.println("");
            System.out.println("For example:");
            System.out.println("");
            System.out.println("");
            System.out.println("   JDBCQuery mySystem qiws qcustcdt");
            System.out.println("");
            return;
        }

        String system           = parameters[0];
        String collectionName   = parameters[1];
        String tableName        = parameters[2];

        Connection connection   = null;
	
        try { 


            DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver()); Note 1 Click to display a detailed explanation

            // Get a connection to the database.  Since we do not
            // provide a user id or password, a prompt will appear.
            connection = DriverManager.getConnection ("jdbc:as400://" + system);
            DatabaseMetaData dmd = connection.getMetaData (); Note 2 Click to display a detailed explanation

            // Execute the query.
            Statement select = connection.createStatement ();
            ResultSet rs = select.executeQuery ("SELECT * FROM "
                + collectionName + dmd.getCatalogSeparator() + tableName); Note 3 Click to display a detailed explanation

            // Get information about the result set.  Set the column
            // width to whichever is longer: the length of the label
            // or the length of the data.
            ResultSetMetaData rsmd = rs.getMetaData ();
            int columnCount = rsmd.getColumnCount (); Note 4 Click to display a detailed explanation
            String[] columnLabels = new String[columnCount];
            int[] columnWidths = new int[columnCount];
            for (int i = 1; i <= columnCount; ++i) { 
                columnLabels[i-1] = rsmd.getColumnLabel (i);
                columnWidths[i-1] = Math.max (columnLabels[i-1].length(),
                    rsmd.getColumnDisplaySize (i)); Note 5 Click to display a detailed explanation
            }

            // Output the column headings.
            for (int i = 1; i <= columnCount; ++i) {
                System.out.print (format (rsmd.getColumnLabel(i), columnWidths[i-1]));
                System.out.print (" ");
            }
            System.out.println ();

            // Output a dashed line.
            StringBuffer dashedLine;
            for (int i = 1; i <= columnCount; ++i) {
                for (int j = 1; j <= columnWidths[i-1]; ++j)
                    System.out.print ("-");
                System.out.print (" ");
            }
            System.out.println ();

            // Iterate throught the rows in the result set and output
            // the columns for each row. 
            while (rs.next ()) {
                for (int i = 1; i <= columnCount; ++i) {
                    String value = rs.getString (i);
                    if (rs.wasNull ())
                        value = "<null>"; Note 6 Click to display a detailed explanation
                    System.out.print (format (value, columnWidths[i-1]));
                    System.out.print (" ");
                }
                System.out.println ();
            }

        }

        catch (Exception e) {
            System.out.println ();
            System.out.println ("ERROR: " + e.getMessage());
        }

        finally {

            // Clean up.
            try {
                if (connection != null)
                    connection.close ();
            }
            catch (SQLException e) {
                // Ignore.
            }
        }

        System.exit (0);
    }



}
  1. This line loads the IBM Toolbox for Java JDBC driver. A JDBC driver mediates between JDBC and the database with which you are working.

  2. This line retrieves the connection's meta data, an object that describes many of the characteristics of the database.

  3. This statement executes the query on the specified table.

  4. These lines retrieve information about the table.

  5. These lines set the column width to either the length of the label or the length of the data, whichever is longer.

  6. This block of code iterates through all of the rows in the table and displays the contents of each column in each row.

[ Previous part | Simple programming examples ]