example4.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.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * This class demonstrates the use of local transactions in JDBC. It also
 * demonstrates the use of PreparedStatements to do JDBC operations
 * 
 * @version 1.0
 */
public class Example4 {
    /**
     * 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) {
        Example4 obj = new Example4();

        int[] item_id = new int[] { 1, 3, 5 };
        double[] qty = new double[] { 12, 8, 24 };
        obj.buyItem(item_id, qty);
    }

    /**
     * Returns a connection to a pointbase database, whose server is running at
     * the given host and port, and the database being called dbName. The
     * credentials supplied while connecting is also accepted as paramter
     * 
     * @param host : The host where the server is running
     * @param port : The port where the server is listening to
     * @param dbName : the database name
     * @param userid : The user id to use while connecting
     * @param password The password of the specified user
     * 
     * @return an object of type java.sql.Connection
     */
    private Connection getConnection(String host, int port, String dbName, 
                                     String userid, String password) {
        Connection conn = null;

        try {
            Class.forName("com.pointbase.jdbc.jdbcUniversalDriver");

            String url = "jdbc:pointbase:server://" + host + ":" + port + "/"
                         + dbName;
            System.out.println("Using url string " + url);
            conn = DriverManager.getConnection(url, userid, password);
            System.out.println("Obtained Connection");
        } catch (Throwable t) {
            t.printStackTrace();
            conn = null;
        }

        return conn;
    }

    /**
     * adds a new sale into the billing system. this function adds a new bill
     * based on the items passed in the item_id array. the quantity of each
     * item is passed in the qty array. the stock of each item is also
     * reduced.
     * 
     * @param item_id : a list of items purchased
     * @param qty : quantity of each item purchases
     */
    private void buyItem(int[] item_id, double[] qty) {
        System.out.println("Trying to obtain a connection ... ");

        Connection conn = getConnection("localhost", 9092, "demo", "PBSYSADMIN", 
                                        "PBSYSADMIN");

        if (conn == null) {
            return;
        }

        System.out.println("Connection obtained !");

        boolean updateItemStockStmtCreated = false;
        boolean insertBillItemStmtCreated = false;
        boolean queryItemTableStmtCreated = false;
        boolean stmtCreated = false;
        boolean orderSuccessful = true;

        PreparedStatement updateItemStockStmt = null;
        PreparedStatement insertBillItemStmt = null;
        PreparedStatement queryItemTableStmt = null;
        Statement stmt = null;

        try {
            conn.setAutoCommit(false);

            String anSQLStmt = " UPDATE XYZCONF.ITEM SET "
                               + " STOCK_QTY = STOCK_QTY - ? "
                               + " WHERE ITEM_ID = ? ";

            updateItemStockStmt = conn.prepareStatement(anSQLStmt);

            updateItemStockStmtCreated = true;

            anSQLStmt = " INSERT INTO XYZCONF.BILL_ITEM ( "
                        + " BILL_ID, ITEM_ID, QUANTITY ) VALUES "
                        + " ( ?, ?, ? ) ";

            insertBillItemStmt = conn.prepareStatement(anSQLStmt);

            insertBillItemStmtCreated = true;

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

            queryItemTableStmt = conn.prepareStatement(anSQLStmt);

            queryItemTableStmtCreated = true;

            stmt = conn.createStatement();

            stmtCreated = true;


            // first get a new bill id
            anSQLStmt = " SELECT COUNT(BILL_ID) FROM XYZCONF.BILL ";

            ResultSet rs = stmt.executeQuery(anSQLStmt);
            int billId = 0;

            if (rs.next() == false) {
                orderSuccessful = false;
            } else {
                billId = rs.getInt(1) + 1;
            }


            // insert a new Bill. - set the amount as zero for now.
            anSQLStmt = " INSERT INTO XYZCONF.BILL ( BILL_ID, SALE_AMOUNT ) "
                        + " VALUES ( " + billId + ", 0.00 )  ";

            // continue with the other operations only if one row was affected.
            // meaning the bill was inserted.
            if (orderSuccessful && (stmt.executeUpdate(anSQLStmt) == 1)) {
                double saleAmt = 0.00;

                for (int i = 0; i < item_id.length; i++) {
                    // query the item table
                    queryItemTableStmt.setInt(1, item_id[i]);
                    rs = queryItemTableStmt.executeQuery();

                    if ((rs == null) || (rs.next() == false)) // item not found
                    {
                        orderSuccessful = false;

                        break;
                    }

                    String description = rs.getString(1);
                    double stock = rs.getDouble(2);
                    double price = rs.getDouble(3);
                    System.out.println("Found item : " + description);

                    // first let us check if we have sufficient stock
                    if (stock < qty[i]) {
                        System.out.println(" Stock for item " + description
                                           + " is less than the requested qty.");

                        orderSuccessful = false;

                        break;
                    }


                    // first insert a bill_item
                    insertBillItemStmt.setInt(1, billId);
                    insertBillItemStmt.setInt(2, item_id[i]);
                    insertBillItemStmt.setDouble(3, qty[i]);

                    if (insertBillItemStmt.executeUpdate() != 1) {
                        System.out.println("Insert into Bill item failed!");
                        orderSuccessful = false;

                        break;
                    }


                    // keep track of the sale amount.
                    saleAmt += (qty[i] * price);


                    //update the stock 					
                    updateItemStockStmt.setDouble(1, qty[i]);
                    updateItemStockStmt.setInt(2, item_id[i]);

                    if (updateItemStockStmt.executeUpdate() != 1) {
                        System.out.println("Update of item stock failed!");
                        orderSuccessful = false;

                        break;
                    }
                }

                // if every thing else succeeded, then we will update the sale amount					
                if (orderSuccessful) {
                    // insert a new Bill. - set the amount as zero for now.
                    anSQLStmt = " UPDATE XYZCONF.BILL SET SALE_AMOUNT = "
                                + saleAmt + " WHERE  BILL_ID = " + billId;

                    if (stmt.executeUpdate(anSQLStmt) != 1) {
                        orderSuccessful = false;
                    }
                }
            } else {
                orderSuccessful = false;
            }
        } catch (SQLException t) {
            t.printStackTrace();
            orderSuccessful = false;
        } finally {
            try {
                if (!orderSuccessful) {
                    System.out.println("rolling back transaction ... ");
                    conn.rollback();
                } else {
                    System.out.println("committing transaction ... ");
                    conn.commit();
                }

                if (stmtCreated) {
                    stmt.close();
                }

                if (queryItemTableStmtCreated) {
                    queryItemTableStmt.close();
                }

                if (insertBillItemStmtCreated) {
                    insertBillItemStmt.close();
                }

                if (updateItemStockStmtCreated) {
                    updateItemStockStmt.close();
                }

                conn.close();
            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
            System.out.println("All done!");
        }
    }
}