jtpccterminal.java

业界最为经典的SQL性能测试工具

                terminalMessage("\nStarting transaction #" + transactionCount + " (Payment)...");
                paymentTransaction(terminalWarehouseID, customerWarehouseID, paymentAmount, districtID, customerDistrictID, customerID, customerLastName, customerByName);
                break;


            case STOCK_LEVEL:
                int threshold = jTPCCUtil.randomNumber(10, 20, gen);

                terminalMessage("\nStarting transaction #" + transactionCount + " (Stock-Level)...");
                stockLevelTransaction(terminalWarehouseID, terminalDistrictID, threshold);
                break;


            case ORDER_STATUS:
                districtID = jTPCCUtil.randomNumber(1, 10, gen);

                y = jTPCCUtil.randomNumber(1, 100, gen);
                customerLastName = null;
                customerID = -1;
                if(y <= 60)
                {
                    customerByName = true;
                    customerLastName = jTPCCUtil.getLastName(gen);
                }
                else
                {
                    customerByName = false;
                    customerID = jTPCCUtil.getCustomerID(gen);
                }

                terminalMessage("\nStarting transaction #" + transactionCount + " (Order-Status)...");
                orderStatusTransaction(terminalWarehouseID, districtID, customerID, customerLastName, customerByName);
                break;


            case DELIVERY:
                int orderCarrierID = jTPCCUtil.randomNumber(1, 10, gen);

                terminalMessage("\nStarting transaction #" + transactionCount + " (Delivery)...");
                result = deliveryTransaction(terminalWarehouseID, orderCarrierID);
                break;


            default:
                error("EMPTY-TYPE");
                break;
        }
        transactionCount++;

        return result;
    }


    private int deliveryTransaction(int w_id, int o_carrier_id)
    {
        int d_id, no_o_id, c_id;
        float ol_total;
        int[] orderIDs;
        int skippedDeliveries = 0;
        boolean newOrderRemoved;

        Oorder oorder = new Oorder();
        OrderLine order_line = new OrderLine();
        NewOrder new_order = new NewOrder();
        new_order.no_w_id = w_id;

        try
        {
            orderIDs = new int[10];
            for(d_id = 1; d_id <= 10; d_id++)
            {
                new_order.no_d_id = d_id;

                do
                {
                    no_o_id = -1;

                    if (delivGetOrderId == null) {
                      delivGetOrderId = conn.prepareStatement(
                        "SELECT no_o_id FROM new_order WHERE no_d_id = ?" +
                        " AND no_w_id = ?" +
                        " ORDER BY no_o_id ASC");
                    }
                    delivGetOrderId.setInt(1, d_id);
                    delivGetOrderId.setInt(2, w_id);
                    rs = delivGetOrderId.executeQuery();
                    if(rs.next()) no_o_id = rs.getInt("no_o_id");
                    orderIDs[(int)d_id-1] = no_o_id;
                    rs.close();
                    rs = null;

                    newOrderRemoved = false;
                    if(no_o_id != -1)
                    {
                      new_order.no_o_id = no_o_id;

                      if (delivDeleteNewOrder == null) {
                        delivDeleteNewOrder = conn.prepareStatement(
                          "DELETE FROM new_order" +
                          " WHERE no_d_id = ?" +
                          " AND no_w_id = ?" +
                          " AND no_o_id = ?");
                      }
                      delivDeleteNewOrder.setInt(1, d_id);
                      delivDeleteNewOrder.setInt(2, w_id);
                      delivDeleteNewOrder.setInt(3, no_o_id);
                      result = delivDeleteNewOrder.executeUpdate();

                      if(result > 0) newOrderRemoved = true;
                    }
                }
                while(no_o_id != -1 && !newOrderRemoved);


                if(no_o_id != -1)
                {
                      if (delivGetCustId == null) {
                        delivGetCustId = conn.prepareStatement(
                          "SELECT o_c_id" +
                          " FROM oorder" +
                          " WHERE o_id = ?" +
                          " AND o_d_id = ?" +
                          " AND o_w_id = ?");
                      }
                      delivGetCustId.setInt(1, no_o_id);
                      delivGetCustId.setInt(2, d_id);
                      delivGetCustId.setInt(3, w_id);
                      rs = delivGetCustId.executeQuery();

                      if(!rs.next()) throw new Exception("O_ID=" + no_o_id + " O_D_ID=" + d_id + " O_W_ID=" + w_id + " not found!");
                      c_id = rs.getInt("o_c_id");
                      rs.close();
                      rs = null;

                      if (delivUpdateCarrierId == null) {
                        delivUpdateCarrierId = conn.prepareStatement(
                          "UPDATE oorder SET o_carrier_id = ?" +
                          " WHERE o_id = ?" +
                          " AND o_d_id = ?" +
                          " AND o_w_id = ?");
                      }
                      delivUpdateCarrierId.setInt(1, o_carrier_id);
                      delivUpdateCarrierId.setInt(2, no_o_id);
                      delivUpdateCarrierId.setInt(3, d_id);
                      delivUpdateCarrierId.setInt(4, w_id);
                      result = delivUpdateCarrierId.executeUpdate();

                    if(result != 1) throw new Exception("O_ID=" + no_o_id + " O_D_ID=" + d_id + " O_W_ID=" + w_id + " not found!");

                      if (delivUpdateDeliveryDate == null) {
                        delivUpdateDeliveryDate = conn.prepareStatement(
                          "UPDATE order_line SET ol_delivery_d = ?" +
                          " WHERE ol_o_id = ?" +
                          " AND ol_d_id = ?" +
                          " AND ol_w_id = ?");
                      }
                      delivUpdateDeliveryDate.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
                      delivUpdateDeliveryDate.setInt(2,no_o_id);
                      delivUpdateDeliveryDate.setInt(3,d_id);
                      delivUpdateDeliveryDate.setInt(4,w_id);
                      result = delivUpdateDeliveryDate.executeUpdate();

                      if(result == 0) throw new Exception("OL_O_ID=" + no_o_id + " OL_D_ID=" + d_id + " OL_W_ID=" + w_id + " not found!");

                      if (delivSumOrderAmount == null) {
                        delivSumOrderAmount = conn.prepareStatement(
                          "SELECT SUM(ol_amount) AS ol_total" +
                          " FROM order_line" +
                          " WHERE ol_o_id = ?" +
                          " AND ol_d_id = ?" +
                          " AND ol_w_id = ?");
                      }
                      delivSumOrderAmount.setInt(1, no_o_id);
                      delivSumOrderAmount.setInt(2, d_id);
                      delivSumOrderAmount.setInt(3, w_id);
                      rs = delivSumOrderAmount.executeQuery();

                      if(!rs.next()) throw new Exception("OL_O_ID=" + no_o_id + " OL_D_ID=" + d_id + " OL_W_ID=" + w_id + " not found!");
                      ol_total = rs.getFloat("ol_total");
                      rs.close();
                      rs = null;

                    if (delivUpdateCustBalDelivCnt == null) {
                      delivUpdateCustBalDelivCnt = conn.prepareStatement(
                        "UPDATE customer SET c_balance = c_balance + ?" +
                        ", c_delivery_cnt = c_delivery_cnt + 1" +
                        " WHERE c_id = ?" +
                        " AND c_d_id = ?" +
                        " AND c_w_id = ?");
                    }
                    delivUpdateCustBalDelivCnt.setFloat(1, ol_total);
                    delivUpdateCustBalDelivCnt.setInt(2, c_id);
                    delivUpdateCustBalDelivCnt.setInt(3, d_id);
                    delivUpdateCustBalDelivCnt.setInt(4, w_id);
                    result = delivUpdateCustBalDelivCnt.executeUpdate();

                    if(result == 0) throw new Exception("C_ID=" + c_id + " C_W_ID=" + w_id + " C_D_ID=" + d_id + " not found!");
                }
            }

            conn.commit();

            StringBuffer terminalMessage = new StringBuffer();
            terminalMessage.append("\n+---------------------------- DELIVERY ---------------------------+\n");
            terminalMessage.append(" Date: ");
            terminalMessage.append(jTPCCUtil.getCurrentTime());
            terminalMessage.append("\n\n Warehouse: ");
            terminalMessage.append(w_id);
            terminalMessage.append("\n Carrier:   ");
            terminalMessage.append(o_carrier_id);
            terminalMessage.append("\n\n Delivered Orders\n");
            for(int i = 1; i <= 10; i++)
            {
                if(orderIDs[i-1] >= 0)
                {
                    terminalMessage.append("  District ");
                    terminalMessage.append(i < 10 ? " " : "");
                    terminalMessage.append(i);
                    terminalMessage.append(": Order number ");
                    terminalMessage.append(orderIDs[i-1]);
                    terminalMessage.append(" was delivered.\n");
                }
                else
                {
                    terminalMessage.append("  District ");
                    terminalMessage.append(i < 10 ? " " : "");
                    terminalMessage.append(i);
                    terminalMessage.append(": No orders to be delivered.\n");
                    skippedDeliveries++;
                }
            }
            terminalMessage.append("+-----------------------------------------------------------------+\n\n");
            terminalMessage(terminalMessage.toString());
        }
        catch(Exception e)
        {
            error("DELIVERY");
            logException(e);
            try
            {
                terminalMessage("Performing ROLLBACK...");
                conn.rollback();
            }
            catch(Exception e1)
            {
                error("DELIVERY-ROLLBACK");
                logException(e1);
            }
        }

        return skippedDeliveries;
    }


    private void orderStatusTransaction(int w_id, int d_id, int c_id, String c_last, boolean c_by_name)
    {
        int namecnt, o_id = -1, o_carrier_id = -1;
        float c_balance;
        String c_first, c_middle;
        java.sql.Date entdate = null;
        Vector orderLines = new Vector();


        try
        {
            if(c_by_name)
            {
                if (ordStatCountCust == null) {
                  ordStatCountCust = conn.prepareStatement(
                    "SELECT count(*) AS namecnt FROM customer" +
                    " WHERE c_last = ?" +
                    " AND c_d_id = ?" +
                    " AND c_w_id = ?");
                }
                ordStatCountCust.setString(1, c_last);
                ordStatCountCust.setInt(2, d_id);
                ordStatCountCust.setInt(3, w_id);
                rs = ordStatCountCust.executeQuery();

                if(!rs.next()) throw new Exception("C_LAST=" + c_last + " C_D_ID=" + d_id + " C_W_ID=" + w_id + " not found!");
                namecnt = rs.getInt("namecnt");
                rs.close();
                rs = null;

                // pick the middle customer from the list of customers

                if (ordStatGetCust == null) {
                  ordStatGetCust = conn.prepareStatement(
                    "SELECT c_balance, c_first, c_middle, c_id FROM customer" +
                    " WHERE c_last = ?" +
                    " AND c_d_id = ?" +
                    " AND c_w_id = ?" +
                    " ORDER BY c_w_id, c_d_id, c_last, c_first");
                }
                ordStatGetCust.setString(1, c_last);