Example: JDBC

//////////////////////////////////////////////////////////////////////////////////
//
// BasicJDBC example.  This program uses the native JDBC driver for the 
// Developer Kit for Java to build a simple table and process a query
// that displays the data in that table.
//
// Command syntax:
//    BasicJDBC 
//
//////////////////////////////////////////////////////////////////////////////////
//
// This source is an example of the native JDBC driver.
// IBM grants you a nonexclusive license to use this as an example
// from which you can generate similar function tailored to
// your own specific needs.
//
// This sample code is provided by IBM for illustrative purposes
// only. These examples have not been thoroughly tested under all
// conditions. IBM, therefore, cannot guarantee or imply
// reliability, serviceability, or function of these programs.
//
// All programs contained herein are provided to you "AS IS"
// without any warranties of any kind.  The implied warranties of
// merchantability and fitness for a particular purpose are
// expressly disclaimed.
//
// IBM Developer Kit for Java
// (C) Copyright IBM Corp. 2001
// All rights reserved.
// US Government Users Restricted Rights -
// Use, duplication, or disclosure restricted
// by GSA ADP Schedule Contract with IBM Corp.
//
//////////////////////////////////////////////////////////////////////////////////

// Include any Java classes that are to be used. In this application,  
// many classes from the java.sql package are used and the 
// java.util.Properties class is also used as part of obtaining
// a connection to the database.
import java.sql.*;
import java.util.Properties;

// Create a public class to encapsulate the program.
public class BasicJDBC {

    // The connection is a private variable of the object.
    private Connection connection = null;

    // Any class that is to be an 'entry point' for running 
    // a program must have a main method. The main method
    // is where processing begins when the program is called.
    public static void main(java.lang.String[] args) {

        // Create an object of type BasicJDBC. This 
        // is fundamental to object-oriented programming. Once
        // an object is created, call various methods on 
        // that object to accomplish work.
        // In this case, calling the constructor for the object
        // creates a database connection that the other 
        // methods use to do work against the database.
        BasicJDBC test = new BasicJDBC();

        // Call the rebuildTable method. This method ensures that 
        // the table used in this program exists and looks
        // correct. The return value is a boolean for  
        // whether or not rebuilding the table completed
        // successfully. If it did no, display a message 
        // and exit the program.
        if (!test.rebuildTable()) {
            System.out.println("Failure occurred while setting up " + 
                               " for running the test.");
            System.out.println("Test will not continue.");
            System.exit(0);
        }

        // The run query method is called next. This method  
        // processes an SQL select statement against the table that 
        // was created in the rebuildTable method. The output of
        // that query is output to standard out for you to view.
        test.runQuery();

        // Finally, the cleanup method is called. This method 
        // ensures that the database connection that the object has 
        // been hanging on to is closed.
        test.cleanup();
    }


    /**
    This is the constructor for the basic JDBC test. It creates a database 
    connection that is stored in an instance variable to be used in later
    method calls.
    **/
    public BasicJDBC() {

        // One way to create a database connection is to pass a URL
        // and a java Properties object to the DriverManager. The following
        // code constructs a Properties object that has your user ID and
        // password. These pieces of information are used for connecting
        // to the database.
        Properties properties = new Properties ();
        properties.put("user", "cujo");
        properties.put("password", "newtiger");

        // Use a try/catch block to catch all exceptions that can come out of the 
        // following code.
        try {
            // Create the database Connection object that this program uses in all
            // the other method calls that are made. The following code specifies
            // that a connection is to be established to the local database and that
            // that connection should conform to the properties that were set up 
            // previously (that is, it should use the user ID and password specified).
            connection = DriverManager.getConnection("jdbc:db2:*local", properties);

        } catch (Exception e) {
            // If any of the lines in the try/catch block fail, control transfers to
            // the following line of code. A robust application tries to handle the 
            // problem or provide more details to you. In this program, the error 
            // message from the exception is displayed and the application allows 
            // the program to return.
            System.out.println("Caught exception: " + e.getMessage());
        }
    }


    /**
    Ensures that the qgpl.basicjdbc table looks you want it to at the start of
    the test. 
    
    @returns boolean    Returns true if the table was rebuild successfully; 
                        returns false if any failure occurred.
    **/
    public boolean rebuildTable() {
        // Wrap all the functionality in a try/catch block so an attempt is 
        // made to handle any errors that may happen within this method.
        try {

            // Statement objects are used to process SQL statements against the 
            // database. The Connection object is used to create a Statement
            // object.
            Statement s = connection.createStatement();

            try {
                // Build the test table from scratch. Process an update statement
                // that attempts to delete the table if it currently exists. 
                s.executeUpdate("drop table qgpl.basicjdbc");
            } catch (SQLException e) {
                // Do not perform anything if an exception occurred. Assume
                // that the problem is that the table that was dropped does not
                // exist and that it can be created next. 
            }

            // Use the statement object to create our table.
            s.executeUpdate("create table qgpl.basicjdbc(id int, name char(15))");

            // Use the statement object to populate our table with some data.
            s.executeUpdate("insert into qgpl.basicjdbc values(1, 'Frank Johnson')");
            s.executeUpdate("insert into qgpl.basicjdbc values(2, 'Neil Schwartz')");
            s.executeUpdate("insert into qgpl.basicjdbc values(3, 'Ben Rodman')");
            s.executeUpdate("insert into qgpl.basicjdbc values(4, 'Dan Gloore')");

            // Close the SQL statement to tell the database that it is no longer 
            // needed.
            s.close();

            // If the entire method processed successfully, return true. At this point, 
            // the table has been created or refreshed correctly.
            return true;

        } catch (SQLException sqle) {
            // If any of our SQL statements failed (other than the drop of the table
            // that was handled in the inner try/catch block), the error message is
            // displayed and false is returned to the caller, indicating that the table
            // may not be complete.
            System.out.println("Error in rebuildTable: " + sqle.getMessage());
            return false;
        }
    }



    /**
    Runs a query against the demonstration table and the results are displayed to 
    standard out.
    **/
    public void runQuery() {
        // Wrap all the functionality in a try/catch block so an attempts is 
        // made to handle any errors that might happen within this 
        // method.
        try {
            // Create a Statement object.
            Statement s = connection.createStatement();

            // Use the statement object to run an SQL query. Queries return 
            // ResultSet objects that are used to look at the data the query
            // provides.
            ResultSet rs = s.executeQuery("select * from qgpl.basicjdbc");

            // Display the top of our 'table' and initialize the counter for the 
            // number of rows returned.
            System.out.println("--------------------");
            int i = 0;

            // The ResultSet next method is used to process the rows of a 
            // ResultSet. The next method must be called once before the 
            // first data is available for viewing. As long as next returns
            // true, there is another row of data that can be used.
            while (rs.next()) {

                // Obtain both columns in the table for each row and write a row to 
                // our on-screen table with the data. Then, increment the count 
                // of rows that have been processed.
                System.out.println("| " + rs.getInt(1) + " | " + rs.getString(2) + "|");
                i++;
            }

            // Place a border at the bottom on the table and display the number of rows 
            // as output.
            System.out.println("--------------------");
            System.out.println("There were " + i + " rows returned.");
            System.out.println("Output is complete.");

        } catch (SQLException e) {
            // Display more information about any SQL exceptions that are 
            // generated as output.
            System.out.println("SQLException exception: ");
            System.out.println("Message:....." + e.getMessage());
            System.out.println("SQLState:...." + e.getSQLState());
            System.out.println("Vendor Code:." + e.getErrorCode());
            e.printStackTrace();
        }
    }


    /**
    The following method ensures that any JDBC resources that are still  
    allocated are freed.
    **/
    public void cleanup() {
        try {
            if (connection != null)
                connection.close();
        } catch (Exception e) {
            System.out.println("Caught exception: ");
            e.printStackTrace();
        }
    }
}