コード例 #1
0
  public void insertGrandprizePoint() throws IOException, SQLException {
    recordCounter = 0;
    System.out.println("Starting inserting Grand Prize point records into points' table..");
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(),
        "[INSERT GRANDPRIZE] Started: " + getCurrentTimeStamp());
    // INSERT MONTHLY - Supposed to be executed on INIT w/CREATE TABLE function
    // Binding a single input stream CSV reader & its buffer
    CSVReader grandprizeReader =
        new CSVReader(
            new FileReader(workingDir + grandprizeFile), recSeparator, escChar, headerLine);
    String[] grandprize;
    String insertGrandprizeQuery;

    if (con == null) {
      db_object.openConnection();
      con = db_object.getConnection();
    }

    try {
      stm = con.createStatement();
      while ((grandprize = grandprizeReader.readNext()) != null) {
        // System.out.println(monthly[0] + monthly[1] + monthly[2]);
        insertGrandprizeQuery =
            "INSERT INTO tbl_points_grandprize (point_cardno, point_grandprize) VALUES ('"
                + grandprize[1].trim()
                + "', "
                + grandprize[2].trim()
                + ");";
        stm.addBatch(insertGrandprizeQuery);
        // stm 	     = con.createStatement();
        // affectedRow  = stm.executeUpdate(updateGrandprizeQuery);
        recordCounter++;
      }
      stm.executeBatch();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      stm.close();
      if (con != null) {
        try {
          db_object.closeConnection();
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
          con = null;
        }
      }
    }

    statGrandprize = true;
    System.out.println("Grand Prize Records Inserted:" + recordCounter);
    System.out.println("Grand Prize Records Finished Time: " + getCurrentTimeStamp());
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(),
        "[INSERT GRANDPRIZE] Finished: " + getCurrentTimeStamp());
  }
コード例 #2
0
  public void updateGrandprizePoint() throws IOException, SQLException {
    recordCounter = 0;
    System.out.println("Starting updating grand prize point records into points' table..");
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(),
        "[UPDATE GRANDPRIZE] Started: " + getCurrentTimeStamp());
    // UPDATE GRANDPRIZE
    CSVReader grandprizeReader =
        new CSVReader(
            new FileReader(workingDir + grandprizeFile), recSeparator, escChar, headerLine);
    String[] grandprize;
    String updateGrandprizeQuery;

    if (con == null) {
      db_object.openConnection();
      con = db_object.getConnection();
    }

    try {
      stm = con.createStatement();
      while ((grandprize = grandprizeReader.readNext()) != null) {
        // System.out.println(grandprize[0] + grandprize[1] + grandprize[2]);
        updateGrandprizeQuery =
            "UPDATE tbl_points SET point_grandprize = "
                + grandprize[2].trim()
                + " WHERE point_cardno = '"
                + grandprize[1].trim()
                + "';";
        stm.addBatch(updateGrandprizeQuery);
        recordCounter++;
      }
      stm.executeBatch();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      stm.close();
      if (con != null) {
        try {
          db_object.closeConnection();
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
          con = null;
        }
      }
    }

    statGrandprize = true;
    System.out.println("Updated Grand Prize Records: " + recordCounter);
    System.out.println("Grand Prize Records Finished Time: " + getCurrentTimeStamp());
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(),
        "[UPDATE GRANDPRIZE] Finished: " + getCurrentTimeStamp());
  }
コード例 #3
0
  public void updateMonthlyPoint() throws IOException, SQLException {
    recordCounter = 0;
    System.out.println("Starting updating monthly point records into points' table..");
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(),
        "[UPDATE MONTHLY] Started: " + getCurrentTimeStamp());
    // UPDATE MONTHLY
    // Binding a single input stream CSV reader & its buffer
    CSVReader monthlyReader =
        new CSVReader(new FileReader(workingDir + monthlyFile), recSeparator, escChar, headerLine);
    String[] monthly;
    String updateMonthlyQuery;

    if (con == null) {
      db_object.openConnection();
      con = db_object.getConnection();
    }

    try {
      stm = con.createStatement();
      while ((monthly = monthlyReader.readNext()) != null) {
        // System.out.println(monthly[0] + monthly[1] + monthly[2]);
        updateMonthlyQuery =
            "UPDATE tbl_points SET point_monthly = "
                + monthly[2].trim()
                + " WHERE  point_cardno = '"
                + monthly[1].trim()
                + "';";
        stm.addBatch(updateMonthlyQuery);
        recordCounter++;
      }
      stm.executeBatch();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      stm.close();
      if (con != null) {
        try {
          db_object.closeConnection();
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
          con = null;
        }
      }
    }

    statMonthly = true;
    System.out.println("Updated Monthly Records:" + recordCounter);
    System.out.println("Monthly Records Update Finished Time: " + getCurrentTimeStamp());
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(),
        "[UPDATE MONTHLY] Finished: " + getCurrentTimeStamp());
  }
コード例 #4
0
  public void createTruncateUnitedPointTable() {
    System.out.println(
        "Starting creating a table if not exists yet or truncating non-empty table..");
    System.out.println("[TABLE] Started: " + getCurrentTimeStamp());
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(), "[TABLE] Started: " + getCurrentTimeStamp());
    // CREATE TABLE & TRUNCATE AL PREV. DATA
    String createPointTableQuery =
        "CREATE TABLE IF NOT EXISTS `tbl_points` ("
            + " `point_id` int(11) NOT NULL NOT NULL DEFAULT '0',"
            + " `point_no` int(7) NOT NULL DEFAULT '0',"
            + " `point_accnum` varchar(20) NOT NULL DEFAULT '0',"
            + " `point_cardno` varchar(16) NOT NULL DEFAULT '0',"
            + " `point_monthly` int(6) NOT NULL DEFAULT '0',"
            + " `point_quarterly` int(6) NOT NULL DEFAULT '0',"
            + " `point_grandprize` int(6) NOT NULL DEFAULT '0',"
            + " `deleted` int(1) NOT NULL DEFAULT '0',"
            + " `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP"
            + ") ENGINE=InnoDB DEFAULT CHARSET=latin1;"
            + "ALTER TABLE `tbl_points` ADD PRIMARY KEY (`point_id`);"
            + "ALTER TABLE `tbl_points` MODIFY `point_id` int(11) NOT NULL AUTO_INCREMENT;";

    String truncatePointTableQuery = "TRUNCATE TABLE  `tbl_points`;";

    try {
      if (con == null) {
        db_object.openConnection();
        con = db_object.getConnection();
      }
      stm = con.createStatement();
      affectedRow = stm.executeUpdate(createPointTableQuery);
      affectedRow = stm.executeUpdate(truncatePointTableQuery);
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      if (con != null) {
        try {
          db_object.closeConnection();
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
          con = null;
        }
      }
      statCreate = true;
    }
    System.out.println("[TABLE] Finished: " + getCurrentTimeStamp());
    LogLoader.setInfo(
        PointUpdaterDaemon.class.getSimpleName(), "[TABLE] Finished: " + getCurrentTimeStamp());
  }
コード例 #5
0
  public PointUpdaterDaemon() {
    con = null;
    stm = null;
    //	rs               = null;
    //	affectedRow      = 0;
    recordCounter = 0;
    statCreate = false;
    statMonthly = false;
    statQuarterly = false;
    statGrandprize = false;
    workingDir = System.getProperty("user.dir");
    monthlyFile = PropertiesLoader.getProperty("POINT_MONTHLY_FILE");
    quarterlyFile = PropertiesLoader.getProperty("POINT_QUARTERLY_FILE");
    grandprizeFile = PropertiesLoader.getProperty("POINT_GRANDPRIZE_FILE");
    recSeparator = '|';
    escChar = '\'';
    headerLine = 1;

    // Establishing a Single DB Connection.
    // This Connection is usable across app life-cycle.
    try {
      db_object = new MysqlConnect();
      con = db_object.getConnection();
    } catch (ClassNotFoundException nfe) {
      nfe.printStackTrace();
    } catch (SQLException sqle) {
      sqle.printStackTrace();
    }
  }