Esempio n. 1
0
 private static void storeNodeCentrality(
     Connection connection, TableLocation nodesName, KeyedGraph graph) throws SQLException {
   final PreparedStatement nodeSt =
       connection.prepareStatement("INSERT INTO " + nodesName + " VALUES(?,?,?)");
   try {
     int count = 0;
     for (VCent v : (Set<VCent>) graph.vertexSet()) {
       nodeSt.setInt(1, v.getID());
       nodeSt.setDouble(2, v.getBetweenness());
       nodeSt.setDouble(3, v.getCloseness());
       nodeSt.addBatch();
       count++;
       if (count >= BATCH_SIZE) {
         nodeSt.executeBatch();
         nodeSt.clearBatch();
         count = 0;
       }
     }
     if (count > 0) {
       nodeSt.executeBatch();
       nodeSt.clearBatch();
     }
     connection.commit();
   } finally {
     nodeSt.close();
   }
 }
Esempio n. 2
0
 private static void storeEdgeCentrality(
     Connection connection, TableLocation edgesName, KeyedGraph graph) throws SQLException {
   final PreparedStatement edgeSt =
       connection.prepareStatement("INSERT INTO " + edgesName + " VALUES(?,?)");
   try {
     int count = 0;
     for (EdgeCent e : (Set<EdgeCent>) graph.edgeSet()) {
       edgeSt.setInt(1, e.getID());
       edgeSt.setDouble(2, e.getBetweenness());
       edgeSt.addBatch();
       count++;
       if (count >= BATCH_SIZE) {
         edgeSt.executeBatch();
         edgeSt.clearBatch();
         count = 0;
       }
     }
     if (count > 0) {
       edgeSt.executeBatch();
       edgeSt.clearBatch();
     }
     connection.commit();
   } finally {
     edgeSt.close();
   }
 }
  /**
   * Convenience method to create a new PreparedStatement for a query.
   *
   * @param conn The Connection to use for the statement
   * @param stmtText Statement text
   * @param resultSetType Type of result set
   * @param resultSetConcurrency Concurrency for the result set
   * @return The PreparedStatement
   * @throws SQLException thrown if an error occurs creating the statement
   */
  public PreparedStatement getStatementForQuery(
      ManagedConnection conn, String stmtText, String resultSetType, String resultSetConcurrency)
      throws SQLException {
    Connection c = (Connection) conn.getConnection();
    if (supportsBatching) {
      // Check for a waiting batched statement that is ready for processing
      ConnectionStatementState state = getConnectionStatementState(conn);
      if (state != null && state.processable) {
        // Process the batch statement before returning our new query statement
        processConnectionStatement(conn);
      }
    }

    // Create a new PreparedStatement for this query
    PreparedStatement ps = null;
    if (resultSetType != null || resultSetConcurrency != null) {
      int rsTypeValue = ResultSet.TYPE_FORWARD_ONLY;
      if (resultSetType != null) {
        if (resultSetType.equals("scroll-sensitive")) {
          rsTypeValue = ResultSet.TYPE_SCROLL_SENSITIVE;
        } else if (resultSetType.equals("scroll-insensitive")) {
          rsTypeValue = ResultSet.TYPE_SCROLL_INSENSITIVE;
        }
      }

      int rsConcurrencyValue = ResultSet.CONCUR_READ_ONLY;
      if (resultSetConcurrency != null && resultSetConcurrency.equals("updateable")) {
        rsConcurrencyValue = ResultSet.CONCUR_UPDATABLE;
      }
      ps = c.prepareStatement(stmtText, rsTypeValue, rsConcurrencyValue);
      ps
          .clearBatch(); // In case using statement caching and given one with batched statements
                         // left hanging (C3P0)
    } else {
      ps = c.prepareStatement(stmtText);
      ps
          .clearBatch(); // In case using statement caching and given one with batched statements
                         // left hanging (C3P0)
    }

    if (queryTimeout > 0) {
      // Apply any query timeout
      ps.setQueryTimeout(queryTimeout / 1000); // queryTimeout is in milliseconds
    }
    if (NucleusLogger.DATASTORE.isDebugEnabled()) {
      NucleusLogger.DATASTORE.debug(LOCALISER.msg("052110", StringUtils.toJVMIDString(ps)));
    }

    if (!jdbcStatements) {
      // Wrap with our parameter logger
      ps = new ParamLoggingPreparedStatement(ps, stmtText);
      ((ParamLoggingPreparedStatement) ps).setParamsInAngleBrackets(paramValuesInBrackets);
    }

    return ps;
  }
  protected void createTiles() throws InterruptedException, MapCreationException {
    @SuppressWarnings("unused") // W #unused
    int maxMapProgress =
        2 * (mMap.getXMax() - mMap.getXMin() + 1) * (mMap.getYMax() - mMap.getYMin() + 1);
    // bundleProgress.initMapCreation(maxMapProgress);
    TileImageParameters param = mMap.getParameters();
    // if (param != null)
    // mapDlTileProvider = new ConvertedRawTileProvider(mapDlTileProvider, param.getFormat());
    try {
      conn.setAutoCommit(false);
      int batchTileCount = 0;
      int tilesWritten = 0;
      Runtime r = Runtime.getRuntime();
      long heapMaxSize = r.maxMemory();
      prepStmt = conn.prepareStatement(getTileInsertSQL());
      for (int x = mMap.getXMin(); x <= mMap.getXMax(); x++) {
        for (int y = mMap.getYMin(); y <= mMap.getYMax(); y++) {
          // checkUserAbort();
          // bundleProgress.incMapCreationProgress();
          try {
            // byte[] sourceTileData = mapDlTileProvider.getTileData(x, y);
            byte[] sourceTileData = null;
            if (sourceTileData != null) {
              writeTile(x, y, mMap.getZoom(), sourceTileData);
              tilesWritten++;
              long heapAvailable = heapMaxSize - r.totalMemory() + r.freeMemory();

              batchTileCount++;
              if ((heapAvailable < HEAP_MIN) || (batchTileCount >= MAX_BATCH_SIZE)) {
                log.trace("Executing batch containing " + batchTileCount + " tiles");
                prepStmt.executeBatch();
                prepStmt.clearBatch();
                System.gc();
                conn.commit();
                // bundleProgress.incMapCreationProgress(batchTileCount);
                batchTileCount = 0;
              }
            }
          } catch (IOException e) {
            throw new MapCreationException(mMap, e);
          }
        }
      }
      prepStmt.executeBatch();
      prepStmt.clearBatch();
      System.gc();
      if (tilesWritten > 0) updateTileMetaInfo();
      log.trace("Final commit containing " + batchTileCount + " tiles");
      conn.commit();
      // bundleProgress.setMapCreationProgress(maxMapProgress);
    } catch (SQLException e) {
      throw new MapCreationException(mMap, e);
    }
  }
  @Test
  public void testPreparedStatementWithBatch() throws Exception {
    Connection connection = proxyDataSource.getConnection();
    PreparedStatement statement =
        connection.prepareStatement("update emp set name = ? where id = ?");
    statement.setString(1, "BAZ");
    statement.setInt(2, 3);
    statement.addBatch();

    statement.clearBatch();

    statement.setString(1, "FOO");
    statement.setInt(2, 1);
    statement.addBatch();

    statement.setString(1, "BAR");
    statement.setInt(2, 2);
    statement.addBatch();

    statement.executeBatch();

    final InMemoryLog log = getInMemoryLog();
    verifyMessage(CommonsLogLevel.DEBUG, log, "update emp set name = ? where id = ?");
    verifyMessage(CommonsLogLevel.DEBUG, log, "[(FOO,1),(BAR,2)]");
  }
 public static void insertBatch() {
   String sql =
       "INSERT into order_line (ol_id, ol_o_id, ol_i_id, ol_qty, ol_discount, ol_comments) VALUES (?, ?, ?, ?, ?, ?)";
   Connection conn = null;
   if (flag) {
     conn = IMDGHelper.getConnection();
   } else {
     conn = DBHelper.getConnection();
   }
   try {
     PreparedStatement pst = conn.prepareStatement(sql);
     Random random = new Random(System.currentTimeMillis());
     int count = 1000;
     int range = 10000;
     long start = System.currentTimeMillis();
     for (int i = 0; i < count; i++) {
       pst.setInt(1, random.nextInt(range) + 2000);
       pst.setInt(2, random.nextInt(range) + 2000);
       pst.setInt(3, random.nextInt(range) + 2000);
       pst.setInt(4, random.nextInt(range) + 2000);
       pst.setDouble(5, (double) getRandomInt(0, 30) / 100);
       pst.setString(6, getRandomAString(20, 100));
       pst.addBatch();
     }
     pst.executeBatch();
     pst.clearBatch();
     long end = System.currentTimeMillis();
     System.out.println("INSERT time : " + (end - start));
     pst.close();
     conn.close();
   } catch (SQLException e) {
     e.printStackTrace();
   }
 }
Esempio n. 7
0
  public void clearBatch() throws SQLException {
    Profiler profiler = _profilerPoint.start();

    try {
      _preparedStatement.clearBatch();
    } finally {
      profiler.finish();
    }
  }
Esempio n. 8
0
 /** Flushes the batch */
 public void flush() throws SQLException {
   try {
     preparedStatement.executeBatch();
     preparedStatement.clearBatch();
   } catch (SQLException e) {
     String details = e.getNextException() == null ? "" : e.getNextException().getMessage();
     throw new SQLException(e.getMessage() + "\n\n" + details);
   }
 }
Esempio n. 9
0
 @Override
 public void run() {
   Connection mConn = DBService.sharedInstance().getConnection();
   try {
     while (true) {
       for (int i = 0; i < 80; i++) {
         DBDData data = mDataQueue.poll();
         if (data == null) {
           if (mDataQueue.size() == 0) {
             data = mDataQueue.take();
           } else {
             break;
           }
         }
         if (data != null) {
           mCacheData.add(data);
         }
       }
       if (mCacheData.size() == 0) {
         continue;
       }
       if (isInterrupt) {
         break;
       }
       PreparedStatement ps = null;
       try {
         ps = mConn.prepareStatement(INSERT_SQL);
         System.out.println("write_size=" + mCacheData.size());
         for (DBDData data : mCacheData) {
           data.insertIntoDB(ps);
         }
         int[] result = ps.executeBatch();
         System.out.println(result);
       } catch (Exception e) {
         e.printStackTrace();
       } finally {
         try {
           if (ps != null) {
             ps.clearParameters();
             ps.clearBatch();
             ps.close();
           }
         } catch (SQLException e) {
           e.printStackTrace();
         }
       }
       mCacheData.clear();
     }
   } catch (InterruptedException e) {
     e.printStackTrace();
   } finally {
     CloseUtil.close(mConn);
   }
 }
 public static Integer batchInsertData(
     List<Object[]> data, Connection countCon, PreparedStatement ps, Set<String> yearMonthCache)
     throws SQLException {
   if (CollectionUtils.isEmpty(data)) {
     return 0;
   }
   Integer curMaxId = 0;
   for (int i = 0; i < data.size(); i++) {
     // 插入数据
     // 数据库表的设定也不允许任何列为空
     if (data.get(i).length < 4
         || data.get(i)[1] == null
         || data.get(i)[2] == null
         || data.get(i)[3] == null
         || data.get(i)[0] == null) {
       logger.error("搜索关键字备份 - 数据插入:存在空值!");
       continue;
     }
     Integer curId = ((BigInteger) data.get(i)[0]).intValue();
     Date curDate = (Date) data.get(i)[3];
     String curYearMonth = new DateTime(curDate).toString("yyyyMM");
     if (!yearMonthCache.contains(curYearMonth)) {
       yearMonthCache.add(curYearMonth);
       // 如果当前id大于等于5月最大id,结束
       for (DateTime temp = DateTime.parse("201401", DateTimeFormat.forPattern("yyyyMM"));
           temp.toString("yyyyMM").compareTo(curYearMonth) < 0;
           temp = temp.plusMonths(1)) { // 如果出现比现在的月份还小的分区不存在的情况,也创建
         String tempCurString = temp.toString("yyyyMM");
         if (!yearMonthCache.contains(tempCurString)) {
           yearMonthCache.add(tempCurString);
           createMonthPartitionIfNotExist(tempCurString, countCon);
         }
       }
       createMonthPartitionIfNotExist(curYearMonth, countCon);
     }
     ps.setObject(1, curId);
     ps.setObject(2, data.get(i)[1]);
     ps.setObject(3, data.get(i)[2]);
     ps.setObject(4, curDate);
     ps.addBatch();
     if (curId > curMaxId) {
       curMaxId = curId;
     }
     if ((i + 1) == data.size()) {
       long b = new Date().getTime();
       ps.executeBatch();
       ps.clearBatch();
       logger.info("插入【" + data.size() + "】条数据,耗时【" + (new Date().getTime() - b) + "】毫秒");
     }
   }
   return curMaxId;
 }
Esempio n. 11
0
 private void testClearBatch01() throws SQLException {
   trace("testClearBatch01");
   String sPrepStmt = COFFEE_UPDATE;
   trace("Prepared Statement String:" + sPrepStmt);
   prep = conn.prepareStatement(sPrepStmt);
   prep.setInt(1, 2);
   prep.addBatch();
   prep.setInt(1, 3);
   prep.addBatch();
   prep.setInt(1, 4);
   prep.addBatch();
   prep.clearBatch();
   assertEquals(0, prep.executeBatch().length);
 }
  /**
   * 说明:更新给定的部门对象
   *
   * @param con 数据库连接对象
   * @param source 部门对象
   * @throws LawyerException
   */
  public void updateDepartment(Connection con, Department source) throws LawyerException {
    String sql =
        "UPDATE "
            + DEPTABLE
            + " SET NAME=?,STATUS=?,SDATE=?,EDATE=?,REMARKS=?,PRINCIPAl=? WHERE ID='"
            + source.getId()
            + "' AND SYSID='"
            + source.getSysID()
            + "'";
    PreparedStatement psment = null;
    try {
      psment = con.prepareStatement(sql);
      // 清除参数
      psment.clearBatch();

      psment.setString(1, source.getName());
      psment.setString(2, source.getStatus());
      psment.setDate(
          3,
          null == source.getSdate()
              ? new java.sql.Date(new Date().getTime())
              : new java.sql.Date(source.getSdate().getTime()));
      psment.setDate(
          4,
          null == source.getEdate()
              ? new java.sql.Date(new Date().getTime())
              : new java.sql.Date(source.getEdate().getTime()));
      psment.setString(5, source.getRemarks());
      psment.setString(6, source.getPrincipal());

      psment.addBatch();

      // 执行数据库插入处理
      psment.execute();
    } catch (SQLException e) {
      throw new LawyerException("Update Department is error! " + e.getMessage());
    } finally {
      try {
        psment.close();
      } catch (SQLException e) {
      }
      psment = null;
    }
  }
Esempio n. 13
0
  /**
   * Method to execute a PreparedStatement query, and return the ResultSet. Prints logging
   * information about timings.
   *
   * @param conn The connection (required since the one on PreparedStatement is not always the same
   *     so we can't use it)
   * @param stmt The statement text
   * @param ps The Prepared Statement
   * @return The ResultSet from the query
   * @throws SQLException Thrown if an error occurs
   */
  public ResultSet executeStatementQuery(
      ExecutionContext ec, ManagedConnection conn, String stmt, PreparedStatement ps)
      throws SQLException {
    if (supportsBatching) {
      ConnectionStatementState state = getConnectionStatementState(conn);
      if (state != null) {
        if (state.processable) {
          // Current batch statement is processable now so lets just process it before processing
          // our query
          processConnectionStatement(conn);
        } else {
          // Current wait statement is not processable now so leave it in wait state
          if (NucleusLogger.DATASTORE_RETRIEVE.isDebugEnabled()) {
            NucleusLogger.DATASTORE_RETRIEVE.debug(LOCALISER.msg("052106", state.stmtText, stmt));
          }
        }
      }
    }

    // Execute this query
    long startTime = System.currentTimeMillis();
    if (NucleusLogger.DATASTORE_NATIVE.isDebugEnabled()) {
      if (ps instanceof ParamLoggingPreparedStatement) {
        NucleusLogger.DATASTORE_NATIVE.debug(
            ((ParamLoggingPreparedStatement) ps).getStatementWithParamsReplaced());
      } else {
        NucleusLogger.DATASTORE_NATIVE.debug(stmt);
      }
    }

    ResultSet rs = ps.executeQuery();
    if (ec != null && ec.getStatistics() != null) {
      // Add to statistics
      ec.getStatistics().incrementNumReads();
    }

    ps.clearBatch();
    if (NucleusLogger.DATASTORE_RETRIEVE.isDebugEnabled()) {
      NucleusLogger.DATASTORE_RETRIEVE.debug(
          LOCALISER.msg("045000", (System.currentTimeMillis() - startTime)));
    }

    return rs;
  }
  /**
   * 说明:存储给定的部门对象
   *
   * @param con 数据库连接对象
   * @param source 部门对象
   * @throws LawyerException
   */
  public void saveDepartment(Connection con, Department source) throws LawyerException {
    String sql =
        "INSERT INTO "
            + DEPTABLE
            + "(ID,NAME,STATUS,SDATE,EDATE,REMARKS,PRINCIPAl,SYSID) VALUES(?,?,?,?,?,?,?,?)";
    PreparedStatement psment = null;
    try {
      psment = con.prepareStatement(sql);
      // 清除参数
      psment.clearBatch();

      psment.setString(1, source.getId());
      psment.setString(2, source.getName());
      psment.setString(3, source.getStatus());
      psment.setDate(
          4,
          null == source.getSdate()
              ? new java.sql.Date(new Date().getTime())
              : new java.sql.Date(source.getSdate().getTime()));
      psment.setDate(
          5,
          null == source.getEdate()
              ? new java.sql.Date(new Date().getTime())
              : new java.sql.Date(source.getEdate().getTime()));
      psment.setString(6, source.getRemarks());
      psment.setString(7, source.getPrincipal());
      psment.setString(8, source.getSysID());

      psment.addBatch();

      // 执行数据库插入处理
      psment.execute();
    } catch (SQLException e) {
      throw new LawyerException("Save Department is error! " + e.getMessage());
    } finally {
      try {
        psment.close();
      } catch (SQLException e) {
      }
      psment = null;
    }
  }
Esempio n. 15
0
 @Override
 public void traceMarker() throws Exception {
   PreparedStatement preparedStatement =
       connection.prepareStatement("insert into employee (name) values (?)");
   try {
     preparedStatement.setString(1, "huckle");
     preparedStatement.addBatch();
     preparedStatement.setString(1, "sally");
     preparedStatement.addBatch();
     preparedStatement.executeBatch();
     preparedStatement.clearBatch();
     preparedStatement.setString(1, "lowly");
     preparedStatement.addBatch();
     preparedStatement.setString(1, "pig will");
     preparedStatement.addBatch();
     preparedStatement.executeBatch();
   } finally {
     preparedStatement.close();
   }
 }
Esempio n. 16
0
  /**
   * Method to execute a PreparedStatement (using PreparedStatement.execute()). Prints logging
   * information about timings.
   *
   * @param ec Execution Context
   * @param conn The connection (required since the one on PreparedStatement is not always the same
   *     so we can't use it)
   * @param stmt The statement text
   * @param ps The Prepared Statement
   * @return The numer of rows affected (as per PreparedStatement.execute)
   * @throws SQLException Thrown if an error occurs
   */
  public boolean executeStatement(
      ExecutionContext ec, ManagedConnection conn, String stmt, PreparedStatement ps)
      throws SQLException {
    if (supportsBatching) {
      // Check for a waiting batched statement that is ready for processing
      ConnectionStatementState state = getConnectionStatementState(conn);
      if (state != null && state.processable) {
        // Process the batch statement before returning our new query statement
        processConnectionStatement(conn);
      }
    }

    // Process the normal execute statement
    long startTime = System.currentTimeMillis();
    if (NucleusLogger.DATASTORE_NATIVE.isDebugEnabled()) {
      if (ps instanceof ParamLoggingPreparedStatement) {
        NucleusLogger.DATASTORE_NATIVE.debug(
            ((ParamLoggingPreparedStatement) ps).getStatementWithParamsReplaced());
      } else {
        NucleusLogger.DATASTORE_NATIVE.debug(stmt);
      }
    }

    boolean flag = ps.execute();
    if (ec != null && ec.getStatistics() != null) {
      // Add to statistics
      ec.getStatistics().incrementNumWrites();
    }

    ps.clearBatch();
    if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) {
      NucleusLogger.DATASTORE_PERSIST.debug(
          LOCALISER.msg(
              "045002",
              "" + (System.currentTimeMillis() - startTime),
              StringUtils.toJVMIDString(ps)));
    }

    return flag;
  }
Esempio n. 17
0
 public static final void persistToDatabase(List<ChartPvUvData> datas)
     throws SQLException, ClassNotFoundException {
   if (datas == null || datas.isEmpty()) {
     return;
   }
   Connection conn = null;
   PreparedStatement pstmt = null;
   try {
     conn = getDatabaseConnection();
     String sql =
         "insert into page_view(module, code, country, `from`, `schedule`,pv, uv, gmt_create) values(?,?,?,?,?,?,?,now()) on duplicate key update pv =? ,uv =?";
     pstmt = conn.prepareStatement(sql);
     int count = 0;
     for (ChartPvUvData data : datas) {
       ChartPvUvData.KeyEntry keyEntry = data.getKeyEntry();
       pstmt.setString(1, data.getModule());
       pstmt.setString(2, keyEntry.linkNode);
       pstmt.setString(3, keyEntry.country);
       pstmt.setString(4, keyEntry.from);
       pstmt.setString(5, data.getDate());
       pstmt.setInt(6, data.getPv());
       pstmt.setInt(7, data.getUv());
       pstmt.setInt(8, data.getPv());
       pstmt.setInt(9, data.getUv());
       pstmt.addBatch();
       count++;
       if (count >= BATCH_SIZE) {
         pstmt.executeBatch();
         pstmt.clearBatch();
         count = 0;
       }
     }
     if (count > 0) {
       pstmt.executeBatch();
     }
   } finally {
     closeDatabaseComponent(null, pstmt, conn);
   }
 }
  @Override
  public void save(List<Food> foods) {

    String sql = "insert INTO food VALUES(null, ?, ?, ?, ?);";

    try (Connection conn = dataSource.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
      conn.setAutoCommit(false);
      for (Food actualFood : foods) {
        ps.setInt(1, actualFood.getCalories());
        ps.setBoolean(2, actualFood.isVegan());
        ps.setString(3, actualFood.getName());
        ps.setInt(4, actualFood.getPrice());
        ps.addBatch();
      }

      ps.executeBatch();
      ps.clearBatch();
      conn.commit();

    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
Esempio n. 19
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()
Esempio n. 20
0
  /**
   * insert the given file into the database
   *
   * @param f
   * @param insertDCMSeriesStmt
   * @param insertDCMObjectStmt
   * @param insertDCMTagsStmt
   * @param currentDbConnection
   */
  private void insertFile2DB(
      File f,
      PreparedStatement insertDCMSeriesStmt,
      PreparedStatement insertDCMObjectStmt,
      PreparedStatement insertDCMTagsStmt,
      Connection currentDbConnection) {

    logger.info(
        " \r\n\r\n"
            + "#######################################################\r\n"
            + "#\r\n"
            + "#  STARTING IMPORT FOR "
            + f.getName()
            + "\r\n"
            + "#\r\n"
            + "#######################################################");

    used_files++;

    long start = System.currentTimeMillis();

    boolean importFile = true;

    try {
      DicomHeader dh = new DicomHeader(f);
      DicomObject dcmObjectHeader = dh.getDicomObject();

      // if a rule to filter exist -> no import
      if (!filterTags.isEmpty()) {
        for (Iterator<String> iter = filterTags.keySet().iterator(); iter.hasNext(); ) {
          String tag = (String) iter.next();

          // check comma separated filter e. g. 0008,0060=MR, UR, NM, US
          if (filterTags.get(tag).contains(",")) {
            String[] splittedValue = filterTags.get(tag).split(", ");
            for (int i = 0; i < splittedValue.length; i++) {
              if (splittedValue[i].equals(dh.getHeaderStringValue(tag))) {
                importFile = false;
                logger.info("NOT importing File, matches Filtertags: " + filterTags.get(tag));
                break;
              }
            }
          } else if (dh.getHeaderStringValue(tag).equals(filterTags.get(tag))) {
            importFile = false;
            logger.info("NOT importing File, matches Filtertags: " + filterTags.get(tag));
            break;
          }
        }
      }

      // filter the reconstruction-images
      if (importFile && CHECK_RECONSTRUCTION) {
        importFile = checkRecontruction(currentDbConnection, dh);
      }

      if (importFile) {

        // insert dcmSeries if it not exists
        if (!seriesExists(currentDbConnection, dh)) {
          try {
            int i = 1; // index for database-fields in series
            for (Iterator<String> iter = dicomTags.keySet().iterator(); iter.hasNext(); i++) {
              String tag = (String) iter.next();
              String VR = DicomHeader.getHeaderFieldType(tag);

              // if Date
              if ("DA".equals(VR)) {
                try {
                  Date d = dh.getHeaderDateValue(tag);
                  insertDCMSeriesStmt.setDate(i, new java.sql.Date(d.getTime()));
                } catch (Exception e) {
                  insertDCMSeriesStmt.setDate(i, null);
                }
                // if Time
              } else if ("TM".equals(VR)) {
                try {
                  Date d = dh.getHeaderDateValue(tag);
                  insertDCMSeriesStmt.setTime(i, new java.sql.Time(d.getTime()));
                } catch (Exception e) {
                  insertDCMSeriesStmt.setTime(i, null);
                }
                // else String
              } else {
                String s = dh.getHeaderStringValue(tag);
                s = s.replaceAll("\0", "");
                insertDCMSeriesStmt.setString(i, s);
              }
            }
            insertDCMSeriesStmt.execute();

            // Exception if Seriesuid exist
          } catch (SQLException sqle) {
            if (sqle.getMessage().indexOf("duplicate key") < 0) {
              logger.error("Error adding series: ", sqle);
            } else {
              logger.warn("Series allready in DB");
            }
            currentDbConnection.rollback();
          }
        }

        // insert the dicom-object and the tags
        try {
          int j = 1; // index for database-fields in dicom-objects
          insertDCMObjectStmt.setString(j++, dh.getDicomObject().getString(Tag.SeriesInstanceUID));
          insertDCMObjectStmt.setString(j++, dh.getDicomObject().getString(Tag.SOPInstanceUID));
          insertDCMObjectStmt.setTimestamp(j++, new Timestamp(System.currentTimeMillis()));

          // storing the md5 of pixel-data in database, if CHECKSUM is true
          if (CHECKSUM) {
            DicomInputStream dis = new DicomInputStream(f);
            DicomObject dcmObj = new BasicDicomObject();
            dis.readDicomObject(dcmObj, -1);
            DicomElement de = dcmObj.get(Tag.PixelData);
            byte[] bytes = de.getBytes();

            MessageDigest md = MessageDigest.getInstance("MD5");
            md.update(bytes);
            byte[] md5 = md.digest();
            BigInteger bi = new BigInteger(1, md5);
            insertDCMObjectStmt.setString(j++, bi.toString(16));
          } else {
            insertDCMObjectStmt.setString(j++, null);
          }

          insertDCMObjectStmt.setString(j++, SOFTWARE_VERSION);
          insertDCMObjectStmt.execute();

          //                            currentDbConnection.commit();

          int parentID =
              getDCMObjectID(
                  currentDbConnection, dh.getDicomObject().getString(Tag.SOPInstanceUID));

          itemOrder = 1;
          // recursive insert of dicom-tags
          insertDCMTagsStmt.clearBatch();
          insertDicomTags(parentID, dcmObjectHeader, null, insertDCMTagsStmt);
          insertDCMTagsStmt.executeBatch();

          currentDbConnection.commit();

          logger.info("File inserted");
          imported_files++;
        } catch (SQLException sqle) {
          currentDbConnection.rollback();
          if (sqle.getMessage().indexOf("duplicate key") < 0) {
            logger.error("Error importing image: ", sqle);
          } else {
            logger.warn(
                "File with UID="
                    + dh.getDicomObject().getString(Tag.SOPInstanceUID)
                    + " allready in db");
          }
        }
      }

      if (DELETE_FILES_AFTER_IMPORT) {
        f.delete();
      } else {
        boolean success = f.renameTo(new File(OK_DIR, f.getName()));
        if (success) {
          logger.info("moved file to: " + OK_DIR + File.separator + f.getName());
        } else {
          logger.warn("unable to move file to: " + OK_DIR + File.separator + f.getName());
        }
      }

    } catch (Exception e) {
      boolean success = f.renameTo(new File(ERROR_DIR, f.getName()));
      logger.log(
          Level.WARN, "Failed to insert file: " + f.getAbsolutePath() + " " + e.getMessage(), e);
      if (success) {
        logger.info("moved file to: " + ERROR_DIR + File.separator + f.getName());
      } else {
        logger.warn("unable to move file to: " + ERROR_DIR + File.separator + f.getName());
      }
      try {
        currentDbConnection.rollback();
      } catch (SQLException e1) {
        logger.log(Level.ERROR, e1.getMessage(), e1);
      }
    }

    logger.info(
        "END OF IMPORT FOR "
            + f.getName()
            + " took "
            + (System.currentTimeMillis() - start)
            + "µsec. \r\n"
            + "Files used: "
            + used_files
            + "  Files imported: "
            + imported_files
            + " \r\n"
            + "#######################################################");
  }
Esempio n. 21
0
 public boolean batchProductQR(int sum) {
   if (sum <= 0 || sum > 1000) {
     return false;
   }
   String imgedir = null;
   String os = System.getProperty("os.name");
   if (os.indexOf("Window") != -1) {
     imgedir = WINDOWS_DIR;
   } else {
     imgedir = LINUX_DIR;
   }
   boolean flag = false;
   DBService db = null;
   PreparedStatement st = null;
   int error = 0;
   try {
     DES des = new DES();
     des.setKey("wh#@!a59");
     db = new DBService();
     int n = db.getInt("SELECT MAX(url_id) FROM wht_app_url");
     st =
         db.getConn()
             .prepareStatement(
                 "insert into wht_app_url(url_id,url,imgedir,imgename,imgedirname) values(?,?,?,?,?)");
     db.getConn().setAutoCommit(false);
     for (int i = 1; i <= sum; i++) {
       n = n + i;
       if (QRCodeUtil.encode(
           URLPATH + n, imgedir + "logo.png", imgedir + "QRcard" + n + ".png", true)) {
         st.setInt(1, n);
         st.setString(2, URLPATH + des.decrypt(n + ""));
         st.setString(3, imgedir);
         st.setString(4, "QRcard" + n + ".png");
         st.setString(5, imgedir + "QRcard" + n + ".png");
         st.addBatch();
         if ((n + 1) % 50 == 0) {
           st.executeBatch();
           st.clearBatch();
         }
       } else {
         error++;
       }
     }
     st.executeBatch();
     db.getConn().commit();
     flag = true;
     Log.info("生成智能名片二维码失败张数:" + error);
   } catch (Exception e) {
     db.rollback();
     Log.error("生成智能名片二维码出错:" + e.toString());
   } finally {
     if (null != st) {
       //				st.close();
       st = null;
     }
     if (null != db) {
       db.close();
       db = null;
     }
   }
   return flag;
 }
Esempio n. 22
0
 public void clearBatch() throws SQLException {
   statement.clearBatch();
 }
  protected Map<UUID, Integer> getPlayerIds(Set<OfflinePlayer> players) throws SQLException {

    setupStatement_1(mConnection);
    myAddPlayerStatement.clearBatch();

    for (OfflinePlayer player : players) {
      myAddPlayerStatement.setString(1, player.getPlayer().getUniqueId().toString());
      myAddPlayerStatement.setString(2, player.getPlayer().getName());
      myAddPlayerStatement.addBatch();
    }
    myAddPlayerStatement.executeBatch();
    myAddPlayerStatement.close();

    int left = players.size();
    Iterator<OfflinePlayer> it = players.iterator();
    HashMap<UUID, Integer> ids = new HashMap<UUID, Integer>();

    while (left > 0) {
      PreparedStatement statement;
      int size = 0;
      if (left >= 10) {
        size = 10;
        statement = mGetPlayerStatement[3];
      } else if (left >= 5) {
        size = 5;
        statement = mGetPlayerStatement[2];
      } else if (left >= 2) {
        size = 2;
        statement = mGetPlayerStatement[1];
      } else {
        size = 1;
        statement = mGetPlayerStatement[0];
      }

      left -= size;

      ArrayList<OfflinePlayer> temp = new ArrayList<OfflinePlayer>(size);
      for (int i = 0; i < size; ++i) {
        OfflinePlayer player = it.next();
        temp.add(player);
        statement.setString(i + 1, player.getUniqueId().toString());
      }

      ResultSet results = statement.executeQuery();

      int index = 0;
      while (results.next()) {
        OfflinePlayer player = temp.get(index++);
        if (results.getString(1).equals(player.getUniqueId().toString())
            && !results.getString(2).equals(player.getPlayer().getName())) {
          MobHunting.instance
              .getLogger()
              .severe(
                  "Name change detected(1): "
                      + results.getString(2)
                      + " -> "
                      + player.getPlayer().getName()
                      + " UUID="
                      + player.getUniqueId().toString());
          updatePlayerName(player.getPlayer());
        }

        ids.put(UUID.fromString(results.getString(1)), results.getInt(3));
      }
    }

    return ids;
  }
Esempio n. 24
0
  static int loadOrder(int whseKount, int distWhseKount, int custDistKount) {

    int k = 0;
    int t = 0;
    PrintWriter outLine = null;
    PrintWriter outNewOrder = null;

    try {

      if (outputFiles == true) {
        out = new PrintWriter(new FileOutputStream(fileLocation + "order.csv"));
        System.out.println("\nWriting Order file to: " + fileLocation + "order.csv");
        outLine = new PrintWriter(new FileOutputStream(fileLocation + "order-line.csv"));
        System.out.println("\nWriting Order Line file to: " + fileLocation + "order-line.csv");
        outNewOrder = new PrintWriter(new FileOutputStream(fileLocation + "new-order.csv"));
        System.out.println("\nWriting New Order file to: " + fileLocation + "new-order.csv");
      }

      now = new java.util.Date();
      Oorder oorder = new Oorder();
      NewOrder new_order = new NewOrder();
      OrderLine order_line = new OrderLine();
      jdbcIO myJdbcIO = new jdbcIO();

      t = (whseKount * distWhseKount * custDistKount);
      t = (t * 11) + (t / 3);
      System.out.println(
          "whse=" + whseKount + ", dist=" + distWhseKount + ", cust=" + custDistKount);
      System.out.println("\nStart Order-Line-New Load for approx " + t + " rows @ " + now + " ...");

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

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

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

            oorder.o_id = c;
            oorder.o_w_id = w;
            oorder.o_d_id = d;
            oorder.o_c_id = jTPCCUtil.randomNumber(1, custDistKount, gen);
            oorder.o_carrier_id = jTPCCUtil.randomNumber(1, 10, gen);
            oorder.o_ol_cnt = jTPCCUtil.randomNumber(5, 15, gen);
            oorder.o_all_local = 1;
            oorder.o_entry_d = System.currentTimeMillis();

            k++;
            if (outputFiles == false) {
              myJdbcIO.insertOrder(ordrPrepStmt, oorder);
            } else {
              String str = "";
              str = str + oorder.o_id + ",";
              str = str + oorder.o_w_id + ",";
              str = str + oorder.o_d_id + ",";
              str = str + oorder.o_c_id + ",";
              str = str + oorder.o_carrier_id + ",";
              str = str + oorder.o_ol_cnt + ",";
              str = str + oorder.o_all_local + ",";
              Timestamp entry_d = new java.sql.Timestamp(oorder.o_entry_d);
              str = str + entry_d;
              out.println(str);
            }

            // 900 rows in the NEW-ORDER table corresponding to the last
            // 900 rows in the ORDER table for that district (i.e., with
            // NO_O_ID between 2,101 and 3,000)

            if (c > 2100) {

              new_order.no_w_id = w;
              new_order.no_d_id = d;
              new_order.no_o_id = c;

              k++;
              if (outputFiles == false) {
                myJdbcIO.insertNewOrder(nworPrepStmt, new_order);
              } else {
                String str = "";
                str = str + new_order.no_w_id + ",";
                str = str + new_order.no_d_id + ",";
                str = str + new_order.no_o_id;
                outNewOrder.println(str);
              }
            } // end new order

            for (int l = 1; l <= oorder.o_ol_cnt; l++) {

              order_line.ol_w_id = w;
              order_line.ol_d_id = d;
              order_line.ol_o_id = c;
              order_line.ol_number = l; // ol_number
              order_line.ol_i_id = jTPCCUtil.randomNumber(1, 100000, gen);
              order_line.ol_delivery_d = oorder.o_entry_d;

              if (order_line.ol_o_id < 2101) {
                order_line.ol_amount = 0;
              } else {
                // random within [0.01 .. 9,999.99]

                float f = (float) (jTPCCUtil.randomNumber(1, 999999, gen) / 100.0);

                order_line.ol_amount = f; // XXX float error line 1.
              }

              order_line.ol_supply_w_id = jTPCCUtil.randomNumber(1, numWarehouses, gen);
              order_line.ol_quantity = 5;
              order_line.ol_dist_info = jTPCCUtil.randomStr(24);

              k++;
              if (outputFiles == false) {
                myJdbcIO.insertOrderLine(orlnPrepStmt, order_line);
              } else {
                String str = "";
                str = str + order_line.ol_w_id + ",";
                str = str + order_line.ol_d_id + ",";
                str = str + order_line.ol_o_id + ",";
                str = str + order_line.ol_number + ",";
                str = str + order_line.ol_i_id + ",";
                Timestamp delivery_d = new Timestamp(order_line.ol_delivery_d);
                str = str + delivery_d + ",";
                str = str + order_line.ol_amount + ",";
                str = str + order_line.ol_supply_w_id + ",";
                str = str + order_line.ol_quantity + ",";
                str = str + order_line.ol_dist_info;
                outLine.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;
                if (outputFiles == false) {
                  ordrPrepStmt.executeBatch();
                  nworPrepStmt.executeBatch();
                  orlnPrepStmt.executeBatch();
                  ordrPrepStmt.clearBatch();
                  nworPrepStmt.clearBatch();
                  orlnPrepStmt.clearBatch();
                  transCommit();
                }
              }
            } // end for [l]
          } // end for [c]
        } // end for [d]
      } // end for [w]

      System.out.println("  Writing final records " + k + " of " + t);
      if (outputFiles == false) {
        ordrPrepStmt.executeBatch();
        nworPrepStmt.executeBatch();
        orlnPrepStmt.executeBatch();
      } else {
        outLine.close();
        outNewOrder.close();
      }
      transCommit();
      now = new java.util.Date();
      System.out.println("End Orders Load @  " + now);

    } catch (Exception e) {
      e.printStackTrace();
      transRollback();
      if (outputFiles == true) {
        outLine.close();
        outNewOrder.close();
      }
    }

    return (k);
  } // end loadOrder()
Esempio n. 25
0
  /** 执行批量操作 */
  public int executeBatchUpdate(Connection con) throws SQLException {
    int totalUpdatedRows = 0;
    int currentBatchCount = 0;
    String preSql = "", curSql = "";
    PreparedStatement updateStatement = null;
    int[] batchRows = null;

    try {
      Iterator itor = batchRequestList.iterator();
      while (itor.hasNext()) {
        SqlRequest curReqest = (SqlRequest) itor.next();
        curSql = curReqest.getSqlText();
        String sqlId = curReqest.getSqlId();
        String[] paramNames = curReqest.getParamNames();
        Object[] paramValues = curReqest.getParamValues();
        int[] paramSqlTypeCodes = curReqest.getParamSqlTypeCodes();
        JdaTypePersister[] paramTypePersisters = curReqest.getParamTypePersisters();
        ParamValueMode[] paramValueModes = curReqest.getParamValueModes();
        JdaSessionImpl session = curReqest.getRequestSession();

        /**
         * 满足批量更新的条件如下: 1:上一个Statement更新进去的数据已经达到Size,则需要执行前面Statement,
         * 2:当前的SQL与前面执行SQL不同时候,则需要执行前面Statement, 3:到达数据列表的终点,不论1与2,都需要执行当前的Statement
         */
        if (updateStatement == null)
          updateStatement = SqlRequestHandler.createGeneralStatement(con, curSql);

        if (currentBatchCount == batchUpdateSize) { // Size已满,需要执行批量更新了
          batchRows = updateStatement.executeBatch();
          totalUpdatedRows += getUpdateCount(batchRows);
          updateStatement.clearBatch();
          currentBatchCount = 0;
        }

        if (curSql.equalsIgnoreCase(preSql)) {
          ParamFactory.setParamValues(
              session,
              sqlId,
              updateStatement,
              paramNames,
              paramValues,
              paramSqlTypeCodes,
              paramTypePersisters,
              paramValueModes);
          updateStatement.addBatch();
          currentBatchCount++;
          preSql = curSql;
        } else if (!curSql.equalsIgnoreCase(preSql)) {
          batchRows = updateStatement.executeBatch();
          totalUpdatedRows += getUpdateCount(batchRows);
          updateStatement.clearBatch();
          currentBatchCount = 0;
          CloseUtil.close(updateStatement);
          updateStatement = null;

          updateStatement = SqlRequestHandler.createGeneralStatement(con, curSql);
          ParamFactory.setParamValues(
              session,
              sqlId,
              updateStatement,
              paramNames,
              paramValues,
              paramSqlTypeCodes,
              paramTypePersisters,
              paramValueModes);
          currentBatchCount++;
          preSql = curSql;
        }
      }

      if (currentBatchCount > 0) {
        batchRows = updateStatement.executeBatch();
        totalUpdatedRows += getUpdateCount(batchRows);
        updateStatement.clearBatch();
        currentBatchCount = 0;
        CloseUtil.close(updateStatement);
        updateStatement = null;
      }
    } catch (SQLException e) {
      if (updateStatement != null) {
        try {
          updateStatement.clearBatch();
          CloseUtil.close(updateStatement);
          updateStatement = null;
        } catch (Exception ee) {
        }
      }
      throw e;
    }

    return totalUpdatedRows;
  }
Esempio n. 26
0
 public void clearBatch() throws SQLException {
   this.prepare();
   ps.clearBatch();
 }
  /** 插入测试数据 */
  public void insertTbService() {

    DataBaseConnection dbc = DataBaseConnection.getInstance(dbdriver, dburl, username, password);

    PreparedStatement pstmt = null;

    try {

      Long beginTime = System.currentTimeMillis();
      BigDecimal lon = new BigDecimal(68216140.000000);
      BigDecimal lat = new BigDecimal(20810506.000000);
      BigDecimal add = new BigDecimal(5.000000);
      Long timer = System.currentTimeMillis();
      String no = "京";

      String sql =
          "insert into MEM_TB_SERVICEVIEW(vid,vehicleno,cname,maplon,maplat,speed,corp_id,corp_name,team_id,team_name,utc,alarmcode,cid,PLATE_COLOR_ID,VEHICLETYPE_ID) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

      pstmt = dbc.getConnection().prepareStatement(sql);

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

        pstmt.setInt(1, i);
        pstmt.setString(2, no + i);
        pstmt.setString(3, "abc");
        lon = lon.add(add);
        lat = lat.add(add);
        pstmt.setBigDecimal(4, lon);
        pstmt.setBigDecimal(5, lat);
        pstmt.setInt(6, 20);
        pstmt.setInt(7, 15);
        pstmt.setString(8, "中交兴路");
        pstmt.setInt(9, 14);
        pstmt.setString(10, "默认车队");
        timer = timer + 25;
        pstmt.setLong(11, timer);
        pstmt.setString(12, "12");
        pstmt.setInt(13, 15);
        pstmt.setInt(14, 1);
        pstmt.setInt(15, 35);

        pstmt.addBatch();

        // 批量保存
        if (i % 1000 == 0) {
          pstmt.executeBatch();

          pstmt.clearBatch();

          Long endTime = System.currentTimeMillis();
          System.out.println("pstmt+batch:" + (endTime - beginTime) / 1000 + "秒");
        }
      }

      Long endTimes = System.currentTimeMillis();
      System.out.println("pstmt+batch:" + (endTimes - beginTime) / 1000 + "秒");

      // pstmt.close();

    } catch (Exception e) {

      e.printStackTrace();

    } finally {

      // 关闭数据库连接
      try {
        dbc.close(pstmt, null, dbc.getConnection());

      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
Esempio n. 28
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()
Esempio n. 29
0
  private void insertBatches() throws DaoException, SQLException, InterruptedException {
    boolean finished = false;

    Connection cnx = ds.getConnection();
    if (isPostGisLoader) {
      try {

        ((org.postgresql.PGConnection) cnx)
            .addDataType("geometry", Class.forName("org.postgis.PGgeometry"));
        //                ((org.postgresql.PGConnection) cnx).addDataType("geometry",
        // Class.forName("org.postgis.Multipolygon"));

      } catch (ClassNotFoundException e) {
        throw new DaoException(
            "Could not find PostGIS geometry type. Is the PostGIS library in the class path?: "
                + e.getMessage());
      }
    }

    PreparedStatement statement = null;
    try {
      String[] names = new String[fields.length];
      String[] questions = new String[fields.length];
      for (int i = 0; i < fields.length; i++) {
        names[i] = fields[i];
        questions[i] = "?";
      }
      String sql =
          "INSERT INTO "
              + table
              + "("
              + StringUtils.join(names, ",")
              + ") "
              + "VALUES ("
              + StringUtils.join(questions, ",")
              + ");";
      statement = cnx.prepareStatement(sql);

      while (!finished && inserterState != InserterState.FAILED) {
        // accumulate batch
        int batchSize = 0;
        while (!finished && batchSize < BATCH_SIZE && inserterState != InserterState.FAILED) {
          Object row[] = rowBuffer.poll(100, TimeUnit.MILLISECONDS);
          if (row == null) {
            // do nothing
          } else if (row[0] == POSION_PILL) {
            rowBuffer.put(new Object[] {POSION_PILL});
            finished = true;
          } else {
            batchSize++;
            for (int i = 0; i < row.length; i++) {
              if (row[i] != null && row[i].getClass().equals(java.lang.Character.class))
                statement.setObject(i + 1, row[i].toString());
              else statement.setObject(i + 1, row[i]);
            }
            statement.addBatch();
          }
        }
        try {
          statement.executeBatch();
          cnx.commit();
        } catch (SQLException e) {
          cnx.rollback();
          while (e != null) {
            LOG.error("insert batch failed, attempting to continue:", e);
            e = e.getNextException();
          }
        }
        statement.clearBatch();
      }
    } finally {
      if (statement != null) {
        JDBCUtils.safeClose(statement);
      }
      AbstractSqlDao.quietlyCloseConn(cnx);
    }
  }
Esempio n. 30
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()