loaddata.java

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

/*
 * Copyright (C) 2004-2006, Denis Lussier
 *
 * LoadData - Load Sample Data directly into database tables or create CSV files for
 *            each table that can then be bulk loaded (again & again & again ...)  :-)
 *
 *    Two optional parameter sets for the command line:
 *
 *                 numWarehouses=9999
 *
 *                 fileLocation=c:/temp/csv/
 *
 *    "numWarehouses" defaults to "1" and when "fileLocation" is omitted the generated
 *    data is loaded into the database tables directly.
 *
 */


import java.sql.*;
import java.util.*;
import java.io.*;
import java.lang.Integer;

import jdbc.jdbcIO;

import pojo.Customer;
import pojo.District;
import pojo.History;
import pojo.Item;
import pojo.NewOrder;
import pojo.Oorder;
import pojo.OrderLine;
import pojo.Stock;
import pojo.Warehouse;

import client.jTPCCConfig;
import client.jTPCCUtil;

public class LoadData implements jTPCCConfig {

  // *********** JDBC specific variables ***********************
  private static Connection         conn       = null;
  private static Statement          stmt       = null;
  private static java.sql.Timestamp sysdate    = null;
  private static PreparedStatement  custPrepStmt;
  private static PreparedStatement  distPrepStmt;
  private static PreparedStatement  histPrepStmt;
  private static PreparedStatement  itemPrepStmt;
  private static PreparedStatement  nworPrepStmt;
  private static PreparedStatement  ordrPrepStmt;
  private static PreparedStatement  orlnPrepStmt;
  private static PreparedStatement  stckPrepStmt;
  private static PreparedStatement  whsePrepStmt;

  // ********** general vars **********************************
  private static java.util.Date     now        = null;
  private static java.util.Date     startDate  = null;
  private static java.util.Date     endDate    = null;

  private static Random             gen;
  private static String             dbType;
  private static int                numWarehouses = 0;
  private static String             fileLocation  = "";
  private static boolean            outputFiles   = false;
  private static PrintWriter        out           = null;
  private static long               lastTimeMS    = 0;

  public static void main(String[] args) {

    //#################### INITIALIZATION #####################################

      System.out.println("----------------- Initialization -------------------");

      numWarehouses = configWhseCount;
      for (int i = 0; i < args.length; i++)
      {
	      System.out.println(args[i]);
	      String str = args[i];
	      if (str.toLowerCase().startsWith("numwarehouses"))
	      {
	         String val = args[i + 1];
                 System.out.println("Setting the number of warehouses to: " + val);
    	         numWarehouses = Integer.parseInt(val);
              }

	      if (str.toLowerCase().startsWith("filelocation"))
	      {
                 fileLocation = args[i + 1];
                 System.out.println("Setting the output file location to: " + fileLocation);
                 outputFiles = true;
              }
      }


      if (outputFiles == false)
      {
        initJDBC();

        // Clearout the tables
        truncateTable("item");
        truncateTable("warehouse");
        truncateTable("stock");
        truncateTable("district");
        truncateTable("customer");
        truncateTable("history");
        truncateTable("oorder");
        truncateTable("order_line");
        truncateTable("new_order");
      }

      // seed the random number generator
      gen = new Random(System.currentTimeMillis());


    //######################### MAINLINE ######################################
      startDate = new java.util.Date();
      System.out.println("------------- LoadData Start Date = " + startDate +
                       "-------------");

      long startTimeMS = new java.util.Date().getTime();
      lastTimeMS = startTimeMS;

      long totalRows = loadWhse(numWarehouses);
      totalRows += loadItem(configItemCount);
      totalRows += loadStock(numWarehouses, configItemCount);
      totalRows += loadDist(numWarehouses, configDistPerWhse);
      totalRows += loadCust(numWarehouses, configDistPerWhse, configCustPerDist);
      totalRows += loadOrder(numWarehouses, configDistPerWhse, configCustPerDist);

      long runTimeMS = (new java.util.Date().getTime()) + 1 - startTimeMS;
      endDate = new java.util.Date();
      System.out.println("");
      System.out.println("------------- LoadJDBC Statistics --------------------");
      System.out.println("     Start Time = " + startDate);
      System.out.println("       End Time = " + endDate);
      System.out.println("       Run Time = " + (int)runTimeMS/1000 + " Seconds");
      System.out.println("    Rows Loaded = " + totalRows + " Rows");
      System.out.println("Rows Per Second = "  + (totalRows/(runTimeMS/1000)) + " Rows/Sec");
      System.out.println("------------------------------------------------------");

      //exit Cleanly
      try {
        if (outputFiles == false)
        {
          if (conn !=null)
             conn.close();
	    }
      } catch(SQLException se) {
        se.printStackTrace();
      } // end try

  } // end main


  static void transRollback () {
      if (outputFiles == false)
      {
        try {
          conn.rollback();
        } catch(SQLException se) {
          System.out.println(se.getMessage());
        }
	  } else {
		  out.close();
      }
  }


  static void transCommit() {
	  if (outputFiles == false)
	  {
        try {
          conn.commit();
        } catch(SQLException se) {
          System.out.println(se.getMessage());
          transRollback();
        }
	  } else {
		  out.close();
      }
  }


  static void truncateTable(String strTable) {

    System.out.println("Truncating '" + strTable + "' ...");
    try {
      stmt.execute("TRUNCATE TABLE " + strTable);
      transCommit();
    } catch(SQLException se) {
      System.out.println(se.getMessage());
      transRollback();
    }

  }



static void initJDBC() {

  try {

    // load the ini file
//    Properties ini = new Properties();
//    ini.load( new FileInputStream(System.getProperty("prop")));
//
//    // display the values we need
//    System.out.println("driver=" + ini.getProperty("driver"));
//    System.out.println("conn=" + ini.getProperty("conn"));
//    System.out.println("user=" + ini.getProperty("user"));
//    System.out.println("password=******");

    // Register jdbcDriver
    //Class.forName(ini.getProperty( "driver" ));
    Class.forName("com.mysql.jdbc.Driver");
    // make connection
//    conn = DriverManager.getConnection(ini.getProperty("conn"),
//      ini.getProperty("user"),ini.getProperty("password"));
  conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test",
     "root","840707");
    conn.setAutoCommit(false);

    // Create Statement
    stmt = conn.createStatement();

    distPrepStmt = conn.prepareStatement
      ("INSERT INTO district " +
       " (d_id, d_w_id, d_ytd, d_tax, d_next_o_id, d_name, d_street_1, d_street_2, d_city, d_state, d_zip) " +
       "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

    itemPrepStmt = conn.prepareStatement
      ("INSERT INTO item " +
       " (i_id, i_name, i_price, i_data, i_im_id) " +
       "VALUES (?, ?, ?, ?, ?)");

    custPrepStmt = conn.prepareStatement
      ("INSERT INTO customer " +
       " (c_id, c_d_id, c_w_id, " +
         "c_discount, c_credit, c_last, c_first, c_credit_lim, " +
         "c_balance, c_ytd_payment, c_payment_cnt, c_delivery_cnt, " +
         "c_street_1, c_street_2, c_city, c_state, c_zip, " +
         "c_phone, c_since, c_middle, c_data) " +
       "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

    histPrepStmt = conn.prepareStatement
      ("INSERT INTO history " +
       " (h_c_id, h_c_d_id, h_c_w_id, " +
         "h_d_id, h_w_id, " +
         "h_date, h_amount, h_data) " +
       "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

    ordrPrepStmt = conn.prepareStatement
      ("INSERT INTO oorder " +
       " (o_id, o_w_id,  o_d_id, o_c_id, " +
         "o_carrier_id, o_ol_cnt, o_all_local, o_entry_d) " +
       "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

    orlnPrepStmt = conn.prepareStatement
      ("INSERT INTO order_line " +
       " (ol_w_id, ol_d_id, ol_o_id, " +
         "ol_number, ol_i_id, ol_delivery_d, " +
         "ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info) " +
       "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

    nworPrepStmt = conn.prepareStatement
      ("INSERT INTO new_order " +
       " (no_w_id, no_d_id, no_o_id) " +
       "VALUES (?, ?, ?)");

    stckPrepStmt = conn.prepareStatement
      ("INSERT INTO stock " +
       " (s_i_id, s_w_id, s_quantity, s_ytd, s_order_cnt, s_remote_cnt, 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) " +
       "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

    whsePrepStmt = conn.prepareStatement
       ("INSERT INTO warehouse " +
        " (w_id, w_ytd, w_tax, w_name, w_street_1, w_street_2, w_city, w_state, w_zip) " +
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

  } catch(SQLException se) {
    System.out.println(se.getMessage());
    transRollback();

  } catch(Exception e) {
    e.printStackTrace();
    transRollback();

  }  // end try

} // end initJDBC()


  static int loadItem(int itemKount) {

      int k = 0;
      int t = 0;
      int randPct = 0;
      int len = 0;
      int startORIGINAL = 0;

      try {

        now = new java.util.Date();
        t = itemKount;
        System.out.println("\nStart Item Load for " + t + " Items @ " + now + " ...");

        if (outputFiles == true)
        {
            out = new PrintWriter(new FileOutputStream(fileLocation + "item.csv"));
            System.out.println("\nWriting Item file to: " + fileLocation + "item.csv");
        }

        Item item  = new Item();

        for (int i=1; i <= itemKount; i++) {

          item.i_id = i;
          item.i_name = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(14,24,gen));
          item.i_price = (float)(jTPCCUtil.randomNumber(100,10000,gen)/100.0);

          // i_data
          randPct = jTPCCUtil.randomNumber(1, 100, gen);
          len = jTPCCUtil.randomNumber(26, 50, gen);
          if ( randPct > 10 ) {
             // 90% of time i_data isa random string of length [26 .. 50]
             item.i_data = jTPCCUtil.randomStr(len);
          } else {
            // 10% of time i_data has "ORIGINAL" crammed somewhere in middle
            startORIGINAL = jTPCCUtil.randomNumber(2, (len - 8), gen);
            item.i_data =
              jTPCCUtil.randomStr(startORIGINAL - 1) +
              "ORIGINAL" +
              jTPCCUtil.randomStr(len - startORIGINAL - 9);
          }

          item.i_im_id = jTPCCUtil.randomNumber(1, 10000, gen);

          k++;

          if (outputFiles == false)
          {
            itemPrepStmt.setLong(1, item.i_id);
            itemPrepStmt.setString(2, item.i_name);
            itemPrepStmt.setDouble(3, item.i_price);
            itemPrepStmt.setString(4, item.i_data);
            itemPrepStmt.setLong(5, item.i_im_id);
            itemPrepStmt.addBatch();

            if (( k % configCommitCount) == 0) {
              long tmpTime = new java.util.Date().getTime();
              String etStr = "  Elasped Time(ms): " + ((tmpTime - lastTimeMS)/1000.000) + "                    ";
              System.out.println(etStr.substring(0, 30) + "  Writing record " + k + " of " + t);
              lastTimeMS = tmpTime;
              itemPrepStmt.executeBatch();
              itemPrepStmt.clearBatch();
              transCommit();
            }
	      } else {
			String str = "";
            str = str + item.i_id + ",";
            str = str + item.i_name + ",";
            str = str + item.i_price + ",";
            str = str + item.i_data + ",";
            str = str + item.i_im_id;
    		out.println(str);

            if (( k % configCommitCount) == 0) {
              long tmpTime = new java.util.Date().getTime();
              String etStr = "  Elasped Time(ms): " + ((tmpTime - lastTimeMS)/1000.000) + "                    ";
              System.out.println(etStr.substring(0, 30) + "  Writing record " + k + " of " + t);
              lastTimeMS = tmpTime;
		    }
		  }

        } // end for

        long tmpTime = new java.util.Date().getTime();