jtpccterminal.java

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

            logException(e);
        }
    }


    private void paymentTransaction(int w_id, int c_w_id, float h_amount, int d_id, int c_d_id, int c_id, String c_last, boolean c_by_name)
    {
        String w_street_1, w_street_2, w_city, w_state, w_zip, w_name;
        String d_street_1, d_street_2, d_city, d_state, d_zip, d_name;
        int namecnt;
        String c_first, c_middle, c_street_1, c_street_2, c_city, c_state, c_zip;
        String c_phone, c_credit = null, c_data = null, c_new_data, h_data;
        float c_credit_lim, c_discount, c_balance = 0;
        java.sql.Date c_since;

        Warehouse whse = new Warehouse();
        Customer  cust = new Customer();
        District  dist = new District();
        History   hist = new History();

        try
        {

              if (payUpdateWhse == null) {
                payUpdateWhse = conn.prepareStatement(
                  "UPDATE warehouse SET w_ytd = w_ytd + ?  WHERE w_id = ? ");
              }
              payUpdateWhse.setFloat(1,h_amount);
              payUpdateWhse.setInt(2,w_id);
              result = payUpdateWhse.executeUpdate();
              if(result == 0) throw new Exception("W_ID=" + w_id + " not found!");

              if (payGetWhse == null) {
                payGetWhse = conn.prepareStatement(
                  "SELECT w_street_1, w_street_2, w_city, w_state, w_zip, w_name" +
                  " FROM warehouse WHERE w_id = ?");
              }
              payGetWhse.setInt(1, w_id);
              rs = payGetWhse.executeQuery();
              if(!rs.next()) throw new Exception("W_ID=" + w_id + " not found!");
              w_street_1 = rs.getString("w_street_1");
              w_street_2 = rs.getString("w_street_2");
              w_city = rs.getString("w_city");
              w_state = rs.getString("w_state");
              w_zip = rs.getString("w_zip");
              w_name = rs.getString("w_name");
              rs.close();
              rs = null;

              if (payUpdateDist == null) {
                payUpdateDist = conn.prepareStatement(
                  "UPDATE district SET d_ytd = d_ytd + ? WHERE d_w_id = ? AND d_id = ?");
              }
              payUpdateDist.setFloat(1, h_amount);
              payUpdateDist.setInt(2, w_id);
              payUpdateDist.setInt(3, d_id);
              result = payUpdateDist.executeUpdate();
              if(result == 0) throw new Exception("D_ID=" + d_id + " D_W_ID=" + w_id + " not found!");

              if (payGetDist == null) {
                payGetDist = conn.prepareStatement(
                  "SELECT d_street_1, d_street_2, d_city, d_state, d_zip, d_name" +
                  " FROM district WHERE d_w_id = ? AND d_id = ?");
              }
              payGetDist.setInt(1, w_id);
              payGetDist.setInt(2, d_id);
              rs = payGetDist.executeQuery();
              if(!rs.next()) throw new Exception("D_ID=" + d_id + " D_W_ID=" + w_id + " not found!");
              d_street_1 = rs.getString("d_street_1");
              d_street_2 = rs.getString("d_street_2");
              d_city = rs.getString("d_city");
              d_state = rs.getString("d_state");
              d_zip = rs.getString("d_zip");
              d_name = rs.getString("d_name");
              rs.close();
              rs = null;

            if(c_by_name) {
                // payment is by customer name
                  if (payCountCust == null) {
                    payCountCust = conn.prepareStatement(
                      "SELECT count(c_id) AS namecnt FROM customer " +
                      " WHERE c_last = ?  AND c_d_id = ? AND c_w_id = ?");
                  }
                  payCountCust.setString(1, c_last);
                  payCountCust.setInt(2, c_d_id);
                  payCountCust.setInt(3, c_w_id);
                  rs = payCountCust.executeQuery();
                  if(!rs.next()) throw new Exception("C_LAST=" + c_last + " C_D_ID=" + c_d_id + " C_W_ID=" + c_w_id + " not found!");
                  namecnt = rs.getInt("namecnt");
                  rs.close();
                  rs = null;

                if (payCursorCustByName == null) {
                  payCursorCustByName = conn.prepareStatement(
                    "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_w_id = ? AND c_d_id = ? AND c_last = ? " +
                    "ORDER BY c_w_id, c_d_id, c_last, c_first ");
                }
                payCursorCustByName.setInt(1, c_w_id);
                payCursorCustByName.setInt(2, c_d_id);
                payCursorCustByName.setString(3, c_last);
                rs = payCursorCustByName.executeQuery();
                if(!rs.next()) throw new Exception("C_LAST=" + c_last + " C_D_ID=" + c_d_id + " C_W_ID=" + c_w_id + " not found!");
                if(namecnt%2 == 1) namecnt++;
                for(int i = 1; i < namecnt / 2; i++) rs.next();
                c_id = rs.getInt("c_id");
                c_first = rs.getString("c_first");
                c_middle = rs.getString("c_middle");
                c_street_1 = rs.getString("c_street_1");
                c_street_2 = rs.getString("c_street_2");
                c_city = rs.getString("c_city");
                c_state = rs.getString("c_state");
                c_zip = rs.getString("c_zip");
                c_phone = rs.getString("c_phone");
                c_credit = rs.getString("c_credit");
                c_credit_lim = rs.getFloat("c_credit_lim");
                c_discount = rs.getFloat("c_discount");
                c_balance = rs.getFloat("c_balance");
                c_since = rs.getDate("c_since");
                rs.close();
                rs = null;
            } else {
              // payment is by customer ID
                if (payGetCust == null) {
                  payGetCust = conn.prepareStatement(
                    "SELECT c_first, c_middle, c_last, 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_w_id = ? AND c_d_id = ? AND c_id = ?");
                }
                payGetCust.setInt(1, c_w_id);
                payGetCust.setInt(2, c_d_id);
                payGetCust.setInt(3, c_id);
                rs = payGetCust.executeQuery();
                if(!rs.next()) throw new Exception("C_ID=" + c_id + " C_D_ID=" + c_d_id + " C_W_ID=" + c_w_id + " not found!");
                c_last = rs.getString("c_last");
                c_first = rs.getString("c_first");
                c_middle = rs.getString("c_middle");
                c_street_1 = rs.getString("c_street_1");
                c_street_2 = rs.getString("c_street_2");
                c_city = rs.getString("c_city");
                c_state = rs.getString("c_state");
                c_zip = rs.getString("c_zip");
                c_phone = rs.getString("c_phone");
                c_credit = rs.getString("c_credit");
                c_credit_lim = rs.getFloat("c_credit_lim");
                c_discount = rs.getFloat("c_discount");
                c_balance = rs.getFloat("c_balance");
                c_since = rs.getDate("c_since");
                rs.close();
                rs = null;
            }


            c_balance += h_amount;
            if(c_credit.equals("BC")) {  // bad credit

                if (payGetCustCdata == null) {
                  payGetCustCdata = conn.prepareStatement(
                    "SELECT c_data FROM customer WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?");
                }
                payGetCustCdata.setInt(1, c_w_id);
                payGetCustCdata.setInt(2, c_d_id);
                payGetCustCdata.setInt(3, c_id);
                rs = payGetCustCdata.executeQuery();
                if(!rs.next()) throw new Exception("C_ID=" + c_id + " C_W_ID=" + c_w_id + " C_D_ID=" + c_d_id + " not found!");
                c_data = rs.getString("c_data");
                rs.close();
                rs = null;

              c_new_data = c_id + " " + c_d_id + " " + c_w_id + " " + d_id + " " + w_id  + " " + h_amount + " |";
              if(c_data.length() > c_new_data.length()) {
                  c_new_data += c_data.substring(0, c_data.length()-c_new_data.length());
              } else {
                  c_new_data += c_data;
              }
              if(c_new_data.length() > 500) c_new_data = c_new_data.substring(0, 500);

                if (payUpdateCustBalCdata == null) {
                  payUpdateCustBalCdata = conn.prepareStatement(
                    "UPDATE customer SET c_balance = ?, c_data = ? " +
                    " WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?");
                }
                payUpdateCustBalCdata.setFloat(1, c_balance);
                payUpdateCustBalCdata.setString(2, c_new_data);
                payUpdateCustBalCdata.setInt(3, c_w_id);
                payUpdateCustBalCdata.setInt(4, c_d_id);
                payUpdateCustBalCdata.setInt(5, c_id);
                result = payUpdateCustBalCdata.executeUpdate();

              if(result == 0) throw new Exception("Error in PYMNT Txn updating Customer C_ID=" + c_id + " C_W_ID=" + c_w_id + " C_D_ID=" + c_d_id);

            } else { // GoodCredit

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

              if(result == 0) throw new Exception("C_ID=" + c_id + " C_W_ID=" + c_w_id + " C_D_ID=" + c_d_id + " not found!");

            }


            if(w_name.length() > 10) w_name = w_name.substring(0, 10);
            if(d_name.length() > 10) d_name = d_name.substring(0, 10);
            h_data = w_name + "    " + d_name;


            if (payInsertHist == null) {
              payInsertHist = conn.prepareStatement(
                "INSERT INTO history (h_c_d_id, h_c_w_id, h_c_id, h_d_id, h_w_id, h_date, h_amount, h_data) " +
                " VALUES (?,?,?,?,?,?,?,?)");
            }
            payInsertHist.setInt(1, c_d_id);
            payInsertHist.setInt(2, c_w_id);
            payInsertHist.setInt(3, c_id);
            payInsertHist.setInt(4, d_id);
            payInsertHist.setInt(5, w_id);
            payInsertHist.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
            payInsertHist.setFloat(7, h_amount);
            payInsertHist.setString(8, h_data);
            payInsertHist.executeUpdate();

            transCommit();

            StringBuffer terminalMessage = new StringBuffer();
            terminalMessage.append("\n+---------------------------- PAYMENT ----------------------------+");
            terminalMessage.append("\n Date: " + jTPCCUtil.getCurrentTime());
            terminalMessage.append("\n\n Warehouse: ");
            terminalMessage.append(w_id);
            terminalMessage.append("\n   Street:  ");
            terminalMessage.append(w_street_1);
            terminalMessage.append("\n   Street:  ");
            terminalMessage.append(w_street_2);
            terminalMessage.append("\n   City:    ");
            terminalMessage.append(w_city);
            terminalMessage.append("   State: ");
            terminalMessage.append(w_state);
            terminalMessage.append("  Zip: ");
            terminalMessage.append(w_zip);
            terminalMessage.append("\n\n District:  ");
            terminalMessage.append(d_id);
            terminalMessage.append("\n   Street:  ");
            terminalMessage.append(d_street_1);
            terminalMessage.append("\n   Street:  ");
            terminalMessage.append(d_street_2);
            terminalMessage.append("\n   City:    ");
            terminalMessage.append(d_city);
            terminalMessage.append("   State: ");
            terminalMessage.append(d_state);
            terminalMessage.append("  Zip: ");
            terminalMessage.append(d_zip);
            terminalMessage.append("\n\n Customer:  ");
            terminalMessage.append(c_id);
            terminalMessage.append("\n   Name:    ");
            terminalMessage.append(c_first);
            terminalMessage.append(" ");
            terminalMessage.append(c_middle);
            terminalMessage.append(" ");
            terminalMessage.append(c_last);
            terminalMessage.append("\n   Street:  ");
            terminalMessage.append(c_street_1);
            terminalMessage.append("\n   Street:  ");
            terminalMessage.append(c_street_2);
            terminalMessage.append("\n   City:    ");
            terminalMessage.append(c_city);
            terminalMessage.append("   State: ");
            terminalMessage.append(c_state);
            terminalMessage.append("  Zip: ");
            terminalMessage.append(c_zip);
            terminalMessage.append("\n   Since:   ");
            if (c_since != null)
            {
              terminalMessage.append(c_since.toString());
		    } else {
              terminalMessage.append("");
			}
            terminalMessage.append("\n   Credit:  ");
            terminalMessage.append(c_credit);
            terminalMessage.append("\n   %Disc:   ");
            terminalMessage.append(c_discount);
            terminalMessage.append("\n   Phone:   ");
            terminalMessage.append(c_phone);
            terminalMessage.append("\n\n Amount Paid:      ");
            terminalMessage.append(h_amount);
            terminalMessage.append("\n Credit Limit:     ");
            terminalMessage.append(c_credit_lim);