benchcthread.java

非常棒的java数据库

/*
 * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
 * (license2)
 * Initial Developer: H2 Group
 */
package org.h2.test.bench;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.HashMap;

/**
 * This class implements the functionality of one thread of BenchC.
 */
public class BenchCThread {

    private Database db;
    private int warehouseId;
    private int terminalId;
    private HashMap prepared = new HashMap();
    private static final int OP_NEW_ORDER = 0, OP_PAYMENT = 1,
            OP_ORDER_STATUS = 2, OP_DELIVERY = 3,
            OP_STOCK_LEVEL = 4;
    private static final BigDecimal ONE = new BigDecimal("1");
    private BenchCRandom random;
    private BenchC bench;

    BenchCThread(Database db, BenchC bench, BenchCRandom random, int terminal)
            throws Exception {
        this.db = db;
        this.bench = bench;
        this.terminalId = terminal;
        db.setAutoCommit(false);
        this.random = random;
        warehouseId = random.getInt(1, bench.warehouses);
    }

    void process() throws Exception {
        int[] deck = new int[] { OP_NEW_ORDER, OP_NEW_ORDER, OP_NEW_ORDER,
                OP_NEW_ORDER, OP_NEW_ORDER, OP_NEW_ORDER, OP_NEW_ORDER,
                OP_NEW_ORDER, OP_NEW_ORDER, OP_NEW_ORDER, OP_PAYMENT,
                OP_PAYMENT, OP_PAYMENT, OP_PAYMENT, OP_PAYMENT, OP_PAYMENT,
                OP_PAYMENT, OP_PAYMENT, OP_PAYMENT, OP_PAYMENT,
                OP_ORDER_STATUS, OP_DELIVERY, OP_STOCK_LEVEL };
        int len = deck.length;
        for (int i = 0; i < len; i++) {
            int temp = deck[i];
            int j = random.getInt(0, len);
            deck[i] = deck[j];
            deck[j] = temp;
        }
        for (int i = 0; i < len; i++) {
            int op = deck[i];
            switch (op) {
            case OP_NEW_ORDER:
                processNewOrder();
                break;
            case OP_PAYMENT:
                processPayment();
                break;
            case OP_ORDER_STATUS:
                processOrderStatus();
                break;
            case OP_DELIVERY:
                processDelivery();
                break;
            case OP_STOCK_LEVEL:
                processStockLevel();
                break;
            default:
                throw new Error("op=" + op);
            }
        }
    }

    private void processNewOrder() throws Exception {
        int dId = random.getInt(1, bench.districtsPerWarehouse);
        int cId = random.getNonUniform(1023, 1, bench.customersPerDistrict);
        int olCnt = random.getInt(5, 15);
        boolean rollback = random.getInt(1, 100) == 1;
        int[] supplyId = new int[olCnt];
        int[] itemId = new int[olCnt];
        int[] quantity = new int[olCnt];
        int allLocal = 1;
        for (int i = 0; i < olCnt; i++) {
            int w;
            if (bench.warehouses > 1 && random.getInt(1, 100) == 1) {
                do {
                    w = random.getInt(1, bench.warehouses);
                } while (w != warehouseId);
                allLocal = 0;
            } else {
                w = warehouseId;
            }
            supplyId[i] = w;
            int item;
            if (rollback && i == olCnt - 1) {
                // unused order number
                item = -1;
            } else {
                item = random.getNonUniform(8191, 1, bench.items);
            }
            itemId[i] = item;
            quantity[i] = random.getInt(1, 10);
        }
        char[] bg = new char[olCnt];
        int[] stock = new int[olCnt];
        BigDecimal[] amt = new BigDecimal[olCnt];
        Timestamp datetime = new Timestamp(System.currentTimeMillis());
        PreparedStatement prep;
        ResultSet rs;

        prep = prepare("UPDATE DISTRICT SET D_NEXT_O_ID=D_NEXT_O_ID+1 "
                + "WHERE D_ID=? AND D_W_ID=?");
        prep.setInt(1, dId);
        prep.setInt(2, warehouseId);
        db.update(prep, "updateDistrict");
        prep = prepare("SELECT D_NEXT_O_ID, D_TAX FROM DISTRICT "
                + "WHERE D_ID=? AND D_W_ID=?");
        prep.setInt(1, dId);
        prep.setInt(2, warehouseId);
        rs = db.query(prep);
        rs.next();
        int oId = rs.getInt(1) - 1;
        BigDecimal tax = rs.getBigDecimal(2);
        rs.close();
        // TODO optimizer: such cases can be optimized! A=1 AND B=A means
        // also B=1!
        //        prep = prepare("SELECT C_DISCOUNT, C_LAST, C_CREDIT, W_TAX "
        //                + "FROM CUSTOMER, WAREHOUSE "
        //                + "WHERE C_ID=? AND W_ID=? AND C_W_ID=W_ID AND C_D_ID=?");
        prep = prepare("SELECT C_DISCOUNT, C_LAST, C_CREDIT, W_TAX "
                + "FROM CUSTOMER, WAREHOUSE "
                + "WHERE C_ID=? AND C_W_ID=? AND C_W_ID=W_ID AND C_D_ID=?");
        prep.setInt(1, cId);
        prep.setInt(2, warehouseId);
        prep.setInt(3, dId);
        rs = db.query(prep);
        rs.next();
        BigDecimal discount = rs.getBigDecimal(1);
        rs.getString(2); // c_last
        rs.getString(3); // c_credit
        BigDecimal wTax = rs.getBigDecimal(4);
        rs.close();
        BigDecimal total = new BigDecimal("0");
        for (int number = 1; number <= olCnt; number++) {
            int olId = itemId[number - 1];
            int olSupplyId = supplyId[number - 1];
            int olQuantity = quantity[number - 1];
            prep = prepare("SELECT I_PRICE, I_NAME, I_DATA "
                    + "FROM ITEM WHERE I_ID=?");
            prep.setInt(1, olId);
            rs = db.query(prep);
            if (!rs.next()) {
                if (rollback) {
                    // item not found - correct behavior
                    db.rollback();
                    return;
                }
                throw new Exception("item not found: " + olId + " "
                        + olSupplyId);
            }
            BigDecimal price = rs.getBigDecimal(1);
            rs.getString(2); // i_name
            String data = rs.getString(3);
            rs.close();
            prep = prepare("SELECT S_QUANTITY, S_DATA, "
                    + "S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, "
                    + "S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10 "
                    + "FROM STOCK WHERE S_I_ID=? AND S_W_ID=?");
            prep.setInt(1, olId);
            prep.setInt(2, olSupplyId);
            rs = db.query(prep);
            if (!rs.next()) {
                if (rollback) {
                    // item not found - correct behavior
                    db.rollback();
                    return;
                }
                throw new Exception("item not found: " + olId + " "
                        + olSupplyId);
            }
            int sQuantity = rs.getInt(1);
            String sData = rs.getString(2);
            String[] dist = new String[10];
            for (int i = 0; i < 10; i++) {
                dist[i] = rs.getString(3 + i);
            }
            rs.close();
            String distInfo = dist[dId - 1];
            stock[number - 1] = sQuantity;
            if ((data.indexOf("original") != -1)
                    && (sData.indexOf("original") != -1)) {
                bg[number - 1] = 'B';
            } else {
                bg[number - 1] = 'G';
            }
            if (sQuantity > olQuantity) {
                sQuantity = sQuantity - olQuantity;
            } else {
                sQuantity = sQuantity - olQuantity + 91;
            }
            prep = prepare("UPDATE STOCK SET S_QUANTITY=? "
                    + "WHERE S_W_ID=? AND S_I_ID=?");
            prep.setInt(1, sQuantity);
            prep.setInt(2, olSupplyId);
            prep.setInt(3, olId);
            db.update(prep, "updateStock");
            BigDecimal olAmount = new BigDecimal(olQuantity).multiply(
                    price).multiply(ONE.add(wTax).add(tax)).multiply(
                    ONE.subtract(discount));
            olAmount = olAmount.setScale(2, BigDecimal.ROUND_HALF_UP);
            amt[number - 1] = olAmount;
            total = total.add(olAmount);
            prep = prepare("INSERT INTO ORDER_LINE (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, "
                    + "OL_I_ID, OL_SUPPLY_W_ID, "
                    + "OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
            prep.setInt(1, oId);
            prep.setInt(2, dId);
            prep.setInt(3, warehouseId);
            prep.setInt(4, number);
            prep.setInt(5, olId);
            prep.setInt(6, olSupplyId);
            prep.setInt(7, olQuantity);
            prep.setBigDecimal(8, olAmount);
            prep.setString(9, distInfo);
            db.update(prep, "insertOrderLine");
        }
        prep = prepare("INSERT INTO ORDERS (O_ID, O_D_ID, O_W_ID, O_C_ID, "
                + "O_ENTRY_D, O_OL_CNT, O_ALL_LOCAL) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?)");
        prep.setInt(1, oId);
        prep.setInt(2, dId);
        prep.setInt(3, warehouseId);
        prep.setInt(4, cId);
        prep.setTimestamp(5, datetime);
        prep.setInt(6, olCnt);
        prep.setInt(7, allLocal);
        db.update(prep, "insertOrders");
        prep = prepare("INSERT INTO NEW_ORDER (NO_O_ID, NO_D_ID, NO_W_ID) "
                + "VALUES (?, ?, ?)");
        prep.setInt(1, oId);
        prep.setInt(2, dId);
        prep.setInt(3, warehouseId);
        db.update(prep, "insertNewOrder");
        db.commit();
    }

    private void processPayment() throws Exception {
        int dId = random.getInt(1, bench.districtsPerWarehouse);
        int wId, cdId;
        if (bench.warehouses > 1 && random.getInt(1, 100) <= 15) {
            do {
                wId = random.getInt(1, bench.warehouses);
            } while (wId != warehouseId);
            cdId = random.getInt(1, bench.districtsPerWarehouse);
        } else {
            wId = warehouseId;
            cdId = dId;
        }
        boolean byName;
        String last;
        int cId = 1;
        if (random.getInt(1, 100) <= 60) {
            byName = true;
            last = random.getLastname(random.getNonUniform(255, 0, 999));
        } else {
            byName = false;
            last = "";
            cId = random.getNonUniform(1023, 1, bench.customersPerDistrict);
        }
        BigDecimal amount = random.getBigDecimal(random.getInt(100, 500000),
                2);
        Timestamp datetime = new Timestamp(System.currentTimeMillis());
        PreparedStatement prep;
        ResultSet rs;

        prep = prepare("UPDATE DISTRICT SET D_YTD = D_YTD+? "
                + "WHERE D_ID=? AND D_W_ID=?");
        prep.setBigDecimal(1, amount);
        prep.setInt(2, dId);
        prep.setInt(3, warehouseId);
        db.update(prep, "updateDistrict");
        prep = prepare("UPDATE WAREHOUSE SET W_YTD=W_YTD+? WHERE W_ID=?");
        prep.setBigDecimal(1, amount);
        prep.setInt(2, warehouseId);
        db.update(prep, "updateWarehouse");
        prep = prepare("SELECT W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP, W_NAME "
                + "FROM WAREHOUSE WHERE W_ID=?");
        prep.setInt(1, warehouseId);
        rs = db.query(prep);
        rs.next();
        rs.getString(1); // w_street_1
        rs.getString(2); // w_street_2
        rs.getString(3); // w_city
        rs.getString(4); // w_state
        rs.getString(5); // w_zip
        String wName = rs.getString(6);
        rs.close();
        prep = prepare("SELECT D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP, D_NAME "
                + "FROM DISTRICT WHERE D_ID=? AND D_W_ID=?");
        prep.setInt(1, dId);
        prep.setInt(2, warehouseId);
        rs = db.query(prep);
        rs.next();
        rs.getString(1); // d_street_1
        rs.getString(2); // d_street_2
        rs.getString(3); // d_city
        rs.getString(4); // d_state
        rs.getString(5); // d_zip
        String dName = rs.getString(6);
        rs.close();
        BigDecimal balance;
        String credit;
        if (byName) {
            prep = prepare("SELECT COUNT(C_ID) FROM CUSTOMER "
                    + "WHERE C_LAST=? AND C_D_ID=? AND C_W_ID=?");
            prep.setString(1, last);
            prep.setInt(2, cdId);
            prep.setInt(3, wId);
            rs = db.query(prep);
            rs.next();
            int namecnt = rs.getInt(1);
            rs.close();
            if (namecnt == 0) {
                // TODO TPC-C: check if this can happen
                db.rollback();
                return;
            }
            prep = prepare("SELECT C_FIRST, C_MIDDLE, C_ID, "
                    + "C_STREET_1, C_STREET_2, C_CITY, C_STATE, C_ZIP, "
                    + "C_PHONE, C_CREDIT, C_CREDIT_LIM, "
                    + "C_DISCOUNT, C_BALANCE, C_SINCE FROM CUSTOMER "
                    + "WHERE C_LAST=? AND C_D_ID=? AND C_W_ID=? "
                    + "ORDER BY C_FIRST");
            prep.setString(1, last);
            prep.setInt(2, cdId);