jtpccterminal.java

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

                ol_i_id = itemIDs[ol_number-1];
                ol_quantity = orderQuantities[ol_number-1];

                if (item.i_id == -12345) {
                  // an expected condition generated 1% of the time in the test data...
                  // we throw an illegal access exception and the transaction gets rolled back later on
                  throw new IllegalAccessException("Expected NEW-ORDER error condition excersing rollback functionality");
                }

                  if (stmtGetItem == null) {
                    stmtGetItem = conn.prepareStatement(
                      "SELECT i_price, i_name , i_data FROM item WHERE i_id = ?");
                  }
                  stmtGetItem.setInt(1, ol_i_id);
                  rs = stmtGetItem.executeQuery();
                  if(!rs.next()) throw new IllegalAccessException("I_ID=" + ol_i_id + " not found!");
                  i_price = rs.getFloat("i_price");
                  i_name = rs.getString("i_name");
                  i_data = rs.getString("i_data");
                  rs.close();
                  rs = null;

                itemPrices[ol_number-1] = i_price;
                itemNames[ol_number-1] = i_name;

                if (stmtGetStock == null) {
                  stmtGetStock = conn.prepareStatement(
                      "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 = ? FOR UPDATE");
                }
                stmtGetStock.setInt(1, ol_i_id);
                stmtGetStock.setInt(2, ol_supply_w_id);
                rs = stmtGetStock.executeQuery();
                if(!rs.next()) throw new Exception("I_ID=" + ol_i_id + " not found!");
                s_quantity = rs.getInt("s_quantity");
                s_data = rs.getString("s_data");
                s_dist_01 = rs.getString("s_dist_01");
                s_dist_02 = rs.getString("s_dist_02");
                s_dist_03 = rs.getString("s_dist_03");
                s_dist_04 = rs.getString("s_dist_04");
                s_dist_05 = rs.getString("s_dist_05");
                s_dist_06 = rs.getString("s_dist_06");
                s_dist_07 = rs.getString("s_dist_07");
                s_dist_08 = rs.getString("s_dist_08");
                s_dist_09 = rs.getString("s_dist_09");
                s_dist_10 = rs.getString("s_dist_10");
                rs.close();
                rs = null;

                stockQuantities[ol_number-1] = s_quantity;

                if(s_quantity - ol_quantity >= 10) {
                    s_quantity -= ol_quantity;
                } else {
                    s_quantity += -ol_quantity + 91;
                }

                if(ol_supply_w_id == w_id) {
                    s_remote_cnt_increment = 0;
                } else {
                    s_remote_cnt_increment = 1;
                }


                if (stmtUpdateStock == null) {
                  stmtUpdateStock = conn.prepareStatement(
                    "UPDATE stock SET s_quantity = ? , s_ytd = s_ytd + ?, s_remote_cnt = s_remote_cnt + ? " +
                    " WHERE s_i_id = ? AND s_w_id = ?");
                }
                stmtUpdateStock.setInt(1,s_quantity);
                stmtUpdateStock.setInt(2, ol_quantity);
                stmtUpdateStock.setInt(3,s_remote_cnt_increment);
                stmtUpdateStock.setInt(4,ol_i_id);
                stmtUpdateStock.setInt(5,ol_supply_w_id);
                stmtUpdateStock.addBatch();

                ol_amount = ol_quantity * i_price;
                orderLineAmounts[ol_number-1] = ol_amount;
                total_amount += ol_amount;

                if(i_data.indexOf("GENERIC") != -1 && s_data.indexOf("GENERIC") != -1) {
                    brandGeneric[ol_number-1] = 'B';
                } else {
                    brandGeneric[ol_number-1] = 'G';
                }

                switch((int)d_id) {
                    case 1: ol_dist_info = s_dist_01; break;
                    case 2: ol_dist_info = s_dist_02; break;
                    case 3: ol_dist_info = s_dist_03; break;
                    case 4: ol_dist_info = s_dist_04; break;
                    case 5: ol_dist_info = s_dist_05; break;
                    case 6: ol_dist_info = s_dist_06; break;
                    case 7: ol_dist_info = s_dist_07; break;
                    case 8: ol_dist_info = s_dist_08; break;
                    case 9: ol_dist_info = s_dist_09; break;
                    case 10: ol_dist_info = s_dist_10; break;
                }

                  if (stmtInsertOrderLine == null) {
                    stmtInsertOrderLine = conn.prepareStatement(
                      "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 (?,?,?,?,?,?,?,?,?)");
                  }
                  stmtInsertOrderLine.setInt(1, o_id);
                  stmtInsertOrderLine.setInt(2, d_id);
                  stmtInsertOrderLine.setInt(3, w_id);
                  stmtInsertOrderLine.setInt(4, ol_number);
                  stmtInsertOrderLine.setInt(5, ol_i_id);
                  stmtInsertOrderLine.setInt(6, ol_supply_w_id);
                  stmtInsertOrderLine.setInt(7, ol_quantity);
                  stmtInsertOrderLine.setFloat(8, ol_amount);
                  stmtInsertOrderLine.setString(9, ol_dist_info);
                  stmtInsertOrderLine.addBatch();

            } // end-for

            stmtInsertOrderLine.executeBatch();
            stmtUpdateStock.executeBatch();
            transCommit();
            stmtInsertOrderLine.clearBatch();
            stmtUpdateStock.clearBatch();

            total_amount *= (1+w_tax+d_tax)*(1-c_discount);

            StringBuffer terminalMessage = new StringBuffer();
            terminalMessage.append("\n+--------------------------- NEW-ORDER ---------------------------+\n");
            terminalMessage.append(" Date: ");
            terminalMessage.append(jTPCCUtil.getCurrentTime());
            terminalMessage.append("\n\n Warehouse: ");
            terminalMessage.append(w_id);
            terminalMessage.append("\n   Tax:     ");
            terminalMessage.append(w_tax);
            terminalMessage.append("\n District:  ");
            terminalMessage.append(d_id);
            terminalMessage.append("\n   Tax:     ");
            terminalMessage.append(d_tax);
            terminalMessage.append("\n Order:     ");
            terminalMessage.append(o_id);
            terminalMessage.append("\n   Lines:   ");
            terminalMessage.append(o_ol_cnt);
            terminalMessage.append("\n\n Customer:  ");
            terminalMessage.append(c_id);
            terminalMessage.append("\n   Name:    ");
            terminalMessage.append(c_last);
            terminalMessage.append("\n   Credit:  ");
            terminalMessage.append(c_credit);
            terminalMessage.append("\n   %Disc:   ");
            terminalMessage.append(c_discount);
            terminalMessage.append("\n\n Order-Line List [Supp_W - Item_ID - Item Name - Qty - Stock - B/G - Price - Amount]\n");
            for(int i = 0; i < o_ol_cnt; i++)
            {
                terminalMessage.append("                 [");
                terminalMessage.append(supplierWarehouseIDs[i]);
                terminalMessage.append(" - ");
                terminalMessage.append(itemIDs[i]);
                terminalMessage.append(" - ");
                terminalMessage.append(itemNames[i]);
                terminalMessage.append(" - ");
                terminalMessage.append(orderQuantities[i]);
                terminalMessage.append(" - ");
                terminalMessage.append(stockQuantities[i]);
                terminalMessage.append(" - ");
                terminalMessage.append(brandGeneric[i]);
                terminalMessage.append(" - ");
                terminalMessage.append(jTPCCUtil.formattedDouble(itemPrices[i]));
                terminalMessage.append(" - ");
                terminalMessage.append(jTPCCUtil.formattedDouble(orderLineAmounts[i]));
                terminalMessage.append("]\n");
            }
            terminalMessage.append("\n\n Total Amount: ");
            terminalMessage.append(total_amount);
            terminalMessage.append("\n\n Execution Status: New order placed!\n");
            terminalMessage.append("+-----------------------------------------------------------------+\n\n");
            terminalMessage(terminalMessage.toString());

        } //// ugh :-), this is the end of the try block at the begining of this method /////////

        catch (SQLException ex) {
            System.out.println("\n--- Unexpected SQLException caught in NEW-ORDER Txn ---\n");
            while (ex != null) {
              System.out.println("Message:   " + ex.getMessage ());
              System.out.println("SQLState:  " + ex.getSQLState ());
              System.out.println("ErrorCode: " + ex.getErrorCode ());
              ex = ex.getNextException();
              System.out.println("");
            }

        } catch (Exception e) {
            if (e instanceof IllegalAccessException) {
                StringBuffer terminalMessage = new StringBuffer();
                terminalMessage.append("\n+---- NEW-ORDER Rollback Txn expected to happen for 1% of Txn's -----+");
                terminalMessage.append("\n Warehouse: ");
                terminalMessage.append(w_id);
                terminalMessage.append("\n District:  ");
                terminalMessage.append(d_id);
                terminalMessage.append("\n Order:     ");
                terminalMessage.append(o_id);
                terminalMessage.append("\n\n Customer:  ");
                terminalMessage.append(c_id);
                terminalMessage.append("\n   Name:    ");
                terminalMessage.append(c_last);
                terminalMessage.append("\n   Credit:  ");
                terminalMessage.append(c_credit);
                terminalMessage.append("\n\n Execution Status: Item number is not valid!\n");
                terminalMessage.append("+-----------------------------------------------------------------+\n\n");
                terminalMessage(terminalMessage.toString());
            }

        } finally {
            try {
                terminalMessage("Performing ROLLBACK in NEW-ORDER Txn...");
                transRollback();
                stmtInsertOrderLine.clearBatch();
                stmtUpdateStock.clearBatch();
            } catch(Exception e1){
                error("NEW-ORDER-ROLLBACK");
                logException(e1);
            }
        }
    }


    private void stockLevelTransaction(int w_id, int d_id, int threshold)
    {
        int o_id = 0;
        int i_id = 0;
        int stock_count = 0;

        District  dist = new District();
        OrderLine orln = new OrderLine();
        Stock     stck = new Stock();

        printMessage("Stock Level Txn for W_ID=" + w_id + ", D_ID=" + d_id + ", threshold=" + threshold);

        try
        {
              if (stockGetDistOrderId == null) {
                stockGetDistOrderId = conn.prepareStatement(
                  "SELECT d_next_o_id" +
                  " FROM district" +
                  " WHERE d_w_id = ?" +
                  " AND d_id = ?");
              }
              stockGetDistOrderId.setInt(1, w_id);
              stockGetDistOrderId.setInt(2, d_id);
              rs = stockGetDistOrderId.executeQuery();

              if(!rs.next()) throw new Exception("D_W_ID=" + w_id + " D_ID=" + d_id + " not found!");
              o_id = rs.getInt("d_next_o_id");
              rs.close();
              rs = null;
            printMessage("Next Order ID for District = " + o_id);

              if (stockGetCountStock == null) {
                stockGetCountStock = conn.prepareStatement(
                  "SELECT COUNT(DISTINCT (s_i_id)) AS stock_count" +
                  " FROM order_line, stock" +
                  " WHERE ol_w_id = ?" +
                  " AND ol_d_id = ?" +
                  " AND ol_o_id < ?" +
                  " AND ol_o_id >= ? - 20" +
                  " AND s_w_id = ?" +
                  " AND s_i_id = ol_i_id" +
                  " AND s_quantity < ?");
              }
              stockGetCountStock.setInt(1, w_id);
              stockGetCountStock.setInt(2, d_id);
              stockGetCountStock.setInt(3, o_id);
              stockGetCountStock.setInt(4, o_id);
              stockGetCountStock.setInt(5, w_id);
              stockGetCountStock.setInt(6, threshold);
              rs = stockGetCountStock.executeQuery();

              if(!rs.next()) throw new Exception("OL_W_ID=" + w_id + " OL_D_ID=" + d_id + " OL_O_ID=" + o_id + " (...) not found!");
              stock_count = rs.getInt("stock_count");
              rs.close();
              rs = null;

            StringBuffer terminalMessage = new StringBuffer();
            terminalMessage.append("\n+-------------------------- STOCK-LEVEL --------------------------+");
            terminalMessage.append("\n Warehouse: ");
            terminalMessage.append(w_id);
            terminalMessage.append("\n District:  ");
            terminalMessage.append(d_id);
            terminalMessage.append("\n\n Stock Level Threshold: ");
            terminalMessage.append(threshold);
            terminalMessage.append("\n Low Stock Count:       ");
            terminalMessage.append(stock_count);
            terminalMessage.append("\n+-----------------------------------------------------------------+\n\n");
            terminalMessage(terminalMessage.toString());
        }
        catch(Exception e)
        {
            error("STOCK-LEVEL");