public void doSQL() throws Exception {
    Class.forName("org.sqlite.JDBC");
    Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
    Statement stat = conn.createStatement();
    stat.executeUpdate("drop table if exists people;");
    stat.executeUpdate("create table people (name, occupation);");
    PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");

    prep.setString(1, "Gandhi");
    prep.setString(2, "politics");
    prep.addBatch();
    prep.setString(1, "Turing");
    prep.setString(2, "computers");
    prep.addBatch();
    prep.setString(1, "Wittgenstein");
    prep.setString(2, "smartypants");
    prep.addBatch();

    conn.setAutoCommit(false);
    prep.executeBatch();
    conn.setAutoCommit(true);

    ResultSet rs = stat.executeQuery("select * from people;");
    while (rs.next()) {
      System.out.println("name = " + rs.getString("name"));
      System.out.println("job = " + rs.getString("occupation"));
    }
    rs.close();
    conn.close();
  }
Beispiel #2
0
 /** Test batched prepared statements. */
 public void testPrepStmtBatch() throws Exception {
   Statement stmt = con.createStatement();
   stmt.execute("create table #testbatch (id int, data varchar(255))");
   PreparedStatement pstmt = con.prepareStatement("INSERT INTO #testbatch VALUES (?, ?)");
   for (int i = 0; i < 5; i++) {
     if (i == 2) {
       pstmt.setString(1, "xxx");
     } else {
       pstmt.setInt(1, i);
     }
     pstmt.setString(2, "This is line " + i);
     pstmt.addBatch();
   }
   int x[];
   try {
     x = pstmt.executeBatch();
   } catch (BatchUpdateException e) {
     x = e.getUpdateCounts();
   }
   if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")) {
     assertEquals(5, x.length);
     assertEquals(1, x[0]);
     assertEquals(1, x[1]);
     assertEquals(EXECUTE_FAILED, x[2]);
     assertEquals(EXECUTE_FAILED, x[3]);
     assertEquals(EXECUTE_FAILED, x[4]);
   } else {
     // Sybase - Entire batch fails due to data conversion error
     // detected in statement 3
     assertEquals(5, x.length);
     assertEquals(EXECUTE_FAILED, x[0]);
     assertEquals(EXECUTE_FAILED, x[1]);
     assertEquals(EXECUTE_FAILED, x[2]);
     assertEquals(EXECUTE_FAILED, x[3]);
     assertEquals(EXECUTE_FAILED, x[4]);
   }
   // Now without errors
   stmt.execute("TRUNCATE TABLE #testbatch");
   for (int i = 0; i < 5; i++) {
     pstmt.setInt(1, i);
     pstmt.setString(2, "This is line " + i);
     pstmt.addBatch();
   }
   x = pstmt.executeBatch();
   assertEquals(5, x.length);
   assertEquals(1, x[0]);
   assertEquals(1, x[1]);
   assertEquals(1, x[2]);
   assertEquals(1, x[3]);
   assertEquals(1, x[4]);
 }
Beispiel #3
0
  /** Test large batch behavior. */
  public void testLargeBatch() throws Exception {
    final int n = 5000;
    getConnection().close();

    Statement stmt = con.createStatement();
    stmt.executeUpdate("create table #testLargeBatch (val int)");
    stmt.executeUpdate("insert into #testLargeBatch (val) values (0)");

    PreparedStatement pstmt = con.prepareStatement("update #testLargeBatch set val=? where val=?");
    for (int i = 0; i < n; i++) {
      pstmt.setInt(1, i + 1);
      pstmt.setInt(2, i);
      pstmt.addBatch();
    }
    int counts[] = pstmt.executeBatch();
    //        System.out.println(pstmt.getWarnings());
    assertEquals(n, counts.length);
    for (int i = 0; i < n; i++) {
      assertEquals(1, counts[i]);
    }
    pstmt.close();

    ResultSet rs = stmt.executeQuery("select count(*) from #testLargeBatch");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertFalse(rs.next());
    rs.close();
    stmt.close();
  }
Beispiel #4
0
 /** Test for bug [1371295] SQL Server continues after duplicate key error. */
 public void testPrepStmtBatchDupKey() throws Exception {
   Statement stmt = con.createStatement();
   stmt.execute("create table #testbatch (id int, data varchar(255), PRIMARY KEY (id))");
   PreparedStatement pstmt = con.prepareStatement("INSERT INTO #testbatch VALUES (?, ?)");
   for (int i = 0; i < 5; i++) {
     if (i == 2) {
       pstmt.setInt(1, 1); // Will cause duplicate key batch will continue
     } else {
       pstmt.setInt(1, i);
     }
     pstmt.setString(2, "This is line " + i);
     pstmt.addBatch();
   }
   int x[];
   try {
     x = pstmt.executeBatch();
   } catch (BatchUpdateException e) {
     x = e.getUpdateCounts();
   }
   assertEquals(5, x.length);
   assertEquals(1, x[0]);
   assertEquals(1, x[1]);
   assertEquals(EXECUTE_FAILED, x[2]);
   assertEquals(1, x[3]);
   assertEquals(1, x[4]);
   // Now without errors
   stmt.execute("TRUNCATE TABLE #testbatch");
   for (int i = 0; i < 5; i++) {
     pstmt.setInt(1, i);
     pstmt.setString(2, "This is line " + i);
     pstmt.addBatch();
   }
   x = pstmt.executeBatch();
   assertEquals(5, x.length);
   assertEquals(1, x[0]);
   assertEquals(1, x[1]);
   assertEquals(1, x[2]);
   assertEquals(1, x[3]);
   assertEquals(1, x[4]);
 }
Beispiel #5
0
 public void run() {
   try {
     PreparedStatement pstmt =
         con.prepareStatement(
             "insert into #testConcurrentBatch (v1, v2, v3, v4, v5, v6) values (?, ?, ?, ?, ?, ?)");
     for (int i = 0; i < 64; ++i) {
       // Make sure we end up with 64 different prepares, use the binary representation of i to
       // set each
       // of the 6 parameters to either an int or a string.
       int mask = i;
       for (int j = 1; j <= 6; ++j, mask >>= 1) {
         if ((mask & 1) != 0) {
           pstmt.setInt(j, i);
         } else {
           pstmt.setString(j, String.valueOf(i));
         }
       }
       pstmt.addBatch();
     }
     int x[];
     try {
       x = pstmt.executeBatch();
     } catch (BatchUpdateException e) {
       e.printStackTrace();
       x = e.getUpdateCounts();
     }
     if (x.length != 64) {
       throw new SQLException("Expected 64 update counts, got " + x.length);
     }
     for (int i = 0; i < x.length; ++i) {
       if (x[i] != 1) {
         throw new SQLException("Error at position " + i + ", got " + x[i] + " instead of 1");
       }
     }
     // Rollback the transaction, exposing any race conditions.
     con.rollback();
     pstmt.close();
   } catch (SQLException ex) {
     ex.printStackTrace();
     exceptions.add(ex);
   }
 }
Beispiel #6
0
 /** Test for PreparedStatement batch with no parameters. */
 public void testPrepStmtNoParams() throws Exception {
   Statement stmt = con.createStatement();
   stmt.execute(
       "create table #testbatch (id numeric(10) identity, data varchar(255), PRIMARY KEY (id))");
   PreparedStatement pstmt =
       con.prepareStatement("INSERT INTO #testbatch (data) VALUES ('Same each time')");
   for (int i = 0; i < 5; i++) {
     pstmt.addBatch();
   }
   int x[];
   try {
     x = pstmt.executeBatch();
   } catch (BatchUpdateException e) {
     x = e.getUpdateCounts();
   }
   assertEquals(5, x.length);
   assertEquals(1, x[0]);
   assertEquals(1, x[1]);
   assertEquals(1, x[2]);
   assertEquals(1, x[3]);
   assertEquals(1, x[4]);
 }
Beispiel #7
0
 /** Test for PreparedStatement batch with variable parameter types. */
 public void testPrepStmtVariableParams() throws Exception {
   Statement stmt = con.createStatement();
   stmt.execute("create table #testbatch (id int, data int, PRIMARY KEY (id))");
   PreparedStatement pstmt =
       con.prepareStatement("INSERT INTO #testbatch VALUES (?, convert(int, ?))");
   for (int i = 0; i < 5; i++) {
     pstmt.setInt(1, i);
     if (i == 2) {
       // This statement will require a string param instead of an int
       pstmt.setString(2, "123");
     } else {
       pstmt.setInt(2, 123);
     }
     pstmt.addBatch();
   }
   int x[];
   try {
     x = pstmt.executeBatch();
   } catch (BatchUpdateException e) {
     x = e.getUpdateCounts();
   }
   assertEquals(5, x.length);
   assertEquals(1, x[0]);
   assertEquals(1, x[1]);
   assertEquals(1, x[2]);
   assertEquals(1, x[3]);
   assertEquals(1, x[4]);
   ResultSet rs = stmt.executeQuery("SELECT * FROM #testbatch");
   assertNotNull(rs);
   int i = 0;
   while (rs.next()) {
     assertEquals(123, rs.getInt(2));
     i++;
   }
   assertEquals(5, i);
 }
Beispiel #8
0
  /** this is a test for the data truncation problem described in bug [2731952] */
  public void testDataTruncation() throws SQLException {
    Statement stmt = con.createStatement();
    stmt.execute("CREATE TABLE #DATATRUNC (id int, data text)");
    stmt.close();

    // create 2 different strings
    StringBuilder sb1 = new StringBuilder(10000);
    StringBuilder sb2 = new StringBuilder(100);

    for (int i = 1; i <= 1000; i++) {
      sb1.append(" +++ ")
          .append("    ".substring(String.valueOf(i).length()))
          .append(i)
          .append("\n");
    }

    for (int i = 1; i <= 10; i++) {
      sb2.append(" --- ")
          .append("    ".substring(String.valueOf(i).length()))
          .append(i)
          .append("\n");
    }

    String string1 = sb1.toString();
    String string2 = sb2.toString();

    PreparedStatement pstmt =
        con.prepareStatement("insert into #DATATRUNC (id, data) values (?, ?)");

    // insert both values into DB in batch mode
    pstmt.setInt(1, 1);
    pstmt.setString(2, string1);
    pstmt.addBatch();

    pstmt.setInt(1, 2);
    pstmt.setString(2, string2);
    pstmt.addBatch();

    assertTrue(Arrays.equals(new int[] {1, 1}, pstmt.executeBatch()));

    // insert first string again, no batch
    pstmt.setInt(1, 3);
    pstmt.setString(2, string1);

    assertEquals(1, pstmt.executeUpdate());
    pstmt.close();

    // ensure all 3 entries are still intact
    stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("select data from #DATATRUNC order by id asc");

    // 1st value, should be string1
    assertTrue(rs.next());
    String value = rs.getString(1);
    assertEquals(string1.length(), value.length());
    assertEquals(string1, value);

    // 2nd value, should be string2
    assertTrue(rs.next());
    value = rs.getString(1);
    assertEquals(string2.length(), value.length());
    assertEquals(string2, value);

    // 3rd value, should be string1
    assertTrue(rs.next());
    value = rs.getString(1);
    assertEquals(string1.length(), value.length());
    assertEquals(string1, value);

    rs.close();
    stmt.close();
  }
Beispiel #9
0
  static int loadCust(int whseKount, int distWhseKount, int custDistKount) {

    int k = 0;
    int t = 0;

    Customer customer = new Customer();
    History history = new History();
    PrintWriter outHist = null;

    try {

      now = new java.util.Date();

      if (outputFiles == true) {
        out = new PrintWriter(new FileOutputStream(fileLocation + "customer.csv"));
        System.out.println("\nWriting Customer file to: " + fileLocation + "customer.csv");
        outHist = new PrintWriter(new FileOutputStream(fileLocation + "cust-hist.csv"));
        System.out.println("\nWriting Customer History file to: " + fileLocation + "cust-hist.csv");
      }

      t = (whseKount * distWhseKount * custDistKount * 2);
      System.out.println("\nStart Cust-Hist Load for " + t + " Cust-Hists @ " + now + " ...");

      for (int w = 1; w <= whseKount; w++) {

        for (int d = 1; d <= distWhseKount; d++) {

          for (int c = 1; c <= custDistKount; c++) {

            sysdate = new java.sql.Timestamp(System.currentTimeMillis());

            customer.c_id = c;
            customer.c_d_id = d;
            customer.c_w_id = w;

            // discount is random between [0.0000 ... 0.5000]
            customer.c_discount = (float) (jTPCCUtil.randomNumber(1, 5000, gen) / 10000.0);

            if (jTPCCUtil.randomNumber(1, 100, gen) <= 10) {
              customer.c_credit = "BC"; // 10% Bad Credit
            } else {
              customer.c_credit = "GC"; // 90% Good Credit
            }
            customer.c_last = jTPCCUtil.getLastName(gen);
            customer.c_first = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(8, 16, gen));
            customer.c_credit_lim = 50000;

            customer.c_balance = -10;
            customer.c_ytd_payment = 10;
            customer.c_payment_cnt = 1;
            customer.c_delivery_cnt = 0;

            customer.c_street_1 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10, 20, gen));
            customer.c_street_2 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10, 20, gen));
            customer.c_city = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10, 20, gen));
            customer.c_state = jTPCCUtil.randomStr(3).toUpperCase();
            customer.c_zip = "123456789";

            customer.c_phone = "(732)744-1700";

            customer.c_since = sysdate.getTime();
            customer.c_middle = "OE";
            customer.c_data = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(300, 500, gen));

            history.h_c_id = c;
            history.h_c_d_id = d;
            history.h_c_w_id = w;
            history.h_d_id = d;
            history.h_w_id = w;
            history.h_date = sysdate.getTime();
            history.h_amount = 10;
            history.h_data = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10, 24, gen));

            k = k + 2;
            if (outputFiles == false) {
              custPrepStmt.setLong(1, customer.c_id);
              custPrepStmt.setLong(2, customer.c_d_id);
              custPrepStmt.setLong(3, customer.c_w_id);
              custPrepStmt.setDouble(4, customer.c_discount);
              custPrepStmt.setString(5, customer.c_credit);
              custPrepStmt.setString(6, customer.c_last);
              custPrepStmt.setString(7, customer.c_first);
              custPrepStmt.setDouble(8, customer.c_credit_lim);
              custPrepStmt.setDouble(9, customer.c_balance);
              custPrepStmt.setDouble(10, customer.c_ytd_payment);
              custPrepStmt.setDouble(11, customer.c_payment_cnt);
              custPrepStmt.setDouble(12, customer.c_delivery_cnt);
              custPrepStmt.setString(13, customer.c_street_1);
              custPrepStmt.setString(14, customer.c_street_2);
              custPrepStmt.setString(15, customer.c_city);
              custPrepStmt.setString(16, customer.c_state);
              custPrepStmt.setString(17, customer.c_zip);
              custPrepStmt.setString(18, customer.c_phone);

              Timestamp since = new Timestamp(customer.c_since);
              custPrepStmt.setTimestamp(19, since);
              custPrepStmt.setString(20, customer.c_middle);
              custPrepStmt.setString(21, customer.c_data);

              custPrepStmt.addBatch();

              histPrepStmt.setInt(1, history.h_c_id);
              histPrepStmt.setInt(2, history.h_c_d_id);
              histPrepStmt.setInt(3, history.h_c_w_id);

              histPrepStmt.setInt(4, history.h_d_id);
              histPrepStmt.setInt(5, history.h_w_id);
              Timestamp hdate = new Timestamp(history.h_date);
              histPrepStmt.setTimestamp(6, hdate);
              histPrepStmt.setDouble(7, history.h_amount);
              histPrepStmt.setString(8, history.h_data);

              histPrepStmt.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;

                custPrepStmt.executeBatch();
                histPrepStmt.executeBatch();
                custPrepStmt.clearBatch();
                custPrepStmt.clearBatch();
                transCommit();
              }
            } else {
              String str = "";
              str = str + customer.c_id + ",";
              str = str + customer.c_d_id + ",";
              str = str + customer.c_w_id + ",";
              str = str + customer.c_discount + ",";
              str = str + customer.c_credit + ",";
              str = str + customer.c_last + ",";
              str = str + customer.c_first + ",";
              str = str + customer.c_credit_lim + ",";
              str = str + customer.c_balance + ",";
              str = str + customer.c_ytd_payment + ",";
              str = str + customer.c_payment_cnt + ",";
              str = str + customer.c_delivery_cnt + ",";
              str = str + customer.c_street_1 + ",";
              str = str + customer.c_street_2 + ",";
              str = str + customer.c_city + ",";
              str = str + customer.c_state + ",";
              str = str + customer.c_zip + ",";
              str = str + customer.c_phone;
              out.println(str);

              str = "";
              str = str + history.h_c_id + ",";
              str = str + history.h_c_d_id + ",";
              str = str + history.h_c_w_id + ",";
              str = str + history.h_d_id + ",";
              str = str + history.h_w_id + ",";
              Timestamp hdate = new Timestamp(history.h_date);
              str = str + hdate + ",";
              str = str + history.h_amount + ",";
              str = str + history.h_data;
              outHist.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 [c]
        } // end for [d]
      } // end for [w]

      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;
      custPrepStmt.executeBatch();
      histPrepStmt.executeBatch();
      transCommit();
      now = new java.util.Date();
      if (outputFiles == true) {
        outHist.close();
      }
      System.out.println("End Cust-Hist Data Load @  " + now);

    } catch (SQLException se) {
      System.out.println(se.getMessage());
      transRollback();
      if (outputFiles == true) {
        outHist.close();
      }
    } catch (Exception e) {
      e.printStackTrace();
      transRollback();
      if (outputFiles == true) {
        outHist.close();
      }
    }

    return (k);
  } // end loadCust()
Beispiel #10
0
  static int loadStock(int whseKount, int itemKount) {

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

    try {

      now = new java.util.Date();
      t = (whseKount * itemKount);
      System.out.println("\nStart Stock Load for " + t + " units @ " + now + " ...");

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

      Stock stock = new Stock();

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

        for (int w = 1; w <= whseKount; w++) {

          stock.s_i_id = i;
          stock.s_w_id = w;
          stock.s_quantity = jTPCCUtil.randomNumber(10, 100, gen);
          stock.s_ytd = 0;
          stock.s_order_cnt = 0;
          stock.s_remote_cnt = 0;

          // s_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]
            stock.s_data = jTPCCUtil.randomStr(len);
          } else {
            // 10% of time i_data has "ORIGINAL" crammed somewhere in middle
            startORIGINAL = jTPCCUtil.randomNumber(2, (len - 8), gen);
            stock.s_data =
                jTPCCUtil.randomStr(startORIGINAL - 1)
                    + "ORIGINAL"
                    + jTPCCUtil.randomStr(len - startORIGINAL - 9);
          }

          stock.s_dist_01 = jTPCCUtil.randomStr(24);
          stock.s_dist_02 = jTPCCUtil.randomStr(24);
          stock.s_dist_03 = jTPCCUtil.randomStr(24);
          stock.s_dist_04 = jTPCCUtil.randomStr(24);
          stock.s_dist_05 = jTPCCUtil.randomStr(24);
          stock.s_dist_06 = jTPCCUtil.randomStr(24);
          stock.s_dist_07 = jTPCCUtil.randomStr(24);
          stock.s_dist_08 = jTPCCUtil.randomStr(24);
          stock.s_dist_09 = jTPCCUtil.randomStr(24);
          stock.s_dist_10 = jTPCCUtil.randomStr(24);

          k++;
          if (outputFiles == false) {
            stckPrepStmt.setLong(1, stock.s_i_id);
            stckPrepStmt.setLong(2, stock.s_w_id);
            stckPrepStmt.setDouble(3, stock.s_quantity);
            stckPrepStmt.setDouble(4, stock.s_ytd);
            stckPrepStmt.setLong(5, stock.s_order_cnt);
            stckPrepStmt.setLong(6, stock.s_remote_cnt);
            stckPrepStmt.setString(7, stock.s_data);
            stckPrepStmt.setString(8, stock.s_dist_01);
            stckPrepStmt.setString(9, stock.s_dist_02);
            stckPrepStmt.setString(10, stock.s_dist_03);
            stckPrepStmt.setString(11, stock.s_dist_04);
            stckPrepStmt.setString(12, stock.s_dist_05);
            stckPrepStmt.setString(13, stock.s_dist_06);
            stckPrepStmt.setString(14, stock.s_dist_07);
            stckPrepStmt.setString(15, stock.s_dist_08);
            stckPrepStmt.setString(16, stock.s_dist_09);
            stckPrepStmt.setString(17, stock.s_dist_10);
            stckPrepStmt.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;
              stckPrepStmt.executeBatch();
              stckPrepStmt.clearBatch();
              transCommit();
            }
          } else {
            String str = "";
            str = str + stock.s_i_id + ",";
            str = str + stock.s_w_id + ",";
            str = str + stock.s_quantity + ",";
            str = str + stock.s_ytd + ",";
            str = str + stock.s_order_cnt + ",";
            str = str + stock.s_remote_cnt + ",";
            str = str + stock.s_data + ",";
            str = str + stock.s_dist_01 + ",";
            str = str + stock.s_dist_02 + ",";
            str = str + stock.s_dist_03 + ",";
            str = str + stock.s_dist_04 + ",";
            str = str + stock.s_dist_05 + ",";
            str = str + stock.s_dist_06 + ",";
            str = str + stock.s_dist_07 + ",";
            str = str + stock.s_dist_08 + ",";
            str = str + stock.s_dist_09 + ",";
            str = str + stock.s_dist_10;
            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 [w]
      } // end for [i]

      long tmpTime = new java.util.Date().getTime();
      String etStr =
          "  Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + "                    ";
      System.out.println(etStr.substring(0, 30) + "  Writing final records " + k + " of " + t);
      lastTimeMS = tmpTime;
      if (outputFiles == false) {
        stckPrepStmt.executeBatch();
      }
      transCommit();

      now = new java.util.Date();
      System.out.println("End Stock Load @  " + now);

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

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

    return (k);
  } // end loadStock()
Beispiel #11
0
  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();
      String etStr =
          "  Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + "                    ";
      System.out.println(etStr.substring(0, 30) + "  Writing record " + k + " of " + t);
      lastTimeMS = tmpTime;

      if (outputFiles == false) {
        itemPrepStmt.executeBatch();
      }
      transCommit();
      now = new java.util.Date();
      System.out.println("End Item Load @  " + now);

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

    return (k);
  } // end loadItem()
  /**
   * Insert all service in services table
   *
   * @param noms
   * @throws SQLException
   */
  public static void insertStations(List<String[]> pdvs) throws SQLException {

    int idPdv;
    String nom = "";
    String adresse = "";
    String ville = "";
    String cp = "";
    String type_route;
    String[] ouvert = new String[3];
    String[] fermer = new String[3];
    Time hor_Ouv = null;
    hor_Ouv.valueOf("00:00:00");
    Time hor_Ferm = null;
    hor_Ferm.valueOf("00:00:00");
    double latitudePdv;
    double longitudePdv;

    String path = "src\\DAL\\StationsNOM.csv";
    List<String> nomStations = LectureFichier.readTXT(path);

    Random rand = new Random();
    int nbrand = 2;

    String requete =
        "INSERT INTO stations "
            + "(id_Station,Nom,Adresse,Ville,CP,Type_route,Horaire_ouverture,Horaire_fermeture,latitude,longitude) "
            + "VALUES(?,?,?,?,?,?,?,?,?,?)";
    // int[] count= new int[0];

    Connection connection = ConnexionManager.GetInstance().GetConnection();
    for (String[] pdv : pdvs) {

      if (!pdv[0].equals("id")) {
        idPdv = Integer.parseInt(pdv[0]);
        // integration des noms g�n�r� al�atoirement
        nbrand = rand.nextInt(nomStations.size() - 2) + 1;
        nom = nomStations.get(nbrand);
        adresse = pdv[1];
        ville = pdv[2];
        cp = pdv[3];
        type_route = pdv[4];
        ouvert = pdv[5].split(":");
        fermer = pdv[6].split(":");
        // fermer = pdv[6].replace(':', '-');
        hor_Ouv =
            new Time(
                Integer.parseInt(ouvert[0]),
                Integer.parseInt(ouvert[1]),
                Integer.parseInt(ouvert[2]));
        hor_Ferm =
            new Time(
                Integer.parseInt(fermer[0]),
                Integer.parseInt(fermer[1]),
                Integer.parseInt(fermer[2]));
        latitudePdv = Double.parseDouble(pdv[7]);
        longitudePdv = Double.parseDouble(pdv[8]);

        try {
          PreparedStatement requeteSql = connection.prepareStatement(requete);
          requeteSql.setInt(1, idPdv);
          requeteSql.setString(2, nom);
          requeteSql.setString(3, adresse);
          requeteSql.setString(4, ville);
          requeteSql.setString(5, cp);
          requeteSql.setString(6, type_route);
          requeteSql.setTime(7, hor_Ouv);
          requeteSql.setTime(8, hor_Ferm);
          requeteSql.setDouble(9, latitudePdv);
          requeteSql.setDouble(10, longitudePdv);
          requeteSql.addBatch();
          int[] count = requeteSql.executeBatch();
          // requeteSql.executeBatch();
          // System.out.println(count.length);
        } catch (SQLException sqle) {
          // TODO Auto-generated catch block

          ConnexionManager.GetInstance().GetConnection().rollback();

          sqle.printStackTrace();
        }
      }
    }
  }