////////////////////////////////////////////////////////////////////////////////// // // JDBC 2.0 Standard Extension example. This program uses an AS400JDBCRowSet object, an // AS400JDBCDataSource object, and an AS400JDBCConnectionPool object to create // and update a table. // // Command syntax: // JDBCStdExtExample system userId password collectionName tableName // // For example, // JDBCStdExtExample MySystem MyUserId MyPassword MyCollectionName MyTable // // Note: JDBC 2.0 standard extension classes and JNDI classes must be included // in your classpath to run this example. These are not part of the Toolbox and // are available from Sun. // ////////////////////////////////////////////////////////////////////////////////// // // 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. 2000 // All rights reserved. // US Government Users Restricted Rights - // Use, duplication, or disclosure restricted // by GSA ADP Schedule Contract with IBM Corp. // ////////////////////////////////////////////////////////////////////////////////// import com.ibm.as400.access.*; import java.sql.*; public class JDBCStdExtExample { // 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"}; // Strings to be added in the WORD column of the table. private static final String moreWords[] = { "Anteater", "Bat", "Cat", "Dog", "Elephant", "Fox", "Giraffe", "Hippo", "Iguana", "Just", "Lion", "Monkey", "None", "Or", "Possum", "Quite", "Rabbit", "So", "Tiger", "Bye"}; public static void main (String[] parameters) { // Check the input parameters. if (parameters.length != 5) { System.out.println(""); System.out.println("Usage:"); System.out.println(""); System.out.println(" JDBCStdExtExample system userId password collectionName tableName"); System.out.println(""); System.out.println(""); System.out.println("For example:"); System.out.println(""); System.out.println(""); System.out.println(" JDBCStdExtExample MySystem MyUserId MyPassword MyLibrary MyTable"); System.out.println(""); return; } String system = parameters[0]; String userId = parameters[1]; String password = parameters[2]; String collectionName = parameters[3]; String tableName = parameters[4]; Connection connection = null; try { // Create an AS400JDBCConnectionPoolDataSource with system. Since we do not // provide a userId or password, a prompt will appear. // A data source object can be created and used to create multiple AS400JDBCConnectionPool // and AS400JDBCRowSet objects. Data source objects can also be stored and // retrieved using JNDI. AS400JDBCConnectionPoolDataSource dataSource = new AS400JDBCConnectionPoolDataSource(system, userId, password); // Set the JDBCDriver implementation to Toolbox. dataSource.setDriver("toolbox"); // Set the datasource database name. dataSource.setDatabaseName(system); // Create a AS400JDBCConnectionPool. AS400JDBCConnectionPool pool = new AS400JDBCConnectionPool(dataSource); // Allow a maximum of 128 connections in the pool. pool.setMaxConnections(128); // Add 5 connections to the pool that can be used by the application. pool.fill(5); // Get a handle to a database connection from the pool. connection = pool.getConnection(); // Drop the table if it already exists. try { Statement dropTable = connection.createStatement (); dropTable.executeUpdate ("DROP TABLE " + collectionName + "." + tableName); } catch (SQLException e) { // Ignore. } // Create the table. Statement createTable = connection.createStatement (); createTable.executeUpdate ("CREATE TABLE " + collectionName + "." + 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 " + collectionName + "." + 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."); // Create an AS400JDBCRowSet. AS400JDBCRowSet rowset = new AS400JDBCRowSet("jdbc:as400://" + system, userId, password); // Set concurrency so updates can be made. rowset.setConcurrency(ResultSet.CONCUR_UPDATABLE); // Set the command used to select rows from the table. rowset.setCommand("SELECT * FROM " + collectionName + "." + tableName + " for update"); // Populate the rowset. rowset.execute(); // Update the words. for (int i=0; rowset.next(); i++) { String newWord = moreWords[i]; rowset.updateString("WORD", newWord); rowset.updateRow(); } // Output a completion message. System.out.println ("Table " + collectionName + "." + tableName + " has been updated."); // Close the connection handle to return it to the pool. connection.close(); // Close the pool to release all resources. pool.close(); } catch (Exception e) { System.out.println (); System.out.println ("ERROR: " + e.getMessage()); e.printStackTrace(); } finally { // Clean up. try { if (connection != null) connection.close (); } catch (SQLException e) { // Ignore. } } System.exit (0); } }