Example: Using JDBCPopulate to create and populate a table
//////////////////////////////////////////////////////////////////////////////////
//
// 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 IBM 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
// merchantablility and fitness for a particular purpose are
// expressly disclaimed.
//
// IBM 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
{
// Strings to be added in the WORD column of the table.
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)
{
// Check the input 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 {
// Load the IBM Toolbox for Java JDBC driver.
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
// Get a connection to the database. Since we do not
// provide a user id or password, a prompt will appear.
//
// Note that we provide a default schema here so
// that we do not need to qualify the table name in
// SQL statements.
//
connection = DriverManager.getConnection ("jdbc:as400://"
+ system + "/" + collectionName);
// Drop the table if it already exists.
try {
Statement dropTable = connection.createStatement ();
dropTable.executeUpdate ("DROP TABLE " + tableName);
}
catch (SQLException e) {
// Ignore.
}
// Create the table.
Statement createTable = connection.createStatement ();
createTable.executeUpdate ("CREATE TABLE " + tableName
+ " (I INTEGER, WORD VARCHAR(20), SQUARE INTEGER, "
+ " SQUAREROOT DOUBLE)");
// Prepare a statement for inserting rows. Since we
// execute this multiple times, it is best to use a
// PreparedStatement and parameter markers.
PreparedStatement insert = connection.prepareStatement ("INSERT INTO "
+ tableName + " (I, WORD, SQUARE, SQUAREROOT) "
+ " VALUES (?, ?, ?, ?)");
// Populate the table.
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 ();
}
// Output a completion message.
System.out.println ("Table " + collectionName + "." + tableName
+ " has been populated.");
}
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);
}
}