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());
}
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());
}
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());
}
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());
}
@SuppressWarnings("unused")
public static void main(String[] args) throws IOException, SQLException {
// Evaluation Timer
System.out.println("[MAIN PROCESS] Started: " + getCurrentTimeStamp());
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[MAIN PROCESS] Started: " + getCurrentTimeStamp());
long startTime = System.currentTimeMillis();
pointDaemon = new PointUpdaterDaemon();
// Choose these options:
// (A) Fresh start with ALL INSERT. Separated Tables.
// 3 Separated Point Tables would be Created, then Inserted.
// Creating Table must be DONE BEFORE running INSERTION Threads.
pointDaemon.createTruncateSeparatedPointTables();
// 1. With Single, Blocking Process (No Thread Programming)
if (false) {
pointDaemon.insertMonthlyPoint();
pointDaemon.insertQuarterlyPoint();
pointDaemon.insertGrandprizePoint();
}
// 2. With Concurrent, Multi-Thread Programming
else if (true) {
Runnable monthlyRunnable =
new Runnable() {
public void run() {
System.out.println(
"[THREAD] ["
+ getCurrentTimeStamp()
+ "]"
+ "Thread 1: Inserting Monthly Point Records..");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[THREAD 1 START] [" + getCurrentTimeStamp() + "]");
try {
pointDaemon.insertMonthlyPoint();
} catch (IOException | SQLException e) {
e.printStackTrace();
}
System.out.println(
"[THREAD] [" + getCurrentTimeStamp() + "]" + "Thread 1: Finished.");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[THREAD 2 STOP] [" + getCurrentTimeStamp() + "]");
}
};
Runnable quarterlyRunnable =
new Runnable() {
public void run() {
System.out.println(
"[THREAD] ["
+ getCurrentTimeStamp()
+ "]"
+ "Thread 2: Inserting Quarterly Point Records..");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[THREAD 2 START] [" + getCurrentTimeStamp() + "]");
try {
pointDaemon.insertQuarterlyPoint();
} catch (IOException | SQLException e) {
e.printStackTrace();
}
System.out.println(
"[THREAD] [" + getCurrentTimeStamp() + "]" + "Thread 2: Finished.");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[THREAD 2 STOP] [" + getCurrentTimeStamp() + "]");
}
};
Runnable grandprizeRunnable =
new Runnable() {
public void run() {
System.out.println(
"["
+ getCurrentTimeStamp()
+ "]"
+ "Thread 3: Inserting Grand Prize Point Records..");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[THREAD 3 START] [" + getCurrentTimeStamp() + "]");
try {
pointDaemon.insertGrandprizePoint();
} catch (IOException | SQLException e) {
e.printStackTrace();
}
System.out.println("[" + getCurrentTimeStamp() + "]" + "Thread 3: Finished.");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[THREAD 3 STOP] [" + getCurrentTimeStamp() + "]");
}
};
Thread monthlyThread = new Thread(monthlyRunnable);
Thread quarterlyThread = new Thread(quarterlyRunnable);
Thread grandprizeThread = new Thread(grandprizeRunnable);
// Setting Daemon Mode if Necessary
// monthlyThread.setDaemon(true);
// quarterlyThread.setDaemon(true);
// grandprizeThread.setDaemon(true);
monthlyThread.start();
quarterlyThread.start();
grandprizeThread.start();
// Processing Time, If using No Thread Concept
long endTime = System.currentTimeMillis();
long duration = endTime - startTime;
System.out.println("[MAIN PROCESS] Duration: " + duration + " ms.");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[MAIN PROCESS] Finished: " + getCurrentTimeStamp() + "(" + duration + " ms).");
}
// (B) Fresh start with ONE INSERT, TWO UPDATES. One united point Table.
// 1 Point Table would be Created, then Inserted once, Updated twice.
/*
pointDaemon.createTruncateUnitedPointTable();
pointDaemon.insertMonthlyPoint();
pointDaemon.updateQuarterlyPoint();
pointDaemon.updateGrandprizePoint();
*/
// (C) Current start with existing records
/*
pointDaemon.updateMonthlyPoint();
pointDaemon.updateQuarterlyPoint();
pointDaemon.updateGrandprizePoint();
*/
// Final Result Report
if (statCreate) System.out.println("[RESULT]: TABLES CREATED.");
if (statMonthly) System.out.println("[RESULT]: MONTHLY POINTS INSERTED.");
if (statQuarterly) System.out.println("[RESULT]: QUARTERLY POINTS INSERTED.");
if (statGrandprize) System.out.println("[RESULT]: GRAND PRIZE POINTS INSERTED.");
}