[Next Example | Main Tutorial Page]

JDBC: Example 1 of 2

Use the following as an example for your program.

//////////////////////////////////////////////////////////////////////////////////
//
// JDBCPopulate example.  This program uses the AS/400 JDBC driver to
// create and populate a table.
//
// Command syntax:
//    JDBCPopulate system collectionName tableName
//
// For example,
//    JDBCPopulate MySystem MyLibrary MyTable
//
//////////////////////////////////////////////////////////////////////////////////
//
// This source is an example of AS/400 Toolbox for Java 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.
//
// AS/400 Toolbox for Java
// (C) Copyright IBM Corp. 1997
// All rights reserved.
// US Government Users Restricted Rights -
// Use, duplication, or disclosure restricted
// by GSA ADP Schedule Contract with IBM Corp.
//
//////////////////////////////////////////////////////////////////////////////////

import java.sql.*;

public class JDBCPopulate
{

    private static final String words[]
        = { "One",      "Two",      "Three",    "Four",     "Five",
            "Six",      "Seven",    "Eight",    "Nine",     "Ten",
            "Eleven",   "Twelve",   "Thirteen", "Fourteen", "Fifteen",
            "Sixteen",  "Seventeen","Eighteen", "Nineteen", "Twenty" };



    public static void main (String[] parameters)
    {

        if (parameters.length != 3) {
            System.out.println("");
            System.out.println("Usage:");
            System.out.println("");
            System.out.println("   JDBCPopulate system collectionName tableName");
            System.out.println("");
            System.out.println("");
            System.out.println("For example:");
            System.out.println("");
            System.out.println("");
            System.out.println("   JDBCPopulate MySystem MyLibrary MyTable");
            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());1 

			
            connection = DriverManager.getConnection ("jdbc:as400://"
                + system + "/" + collectionName);2 


            try {
                Statement dropTable = connection.createStatement ();
                dropTable.executeUpdate ("DROP TABLE " + tableName);3
            }
            catch (SQLException e) {
            }

            
            Statement createTable = connection.createStatement ();
            createTable.executeUpdate ("CREATE TABLE " + tableName
                + " (I INTEGER, WORD VARCHAR(20), SQUARE INTEGER, "
                + " SQUAREROOT DOUBLE)");4 

            
            PreparedStatement insert = connection.prepareStatement ("INSERT INTO "
                + tableName + " (I, WORD, SQUARE, SQUAREROOT) "
                + " VALUES (?, ?, ?, ?)");5 

            
            for (int i = 1; i <= words.length; ++i) {
                insert.setInt (1, i); 
                insert.setString (2, words[i-1]); 
                insert.setInt (3, i*i); 
                insert.setDouble (4, Math.sqrt(i)); 
                insert.executeUpdate ();6 
            }

            System.out.println ("Table " + collectionName + "." + tableName
                + " has been populated.");
        }

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

        finally {

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

        System.exit (0);
    }



}

  1. This line loads the AS/400 Toolbox for Java JDBC driver. A JDBC driver is necessary because it mediates between JDBC and the database.

  2. This statement connects to the database. A prompt will appear for the user ID and password. A default schema is provided so that you will not need to qualify the table name in SQL statements.

  3. These lines delete the table if it already exists.

  4. These lines create the table.

  5. This line prepares a statement that will insert rows into the table. Because you will be executing this statement several times, you should use a PreparedStatement and parameter markers.

  6. This block of code populates the table for you; every time the loop is executed, it inserts a row into the table.

  7. Now that the table has been created and filled in, this statement closes the connection to the database.




Previous

Next


[ Legal | AS/400 Glossary ]