@Override
public HTTPResponse update(HTTPRequest request, HTTPResponse response) {
Key key = Key.create(request, response);
Connection connection = getConnection();
PreparedStatement statement = null;
try {
JdbcUtil.startTransaction(connection);
statement =
connection.prepareStatement(
"update response set headers = ?, cachetime = ? where uri = ? and vary = ?");
statement.setString(1, response.getHeaders().toJSON());
statement.setTimestamp(2, new Timestamp(DateTimeUtils.currentTimeMillis()));
statement.setString(3, key.getURI().toString());
statement.setString(4, key.getVary().toJSON());
statement.executeUpdate();
connection.commit();
return getImpl(connection, key);
} catch (SQLException e) {
JdbcUtil.rollback(connection);
JdbcUtil.close(connection);
throw new DataAccessException(e);
} finally {
JdbcUtil.endTransaction(connection);
JdbcUtil.close(statement);
}
}
/**
* Find first Year Period of DateAcct based on Client Calendar
*
* @param ctx context
* @param C_Calendar_ID calendar
* @param DateAcct date
* @return active first Period
*/
public static MPeriod getFirstInYear(Ctx ctx, int C_Calendar_ID, Timestamp DateAcct) {
MPeriod retValue = null;
String sql =
"SELECT * "
+ "FROM C_Period "
+ "WHERE C_Year_ID IN "
+ "(SELECT p.C_Year_ID "
+ "FROM C_Year y"
+ " INNER JOIN C_Period p ON (y.C_Year_ID=p.C_Year_ID) "
+ "WHERE y.C_Calendar_ID=?"
+ " AND ? BETWEEN StartDate AND EndDate)"
+ " AND IsActive='Y' AND PeriodType='S' "
+ "ORDER BY StartDate";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, (Trx) null);
pstmt.setInt(1, C_Calendar_ID);
pstmt.setTimestamp(2, DateAcct);
rs = pstmt.executeQuery();
if (rs.next()) // first only
retValue = new MPeriod(ctx, rs, null);
} catch (SQLException e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeStatement(pstmt);
DB.closeResultSet(rs);
}
return retValue;
} // getFirstInYear
public static void setTimestamp(PreparedStatement stm, int i, Date date, boolean isNull)
throws SQLException {
if (isNull) {
stm.setNull(i, Types.TIMESTAMP);
} else {
stm.setTimestamp(i, new Timestamp(date.getTime()));
}
}
/**
* Find standard Period of DateAcct based on Client Calendar
*
* @param ctx context
* @param C_Calendar_ID calendar
* @param DateAcct date
* @return active Period or null
*/
public static MPeriod getOfCalendar(Ctx ctx, int C_Calendar_ID, Timestamp DateAcct) {
if (DateAcct == null) {
s_log.warning("No DateAcct");
return null;
}
if (C_Calendar_ID == 0) {
s_log.warning("No Calendar");
return null;
}
// Search in Cache first
Iterator<MPeriod> it = s_cache.values().iterator();
while (it.hasNext()) {
MPeriod period = it.next();
if (period.getC_Calendar_ID() == C_Calendar_ID
&& period.isStandardPeriod()
&& period.isInPeriod(DateAcct)) return period;
}
// Get it from DB
MPeriod retValue = null;
String sql =
"SELECT * FROM C_Period "
+ "WHERE C_Year_ID IN "
+ "(SELECT C_Year_ID FROM C_Year WHERE C_Calendar_ID=?)"
+ " AND ? BETWEEN TRUNC(StartDate,'DD') AND TRUNC(EndDate,'DD')"
+ " AND IsActive='Y' AND PeriodType='S'";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, (Trx) null);
pstmt.setInt(1, C_Calendar_ID);
pstmt.setTimestamp(2, TimeUtil.getDay(DateAcct));
rs = pstmt.executeQuery();
while (rs.next()) {
MPeriod period = new MPeriod(ctx, rs, null);
Integer key = Integer.valueOf(period.getC_Period_ID());
s_cache.put(key, period);
if (period.isStandardPeriod()) retValue = period;
}
} catch (SQLException e) {
s_log.log(Level.SEVERE, "DateAcct=" + DateAcct, e);
} finally {
DB.closeStatement(pstmt);
DB.closeResultSet(rs);
}
if (retValue == null)
s_log.warning(
"No Standard Period for " + DateAcct + " (C_Calendar_ID=" + C_Calendar_ID + ")");
return retValue;
} // get
@Override
public HTTPResponse insert(HTTPRequest request, HTTPResponse response) {
Key key = Key.create(request, response);
Connection connection = getConnection();
String sql =
"insert into response(uri, vary, status, headers, payload, mimeType, cachetime) values (?, ?, ?, ?, ?, ?, ?)";
PreparedStatement statement = null;
try {
JdbcUtil.startTransaction(connection);
invalidate(key, connection);
statement = connection.prepareStatement(sql);
statement.setString(1, key.getURI().toString());
statement.setString(2, key.getVary().toJSON());
statement.setInt(3, response.getStatus().getCode());
statement.setString(4, response.getHeaders().toJSON());
InputStream inputStream = null;
if (response.hasPayload() && response.getPayload().isAvailable()) {
statement.setString(6, response.getPayload().getMimeType().toString());
inputStream = response.getPayload().getInputStream();
statement.setBinaryStream(5, inputStream);
} else {
statement.setNull(5, Types.BLOB);
statement.setNull(6, Types.VARCHAR);
}
statement.setTimestamp(7, new Timestamp(DateTimeUtils.currentTimeMillis()));
try {
statement.executeUpdate();
} finally {
IOUtils.closeQuietly(inputStream);
}
connection.commit();
return getImpl(connection, key);
} catch (SQLException e) {
JdbcUtil.rollback(connection);
JdbcUtil.close(connection);
throw new DataAccessException(e);
} finally {
JdbcUtil.endTransaction(connection);
JdbcUtil.close(statement);
}
}
/**
* Find Period of Date based on Client Calendar, it need not be a standard period (used in MRP)
*
* @param ctx context
* @param C_Calendar_ID calendar
* @param Date date
* @param trx trx
* @return active Period or null
*/
public static MPeriod getPeriod(Ctx ctx, int C_Calendar_ID, Timestamp Date, Trx trx) {
if (Date == null) {
s_log.warning("No Date");
return null;
}
if (C_Calendar_ID == 0) {
s_log.warning("No Calendar");
return null;
}
// Get it from DB
PreparedStatement pstmt = null;
ResultSet rs = null;
MPeriod retValue = null;
String sql =
"SELECT * FROM C_Period "
+ "WHERE C_Year_ID IN "
+ "(SELECT C_Year_ID FROM C_Year WHERE C_Calendar_ID=?)"
+ " AND ? BETWEEN TRUNC(StartDate,'DD') AND TRUNC(EndDate,'DD')"
+ " AND IsActive='Y' ";
try {
pstmt = DB.prepareStatement(sql, trx);
pstmt.setInt(1, C_Calendar_ID);
pstmt.setTimestamp(2, TimeUtil.getDay(Date));
rs = pstmt.executeQuery();
if (rs.next()) {
retValue = new MPeriod(ctx, rs, trx);
}
} catch (SQLException e) {
s_log.log(Level.SEVERE, "DateAcct=" + Date, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
if (retValue == null)
s_log.warning("No Period for " + Date + " (C_Calendar_ID=" + C_Calendar_ID + ")");
return retValue;
} // getPeriod
/**
* Add the event to the end of queue.
*
* @param event The event to add to the queue.
* @exception FrameworkException Thrown on errors.
*/
public void add(Event event) throws FrameworkException {
Debug.log(Debug.MSG_STATUS, "QUEUE OPERATION: Adding event to database queue ...");
Connection dbConn = null;
PreparedStatement ps = null;
long startTime = -1;
if (Debug.isLevelEnabled(Debug.BENCHMARK)) startTime = System.currentTimeMillis();
try {
event.id = PersistentSequence.getNextSequenceValue(SEQUENCE_NAME);
dbConn = DBConnectionPool.getInstance().acquireConnection();
event.arrivalTime = new java.sql.Timestamp(System.currentTimeMillis());
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + INSERT_EVENT_SQL);
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "Event being inserted into database:\n" + event.describe());
if (Debug.isLevelEnabled(Debug.MSG_DATA))
Debug.log(Debug.MSG_DATA, "Event contents:\n" + event.message);
ps = dbConn.prepareStatement(INSERT_EVENT_SQL);
ps.setString(1, event.channelName);
ps.setInt(2, event.id);
DBLOBUtils.setCLOB(ps, 3, event.message);
ps.setTimestamp(4, event.arrivalTime);
int numRows = ps.executeUpdate();
if (numRows != 1) {
String errMsg =
"Execution of SQL statement ["
+ INSERT_EVENT_SQL
+ "] affected ["
+ numRows
+ "] rows.";
Debug.error(errMsg);
throw new FrameworkException(errMsg);
}
DBConnectionPool.getInstance().commit(dbConn);
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "Successfully committed SQL operation.\n" + LINE);
// NOTE: We don't add the item just inserted into the database into the in-memory
// queue, as we want it loaded by the separate dequeueing thread.
} catch (SQLException sqle) {
throw new DatabaseException(
"ERROR: Could not execute SQL statement:\n" + DBInterface.getSQLErrorMessage(sqle));
} catch (Exception e) {
throw new DatabaseException("ERROR: Could not execute SQL statement:\n" + e.toString());
} finally {
releaseDatabaseResources(dbConn, ps);
if (Debug.isLevelEnabled(Debug.BENCHMARK) && (startTime > 0)) {
long stopTime = System.currentTimeMillis();
Debug.log(
Debug.BENCHMARK,
"ELAPSED TIME ["
+ (stopTime - startTime)
+ "] msec: "
+ "SQL: Time to insert event into PersistentEvent database table.");
}
}
}
/**
* Reset any events meeting the given criteria so that they can be retried.
*
* @param criteria An event containing the event-selection criteria.
* @return The number of events reset.
* @exception FrameworkException Thrown on errors.
*/
protected static int reset(Event criteria) throws FrameworkException {
Debug.log(
Debug.MSG_STATUS, "QUEUE OPERATION: Resetting events in database for database queue ...");
if (!StringUtils.hasValue(criteria.channelName)) {
throw new FrameworkException(
"ERROR: Event channel name is a required queue search criteria.");
}
Connection dbConn = null;
PreparedStatement ps = null;
long startTime = -1;
if (Debug.isLevelEnabled(Debug.BENCHMARK)) startTime = System.currentTimeMillis();
try {
dbConn = DBConnectionPool.getInstance().acquireConnection();
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(
Debug.DB_DATA, "Criteria used to reset events in database:\n" + criteria.describe());
// If no identifier was given that uniquely identifies a single event ...
if (criteria.id == 0) {
// Use last error time and error count, if available.
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + UPDATE_EVENT_RETRY_SQL);
ps = dbConn.prepareStatement(UPDATE_EVENT_RETRY_SQL);
ps.setString(1, criteria.channelName);
if (criteria.lastErrorTime == null) ps.setNull(2, Types.DATE);
else ps.setTimestamp(2, criteria.lastErrorTime);
if (criteria.errorCount < 1) ps.setNull(3, Types.INTEGER);
else ps.setInt(3, criteria.errorCount);
} else {
// An Id was given which should uniquely identify a single event, so we should
// skip using any other qualifying criteria, if present.
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(
Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + UPDATE_EVENT_RETRY_BY_ID_SQL);
ps = dbConn.prepareStatement(UPDATE_EVENT_RETRY_BY_ID_SQL);
ps.setString(1, criteria.channelName);
ps.setInt(2, criteria.id);
}
int numRows = ps.executeUpdate();
DBConnectionPool.getInstance().commit(dbConn);
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(
Debug.DB_DATA, "Committed SQL execution affected [" + numRows + "] rows.\n" + LINE);
return numRows;
} catch (SQLException sqle) {
throw new DatabaseException(
"ERROR: Could not execute SQL statement:\n" + DBInterface.getSQLErrorMessage(sqle));
} catch (Exception e) {
throw new DatabaseException("ERROR: Could not execute SQL statement:\n" + e.toString());
} finally {
releaseDatabaseResources(dbConn, ps);
if (Debug.isLevelEnabled(Debug.BENCHMARK) && (startTime > 0)) {
long stopTime = System.currentTimeMillis();
Debug.log(
Debug.BENCHMARK,
"ELAPSED TIME ["
+ (stopTime - startTime)
+ "] msec: "
+ "SQL: Time to reset event(s) in PersistentEvent database table.");
}
}
}
/**
* Update the given event as indicated.
*
* @param event The event to update.
* @param eventStatus The event delivery status.
* @exception FrameworkException Thrown on errors.
*/
public void update(Event event, EventStatus eventStatus) throws FrameworkException {
if (Debug.isLevelEnabled(Debug.MSG_STATUS))
Debug.log(
Debug.MSG_STATUS,
"QUEUE OPERATION: Updating database queue using event status ["
+ eventStatus.name
+ "] ...");
// If no consumers were available, the event delivery wasn't attempted so leave
// the queue in its current state.
if (eventStatus == EventStatus.NO_CONSUMERS_AVAILABLE) {
Debug.log(
Debug.MSG_STATUS, "Skipping queue update, as no consumers were available to process it.");
return;
}
Connection dbConn = null;
PreparedStatement ps = null;
long startTime = -1;
if (Debug.isLevelEnabled(Debug.BENCHMARK)) startTime = System.currentTimeMillis();
try {
dbConn = DBConnectionPool.getInstance().acquireConnection();
if (eventStatus == EventStatus.DELIVERY_SUCCESSFUL) {
// If the event was successfully delivered, update status in database to delivered.
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + UPDATE_EVENT_SUCCESS_SQL);
ps = dbConn.prepareStatement(UPDATE_EVENT_SUCCESS_SQL);
java.sql.Timestamp ts = new java.sql.Timestamp(System.currentTimeMillis());
ps.setTimestamp(1, ts);
ps.setString(2, event.channelName);
ps.setInt(3, event.id);
} else if (eventStatus == EventStatus.DELIVERY_FAILED) {
// If the event delivery failed, we mark it as failed in the database.
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + UPDATE_EVENT_ERROR_SQL);
// Truncate error message if it's larger than database column.
if ((event.lastErrorMessage != null)
&& (event.lastErrorMessage.length() > MAX_ERROR_MESSAGE_LENGTH))
event.lastErrorMessage = event.lastErrorMessage.substring(0, MAX_ERROR_MESSAGE_LENGTH);
event.lastErrorTime = new java.sql.Timestamp(System.currentTimeMillis());
ps = dbConn.prepareStatement(UPDATE_EVENT_ERROR_SQL);
ps.setTimestamp(1, event.lastErrorTime);
ps.setString(2, event.lastErrorMessage);
ps.setString(3, event.channelName);
ps.setInt(4, event.id);
} else {
throw new FrameworkException(
"ERROR: Invalid event update type [" + eventStatus.name + "].");
}
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "Event being operated on in database:\n" + event.describe());
int numRows = ps.executeUpdate();
if (numRows > 1) {
String errMsg = "Execution of update SQL statement affected [" + numRows + "] rows.";
Debug.error(errMsg);
throw new FrameworkException(errMsg);
}
DBConnectionPool.getInstance().commit(dbConn);
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "Successfully committed SQL operation.\n" + LINE);
// At this point, the event should be removed from the in-memory buffer of events as well,
// irrespective of processing outcome.
if (Debug.isLevelEnabled(Debug.MSG_STATUS))
Debug.log(
Debug.MSG_STATUS,
"Removing event [" + event.describe() + "] from in-memory queue buffer.");
boolean removed = queue.remove(event);
if (Debug.isLevelEnabled(Debug.MSG_STATUS))
Debug.log(
Debug.MSG_STATUS,
"Event removed? ["
+ removed
+ "]. In-memory queue buffer size ["
+ queue.size()
+ "].");
} catch (SQLException sqle) {
throw new DatabaseException(
"ERROR: Could not execute SQL statement:\n" + DBInterface.getSQLErrorMessage(sqle));
} catch (Exception e) {
throw new DatabaseException("ERROR: Could not execute SQL statement:\n" + e.toString());
} finally {
releaseDatabaseResources(dbConn, ps);
if (Debug.isLevelEnabled(Debug.BENCHMARK) && (startTime > 0)) {
long stopTime = System.currentTimeMillis();
Debug.log(
Debug.BENCHMARK,
"ELAPSED TIME ["
+ (stopTime - startTime)
+ "] msec: "
+ "SQL: Time to update event in PersistentEvent database table.");
}
}
}
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()
/**
* Saves the templates to the database.
*
* @throws java.sql.SQLException Thrown on sql error.
*/
public void saveToDatabase() throws java.sql.SQLException {
setProgressIndeterminate(true);
setMessage("Saving Templates");
ArrayList templates = getTemplates();
Connection oracleConnection = getDataSource().getConnection();
try {
oracleConnection.setAutoCommit(false);
Statement query = oracleConnection.createStatement();
try {
int templateCount = templates.size();
// First remove any existing entries.
StringBuffer sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_SGNL_FLD");
StringBuffer whereClause = new StringBuffer(" WHERE TMPL_ID IN (");
for (int i = 0; i < templateCount; i++) {
if (i > 0) whereClause.append(", ");
whereClause.append("'");
whereClause.append(((Template) templates.get(i)).getID());
whereClause.append("'");
}
whereClause.append(")");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_MACRO");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_SGNL_REC");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_SGNL_FLD");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TEMPLATE");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ_GRP");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ_GRP_ARCH_REQ");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TEMPLATE (TMPL_ID, TMPL_DESC, EXT_SRC_FILE_NM, EXT_SRC_FILE_MOD_DTE) VALUES (?, ?, ?, ?)");
PreparedStatement templateInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_MACRO (TMPL_ID, MACRO_ID) VALUES (?, ?)");
PreparedStatement macroInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_SGNL_REC (TMPL_ID, TMPL_SGNL_ID, REC_TYPE_ID, ARCH_IND, ARCH_FREQ, ARCH_TYPE) VALUES (?, ?, ?, ?, ?, ?)");
PreparedStatement signalInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_SGNL_FLD (TMPL_ID, TMPL_SGNL_ID, FLD_ID, REC_TYPE_ID, VAL) VALUES (?, ?, ?, ?, ?)");
PreparedStatement fieldInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ (TMPL_ID, ARCH_REQ_FILE_NM) VALUES (?, ?)");
PreparedStatement requestInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ_GRP (TMPL_ID, ARCH_REQ_GRP_FILE_NM) VALUES (?, ?)");
PreparedStatement groupInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_ARCH_REQ_GRP_ARCH_REQ (TMPL_ID, ARCH_REQ_GRP_FILE_NM, ARCH_REQ_FILE_NM) VALUES (?, ?, ?)");
PreparedStatement requestGroupInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("UPDATE ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_SGNL_REC SET ARCH_IND = ?, ARCH_FREQ = ?, ARCH_TYPE = ?, ARCH_REQ_FILE = ? WHERE TMPL_ID = ? AND TMPL_SGNL_ID = ? AND REC_TYPE_ID = ?");
PreparedStatement signalUpdateStatement =
oracleConnection.prepareStatement(sql.toString());
try {
int progress = 0;
setProgressMaximum(importedFieldCount + importedMacroCount);
setProgressValue(0);
setProgressIndeterminate(false);
for (int templateIndex = 0;
templateIndex < templateCount;
templateIndex++) {
Template currentTemplate = (Template) templates.get(templateIndex);
String currentTemplateID = currentTemplate.getID();
templateInsertStatement.setString(1, currentTemplateID);
String currentDescription = currentTemplate.getDescription();
if (currentDescription == null)
templateInsertStatement.setNull(2, Types.VARCHAR);
else templateInsertStatement.setString(2, currentDescription);
templateInsertStatement.setString(3, currentTemplate.getFileName());
templateInsertStatement.setTimestamp(
4, currentTemplate.getFileModifiedDate());
templateInsertStatement.execute();
// Need to insert macros.
int macroCount = currentTemplate.getMacroCount();
for (int macroIndex = 0; macroIndex < macroCount; macroIndex++) {
macroInsertStatement.setString(1, currentTemplateID);
String currentMacro = currentTemplate.getMacroAt(macroIndex);
macroInsertStatement.setString(2, currentMacro);
macroInsertStatement.execute();
setProgressValue(++progress);
}
int signalCount = currentTemplate.getSignalCount();
for (int signalIndex = 0; signalIndex < signalCount; signalIndex++) {
Signal currentSignal = currentTemplate.getSignalAt(signalIndex);
String currentSignalID = currentSignal.getID();
String currentRecordTypeID =
currentSignal.getType().getRecordType().getID();
signalInsertStatement.setString(1, currentTemplateID);
signalInsertStatement.setString(2, currentSignalID);
signalInsertStatement.setString(3, currentRecordTypeID);
signalInsertStatement.setString(4, currentSignal.getArchiveIndicator());
BigDecimal currentFrequency = currentSignal.getArchiveFrequency();
if (currentFrequency == null)
currentFrequency = new BigDecimal("60"); // 60 default in RDB
signalInsertStatement.setBigDecimal(5, currentFrequency);
String currentType = currentSignal.getArchiveType();
if (currentType == null)
currentType = "Monitor"; // 'Monitor' default in RDB
signalInsertStatement.setString(6, currentType);
signalInsertStatement.execute();
int fieldCount = currentSignal.getFieldCount();
for (int fieldIndex = 0; fieldIndex < fieldCount; fieldIndex++) {
SignalField currentField = currentSignal.getFieldAt(fieldIndex);
fieldInsertStatement.setString(1, currentTemplateID);
fieldInsertStatement.setString(2, currentSignalID);
fieldInsertStatement.setString(3, currentField.getType().getID());
fieldInsertStatement.setString(4, currentRecordTypeID);
fieldInsertStatement.setString(5, currentField.getValue());
fieldInsertStatement.execute();
if (isParseCanceled()) {
oracleConnection.rollback();
return;
}
setProgressValue(++progress);
}
}
// Insert archive requests.
int requestCount = currentTemplate.getArchiveRequestCount();
for (int requestIndex = 0; requestIndex < requestCount; requestIndex++) {
ArchiveRequest currentRequest =
currentTemplate.getArchiveRequestAt(requestIndex);
String currentRequestFileName = currentRequest.getFileName();
requestInsertStatement.setString(1, currentTemplateID);
requestInsertStatement.setString(2, currentRequestFileName);
requestInsertStatement.execute();
signalCount = currentRequest.getSignalCount();
for (int signalIndex = 0; signalIndex < signalCount; signalIndex++) {
Signal currentSignal = currentRequest.getSignalAt(signalIndex);
String currentSignalID = currentSignal.getID();
signalUpdateStatement.setString(
1, currentSignal.getArchiveIndicator());
signalUpdateStatement.setBigDecimal(
2, currentSignal.getArchiveFrequency());
signalUpdateStatement.setString(3, currentSignal.getArchiveType());
signalUpdateStatement.setString(4, currentRequestFileName);
signalUpdateStatement.setString(5, currentTemplateID);
signalUpdateStatement.setString(6, currentSignalID);
signalUpdateStatement.setString(
7, currentSignal.getType().getRecordType().getID());
signalUpdateStatement.execute();
int fieldCount = currentSignal.getFieldCount();
}
}
// Insert archive groups.
int groupCount = currentTemplate.getArchiveGroupCount();
for (int groupIndex = 0; groupIndex < groupCount; groupIndex++) {
ArchiveGroup currentGroup =
currentTemplate.getArchiveGroupAt(groupIndex);
groupInsertStatement.setString(1, currentTemplateID);
String currentGroupFileName = currentGroup.getFileName();
groupInsertStatement.setString(2, currentGroupFileName);
groupInsertStatement.execute();
requestCount = currentGroup.getArchiveRequestCount();
for (int requestIndex = 0;
requestIndex < requestCount;
requestIndex++) {
ArchiveRequest currentRequest =
currentGroup.getArchiveRequestAt(requestIndex);
String currentRequestFileName = currentRequest.getFileName();
requestGroupInsertStatement.setString(1, currentTemplateID);
requestGroupInsertStatement.setString(2, currentGroupFileName);
requestGroupInsertStatement.setString(
3, currentRequest.getFileName());
}
}
}
} finally {
signalUpdateStatement.close();
}
} finally {
requestGroupInsertStatement.close();
}
} finally {
groupInsertStatement.close();
}
} finally {
requestInsertStatement.close();
}
} finally {
fieldInsertStatement.close();
}
} finally {
signalInsertStatement.close();
}
} finally {
macroInsertStatement.close();
}
} finally {
templateInsertStatement.close();
}
} catch (java.sql.SQLException ex) {
oracleConnection.rollback();
throw ex;
} finally {
query.close();
}
if (isParseCanceled()) oracleConnection.rollback();
else oracleConnection.commit();
} finally {
oracleConnection.close();
}
}
public void testSetTimestamp() throws Exception {
for (int i = 0; i < PREPARE_THRESHOLD; i++) {
con.createStatement().execute("delete from testtimezone");
PreparedStatement insertTimestamp =
con.prepareStatement("INSERT INTO testtimezone(seq,tstz,ts,tz,d) VALUES (?,?,?,?,?)");
int seq = 1;
Timestamp instant = new Timestamp(1104580800000L); // 2005-01-01 12:00:00 UTC
Timestamp instantTime = new Timestamp(instant.getTime() % DAY);
Timestamp instantDateJVM =
new Timestamp(
instant.getTime() - (instant.getTime() % DAY) - TimeZone.getDefault().getRawOffset());
Timestamp instantDateUTC =
new Timestamp(
instant.getTime() - (instant.getTime() % DAY) - cUTC.getTimeZone().getRawOffset());
Timestamp instantDateGMT03 =
new Timestamp(
instant.getTime() - (instant.getTime() % DAY) - cGMT03.getTimeZone().getRawOffset());
Timestamp instantDateGMT05 =
new Timestamp(
instant.getTime() - (instant.getTime() % DAY) - cGMT05.getTimeZone().getRawOffset());
Timestamp instantDateGMT13 =
new Timestamp(
instant.getTime()
- (instant.getTime() % DAY)
- cGMT13.getTimeZone().getRawOffset()
+ DAY);
// +0100 (JVM default)
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTimestamp(2, instant); // 2005-01-01 13:00:00 +0100
insertTimestamp.setTimestamp(3, instant); // 2005-01-01 13:00:00
insertTimestamp.setTimestamp(4, instant); // 13:00:00 +0100
insertTimestamp.setTimestamp(5, instant); // 2005-01-01
insertTimestamp.executeUpdate();
// UTC
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTimestamp(2, instant, cUTC); // 2005-01-01 12:00:00 +0000
insertTimestamp.setTimestamp(3, instant, cUTC); // 2005-01-01 12:00:00
insertTimestamp.setTimestamp(4, instant, cUTC); // 12:00:00 +0000
insertTimestamp.setTimestamp(5, instant, cUTC); // 2005-01-01
insertTimestamp.executeUpdate();
// +0300
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTimestamp(2, instant, cGMT03); // 2005-01-01 15:00:00 +0300
insertTimestamp.setTimestamp(3, instant, cGMT03); // 2005-01-01 15:00:00
insertTimestamp.setTimestamp(4, instant, cGMT03); // 15:00:00 +0300
insertTimestamp.setTimestamp(5, instant, cGMT03); // 2005-01-01
insertTimestamp.executeUpdate();
// -0500
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTimestamp(2, instant, cGMT05); // 2005-01-01 07:00:00 -0500
insertTimestamp.setTimestamp(3, instant, cGMT05); // 2005-01-01 07:00:00
insertTimestamp.setTimestamp(4, instant, cGMT05); // 07:00:00 -0500
insertTimestamp.setTimestamp(5, instant, cGMT05); // 2005-01-01
insertTimestamp.executeUpdate();
if (min73) {
// +1300
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTimestamp(2, instant, cGMT13); // 2005-01-02 01:00:00 +1300
insertTimestamp.setTimestamp(3, instant, cGMT13); // 2005-01-02 01:00:00
insertTimestamp.setTimestamp(4, instant, cGMT13); // 01:00:00 +1300
insertTimestamp.setTimestamp(5, instant, cGMT13); // 2005-01-02
insertTimestamp.executeUpdate();
}
insertTimestamp.close();
// check that insert went correctly by parsing the raw contents in UTC
checkDatabaseContents(
"SELECT seq::text,tstz::text,ts::text,tz::text,d::text from testtimezone ORDER BY seq",
new String[][] {
new String[] {
"1", "2005-01-01 12:00:00+00", "2005-01-01 13:00:00", "13:00:00+01", "2005-01-01"
},
new String[] {
"2", "2005-01-01 12:00:00+00", "2005-01-01 12:00:00", "12:00:00+00", "2005-01-01"
},
new String[] {
"3", "2005-01-01 12:00:00+00", "2005-01-01 15:00:00", "15:00:00+03", "2005-01-01"
},
new String[] {
"4", "2005-01-01 12:00:00+00", "2005-01-01 07:00:00", "07:00:00-05", "2005-01-01"
},
new String[] {
"5", "2005-01-01 12:00:00+00", "2005-01-02 01:00:00", "01:00:00+13", "2005-01-02"
}
});
//
// check results
//
seq = 1;
PreparedStatement ps =
con.prepareStatement("SELECT seq,tstz,ts,tz,d FROM testtimezone ORDER BY seq");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(instant, rs.getTimestamp(2));
assertEquals(instant, rs.getTimestamp(3));
assertEquals(instantTime, rs.getTimestamp(4));
assertEquals(instantDateJVM, rs.getTimestamp(5));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(instant, rs.getTimestamp(2, cUTC));
assertEquals(instant, rs.getTimestamp(3, cUTC));
assertEquals(instantTime, rs.getTimestamp(4, cUTC));
assertEquals(instantDateUTC, rs.getTimestamp(5, cUTC));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(instant, rs.getTimestamp(2, cGMT03));
assertEquals(instant, rs.getTimestamp(3, cGMT03));
assertEquals(instantTime, rs.getTimestamp(4, cGMT03));
assertEquals(instantDateGMT03, rs.getTimestamp(5, cGMT03));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(instant, rs.getTimestamp(2, cGMT05));
assertEquals(instant, rs.getTimestamp(3, cGMT05));
assertEquals(instantTime, rs.getTimestamp(4, cGMT05));
assertEquals(instantDateGMT05, rs.getTimestamp(5, cGMT05));
if (min73) {
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(instant, rs.getTimestamp(2, cGMT13));
assertEquals(instant, rs.getTimestamp(3, cGMT13));
assertEquals(normalizeTimeOfDayPart(instantTime, cGMT13), rs.getTimestamp(4, cGMT13));
assertEquals(instantDateGMT13, rs.getTimestamp(5, cGMT13));
}
assertTrue(!rs.next());
ps.close();
}
}
/**
* This test is broken off from testSetTimestamp because it does not work for pre-7.4 servers and
* putting tons of conditionals in that test makes it largely unreadable. The time data type does
* not accept timestamp with time zone style input on these servers.
*/
public void testSetTimestampOnTime() throws Exception {
// Pre-7.4 servers cannot convert timestamps with timezones to times.
if (!min74) return;
for (int i = 0; i < PREPARE_THRESHOLD; i++) {
con.createStatement().execute("delete from testtimezone");
PreparedStatement insertTimestamp =
con.prepareStatement("INSERT INTO testtimezone(seq,t) VALUES (?,?)");
int seq = 1;
Timestamp instant = new Timestamp(1104580800000L); // 2005-01-01 12:00:00 UTC
Timestamp instantTime = new Timestamp(instant.getTime() % DAY);
// +0100 (JVM default)
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTimestamp(2, instant); // 13:00:00
insertTimestamp.executeUpdate();
// UTC
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTimestamp(2, instant, cUTC); // 12:00:00
insertTimestamp.executeUpdate();
// +0300
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTimestamp(2, instant, cGMT03); // 15:00:00
insertTimestamp.executeUpdate();
// -0500
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTimestamp(2, instant, cGMT05); // 07:00:00
insertTimestamp.executeUpdate();
if (min73) {
// +1300
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTimestamp(2, instant, cGMT13); // 01:00:00
insertTimestamp.executeUpdate();
}
insertTimestamp.close();
checkDatabaseContents(
"SELECT seq::text,t::text from testtimezone ORDER BY seq",
new String[][] {
new String[] {"1", "13:00:00"},
new String[] {"2", "12:00:00"},
new String[] {"3", "15:00:00"},
new String[] {"4", "07:00:00"},
new String[] {"5", "01:00:00"}
});
seq = 1;
PreparedStatement ps = con.prepareStatement("SELECT seq,t FROM testtimezone ORDER BY seq");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(instantTime, rs.getTimestamp(2));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(instantTime, rs.getTimestamp(2, cUTC));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(instantTime, rs.getTimestamp(2, cGMT03));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(instantTime, rs.getTimestamp(2, cGMT05));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(normalizeTimeOfDayPart(instantTime, cGMT13), rs.getTimestamp(2, cGMT13));
assertTrue(!rs.next());
ps.close();
}
}
