example7.java

BEA WebLogic Server 8.1大全 = BEA webLogic server 8.1 unleashed (美) Mark Artiges等著 袁毅 ... [等] 译 eng

/* 
 * WebLogic Server Unleashed
 *
 */
package com.wlsunleashed.jdbc;

import java.sql.SQLException;

import javax.sql.RowSet;

import weblogic.jdbc.rowset.RowSetFactory;
import weblogic.jdbc.rowset.WLCachedRowSet;
import weblogic.jdbc.rowset.WLRowSetMetaData;


/**
 * This class demonstrates the use of a RowSet to perform database operations.
 * Note: To be able to successfully test this class, you require JDK 1.4 or
 * later.
 * 
 * @version 1.0
 */
public class Example7 {
    /** The host to connect to */
    private static final String HOST = "localhost";

    /** The port number */
    private static final int PORT = 9092;

    /** The database name */
    private static final String DBNAME = "demo";

    /** User id in the database */
    private static final String USERID = "PBSYSADMIN";

    /** Password for the user */
    private static final String PASSWORD = "PBSYSADMIN";

    /**
     * The main method - the point of entry into this class
     * 
     * @param args : all run-time args are passed in this String array
     */
    public static void main(String[] args) {
        Example7 obj = new Example7();
        System.out.println("Before doing ANYTHING");

        RowSet aRowSet = null;
        // add is in its own try-catch block because sometimes, the 
        // row we are trying to add may already exist. in that case, we
        // will simply continue with the other operations.
        try {
            // query the db
            aRowSet = obj.getRowSet();
            obj.readRowSet(aRowSet);
            obj.addRow(aRowSet);
            aRowSet.close();
        } catch (Throwable t) {
            t.printStackTrace();
        }

        try {
            System.out.println("After adding");


            // query the db again to check for the updates
            aRowSet = obj.getRowSet();
            obj.readRowSet(aRowSet);
            obj.updateRow(aRowSet);
            aRowSet.close();

            System.out.println("After updating");


            // query the db again to check for the updates
            aRowSet = obj.getRowSet();
            obj.readRowSet(aRowSet);
            obj.deleteRow(aRowSet);
            aRowSet.close();

            System.out.println("After deleting");


            // query the db again to check for the updates
            aRowSet = obj.getRowSet();
            obj.readRowSet(aRowSet);
        } catch (Throwable t) {
            t.printStackTrace();
        }
    }

    /**
     * Returns a Connection URL for the database
     * 
     * @return The URL String
     */
    private String getDBConnectionURL() {
        return "jdbc:pointbase:server://" + HOST + ":" + PORT + "/" + DBNAME;
    }

    /**
     * Returns a RowSet containing data from a query
     * 
     * @return javax.sql.RowSet
     * @throws SQLException Thrown when any SQL db operation fails
     */
    private RowSet getRowSet()
                      throws SQLException, ClassNotFoundException {
        // need to load the driver.
        Class.forName("com.pointbase.jdbc.jdbcUniversalDriver");

        RowSet aRowSet = RowSetFactory.newInstance().newCachedRowSet();


        //aRowSet.setType(RowSet.TYPE_SCROLL_SENSITIVE);
        aRowSet.setUrl(getDBConnectionURL());
        aRowSet.setUsername(USERID);
        aRowSet.setPassword(PASSWORD);

        String anSQLStmt = " SELECT ITEM_ID, ITEM_DESCRIPTION, "
                           + " STOCK_QTY, UNIT_PRICE FROM XYZCONF.ITEM";

        aRowSet.setCommand(anSQLStmt);
        aRowSet.execute();

        WLRowSetMetaData rowSetMetaData = (WLRowSetMetaData) aRowSet.getMetaData();
        rowSetMetaData.setPrimaryKeyColumn(1, true);

        return aRowSet;
    }

    /**
     * Adds a row to the database using a RowSet.
     * 
     * @param aRowSet The RowSet to be used
     * @throws SQLException Thrown when any SQL db operation fails
     */
    private void addRow(RowSet aRowSet)
                 throws SQLException {
        aRowSet.moveToInsertRow();
        aRowSet.updateInt("ITEM_ID", 999);
        aRowSet.updateString("ITEM_DESCRIPTION", "Added via RowSet");
        aRowSet.updateDouble("STOCK_QTY", 100);
        aRowSet.updateDouble("UNIT_PRICE", 10.0);
        System.out.println("Inserting Row ");
        aRowSet.insertRow();
        System.out.println("Accepting Changes");
        ((WLCachedRowSet) aRowSet).acceptChanges();
    }

    /**
     * Deletes the added row from the database using a RowSet
     * 
     * @param aRowSet The RowSet to be used
     * @throws SQLException Thrown when any SQL db operation fails
     */
    private void deleteRow(RowSet aRowSet)
                    throws SQLException {
        aRowSet.beforeFirst();

        while (aRowSet.next()) {
            if (aRowSet.getInt("ITEM_ID") == 999) {
                System.out.println("Deleting Row");
                aRowSet.deleteRow();
                System.out.println("Accepting Changes");
                ((WLCachedRowSet) aRowSet).acceptChanges();

                break;
            }
        }
    }

    /**
     * Prints the contents of a RowSet
     * 
     * @param aRowSet The RowSet to be used
     * @throws SQLException Thrown when any SQL db operation fails
     */
    private void readRowSet(RowSet aRowSet)
                     throws SQLException {
        aRowSet.beforeFirst();

        while (aRowSet.next()) {
            System.out.println("Item id = " + aRowSet.getInt("ITEM_ID")
                               + ", Description = "
                               + aRowSet.getString("ITEM_DESCRIPTION")
                               + ", Stock = " + aRowSet.getDouble("STOCK_QTY")
                               + ", Unit Price = "
                               + aRowSet.getDouble("UNIT_PRICE"));
        }
    }

    /**
     * Updates the Stock using a rowset.
     * 
     * @param aRowSet The RowSet to be used
     * @throws SQLException Thrown when any SQL db operation fails
     */
    private void updateRow(RowSet aRowSet)
                    throws SQLException {
        aRowSet.beforeFirst();

        while (aRowSet.next()) {
            if (aRowSet.getInt("ITEM_ID") == 999) {
                System.out.println("Updating Row");
                aRowSet.updateDouble("STOCK_QTY", 33.33);
                aRowSet.updateRow();
                System.out.println("Accepting Changes");
                ((WLCachedRowSet) aRowSet).acceptChanges();

                break;
            }
        }
    }
}