public static List<PriceBar> getPriceDatas(
String sym,
java.util.Date beginDT,
java.util.Date endDT,
int priceMagnifier,
int multiplier) {
List<PriceBar> priceDatas = new ArrayList<PriceBar>();
try {
PreparedStatement datedRangeBySymbol =
DBopsMySql.datedRangeBySymbol(
sym, new Timestamp(beginDT.getTime()), new Timestamp(endDT.getTime()));
ResultSet res = datedRangeBySymbol.executeQuery();
while (res.next()) {
PriceBar priceBar =
new PriceBar(
res.getTimestamp("datetime").getTime(),
res.getDouble("open") / priceMagnifier * multiplier,
res.getDouble("high") / priceMagnifier * multiplier,
res.getDouble("low") / priceMagnifier * multiplier,
res.getDouble("close") / priceMagnifier * multiplier,
res.getLong("volume"));
priceDatas.add(priceBar);
}
// int i = 1;
} catch (SQLException ex) {
MsgBox.err2(ex);
} catch (Exception ex) {
MsgBox.err2(ex);
} finally {
return priceDatas;
}
}
// ---------------------------------------------------------------//
public void addTasktoProject(int projectid, Task task, Connection conn) throws SQLException {
PreparedStatement prepStmt = null;
java.util.Date date = new java.util.Date();
Timestamp currentdate = new Timestamp(date.getTime());
conn = select();
String sql =
"INSERT INTO TASKS(TASK_ID,PROJ_ID,TASK_DESCRIPTION,TASK_NOTES,TASK_DEADLINE,TASK_FROM,TASK_TO,TASK_ACTIVE,TASK_TYPE,TASK_USER_NOTES,ROWVERSION,INSERTED_AT,INSERTED_BY,MODIFIED_AT,MODIFIED_BY)"
+ " VALUES(PROJ_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
prepStmt = conn.prepareStatement(sql);
prepStmt.setInt(1, projectid);
prepStmt.setString(2, task.getTask_DESC());
prepStmt.setString(3, task.getTask_NOTES());
prepStmt.setDate(4, task.getTask_DEADLINE());
prepStmt.setDate(5, task.getTask_STARDATE());
prepStmt.setDate(6, task.getTask_ENDDATE());
if (task.isTask_ACTIVE()) prepStmt.setString(7, "Y");
else prepStmt.setString(7, "N");
prepStmt.setString(8, task.getTask_Type());
prepStmt.setString(9, task.getTask_USERNOTES());
prepStmt.setInt(10, 1);
if (task.getTask_INSERTEDAT() != null) prepStmt.setTimestamp(11, currentdate);
else prepStmt.setDate(11, null);
prepStmt.setString(12, "Grigoris");
if (task.getTask_MODIFIEDAT() != null) prepStmt.setTimestamp(13, currentdate);
else prepStmt.setDate(13, null);
if (task.getTask_MODIFIEDAT() != null) prepStmt.setString(14, "Grigoris");
else prepStmt.setString(14, "Grigoris");
prepStmt.executeUpdate();
return;
}
@Override
public String executeSqlQuery(String json) {
System.out.println("obj json " + json);
String response = null;
String sqlCommand = null;
JSONObject obj = (JSONObject) JSONValue.parse(json);
Connection connect = null;
try {
connect = SqlQueries.getConnection();
} catch (SQLException e) {
}
Statement statement = null;
// ResultSet rset = null;
JSONObject object = new JSONObject();
java.util.Date javaDate = new java.util.Date();
long javaTime = javaDate.getTime();
java.sql.Date sqldate = new java.sql.Date(javaTime);
java.sql.Time sqltime = new java.sql.Time(javaTime);
sqlCommand =
"INSERT INTO userinfos "
+ "(reportdate, reporttime, patientname, measuredarm, position, systole, diastole,"
+ " heartrate, bodytemp) VALUES('"
+ sqldate
+ "', '"
+ sqltime
+ "','"
+ obj.get("patientname").toString()
+ "','"
+ obj.get("arm").toString()
+ "','"
+ obj.get("position").toString()
+ "',"
+ obj.get("systole")
+ ","
+ obj.get("diastole")
+ ","
+ obj.get("heartRate")
+ ","
+ obj.get("bodyTemp")
+ ");";
System.out.println(sqlCommand);
try {
statement = connect.createStatement();
statement.executeUpdate(sqlCommand);
} catch (SQLException e) {
}
object.put("status", true);
System.out.println("object : " + object);
response = object.toString();
return response;
}
// ---------------------------------------------------------------//
public void updateTasktoProject(Task task, Connection conn) throws SQLException {
PreparedStatement prepStmt = null;
java.util.Date date = new java.util.Date();
Timestamp currentdate = new Timestamp(date.getTime());
conn = select();
String sql =
"UPDATE TASKS SET TASK_DESCRIPTION=?,TASK_NOTES=?,TASK_DEADLINE=?,TASK_FROM=?,TASK_TO=?,TASK_ACTIVE=?,TASK_TYPE=?,TASK_USER_NOTES=?,ROWVERSION=+ROWVERSION+1,INSERTED_AT=?,INSERTED_BY=?,MODIFIED_AT=?,MODIFIED_BY=?"
+ " WHERE TASK_ID=?";
prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, task.getTask_DESC());
prepStmt.setString(2, task.getTask_NOTES());
prepStmt.setDate(3, task.getTask_DEADLINE());
prepStmt.setDate(4, task.getTask_STARDATE());
prepStmt.setDate(5, task.getTask_ENDDATE());
if (task.isTask_ACTIVE()) prepStmt.setString(6, "Y");
else prepStmt.setString(6, "N");
prepStmt.setString(7, task.getTask_Type());
prepStmt.setString(8, task.getTask_USERNOTES());
if (task.getTask_INSERTEDAT() != null) prepStmt.setTimestamp(9, currentdate);
else prepStmt.setDate(9, null);
prepStmt.setString(10, "Grigoris");
if (task.getTask_MODIFIEDAT() != null) prepStmt.setTimestamp(11, currentdate);
else prepStmt.setDate(11, null);
prepStmt.setString(12, "Grigoris");
prepStmt.setInt(13, task.getTask_ID());
prepStmt.executeUpdate();
return;
}
// Hakee ja palauttaa tämänhetkisen ajan, eli pelkän ajan, ei pvm
public java.sql.Time haeAika() {
java.util.Date a = new java.util.Date();
long aikaL = a.getTime();
java.sql.Time aika = new java.sql.Time(aikaL);
return aika;
}
public Timestamp generateTimeStamp() {
// 1) create a java calendar instance
Calendar calendar = Calendar.getInstance();
// 2) get a java.util.Date from the calendar instance.
// this date will represent the current instant, or "now".
java.util.Date now = calendar.getTime();
// 3) a java current time (now) instance
return new java.sql.Timestamp(now.getTime());
}
// ambil id dan Hipotesis
private void prosesH() {
ResultSet rs = null;
try {
// pSel.setString(1,"1");
rs = pSel.executeQuery();
String h = "";
int id = -1;
while (rs.next()) { // pasti dapat 1
id = rs.getInt(1);
h = rs.getString(2);
System.out.println(id + ":" + h);
}
if (h.equals("")) {
// nggak boleh kosong!
System.out.println("Error: h kosong");
return;
}
// konversi H ke bentuk valid URL
// contoh,
// input:
// Capt. Scott reached Scott Island in December 1902 -.edu -nltk -linguistics -nlp -github
// -entailment
// output:
// Capt.+Scott+reached+Scott+Island+in+December+1902++-.edu+-nltk+-linguistics+-nlp+-github+-entailment&oq=Capt.+Scott+reached+Scott+Island+in+December+1902++-.edu+-nltk+-linguistics+-nlp+-github+-entailment
String parH = java.net.URLEncoder.encode(h, "utf-8");
// panggil API google, hasilnya simpan
String hasilSE = urlReader(parH);
// System.out.println("hasil crawl:"+hasilSE);
// save ke DB
// "update rte3_babak2 " +
// "set hasilsearch=?,
// isproses=1,
// timestamp=? where id=?");
java.util.Date date = new Date();
Timestamp timestamp = new Timestamp(date.getTime());
pIns.setString(1, hasilSE);
pIns.setTimestamp(2, timestamp);
pIns.setInt(3, id);
pIns.execute();
rs.close();
// koneksi ditutup di method close()
} catch (Exception e) {
e.printStackTrace();
}
}
public void register(String email, String password, String username) throws SQLException {
Statement stmnt = conn.createStatement();
java.util.Date d = new java.util.Date();
Timestamp ts = new Timestamp(d.getTime());
String created_at = ts.toString();
String updated_at = created_at;
String sql =
"INSERT INTO members (email,encrypted_password,username,created_at,updated_at) VALUES('"
+ email
+ "','"
+ BCrypt.hashpw(password, BCrypt.gensalt())
+ "','"
+ username
+ "','"
+ created_at
+ "','"
+ updated_at
+ "')";
stmnt.execute(sql);
}
public static void addToResultsFile(
String executingDatabaseName,
long runTime,
long rowsLoaded,
long rowsLoadedPerSecond,
File resultsFile) {
boolean resultsFileCreated = false;
try {
if (!resultsFile.exists()) {
resultsFileCreated = resultsFile.createNewFile();
}
OutputStream resultsFileStream = new FileOutputStream(resultsFile, true);
if (resultsFileCreated) {
// Write header lines.
String header =
"timeOfTest, runTime, databaseBeingTested, rowsLoaded, rowsLoadedPerSecond, hotStart, config, replicas"
+ "\n";
resultsFileStream.write(header.getBytes());
}
String results =
startDate.getTime()
+ ", "
+ runTime
+ ", "
+ executingDatabaseName
+ ", "
+ rowsLoaded
+ ", "
+ rowsLoadedPerSecond
+ ", "
+ hotStart
+ ", "
+ configInfo
+ ", "
+ numberOfReplicas
+ "\n";
resultsFileStream.write(results.getBytes());
resultsFileStream.flush();
resultsFileStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
// ---------------------------------------------------------------//
public int editProject(Project project, Connection conn) throws SQLException {
PreparedStatement prepStmt = null;
int rtrn = -1;
java.util.Date date = new java.util.Date();
Timestamp currentdate = new Timestamp(date.getTime());
conn = select();
String sql =
"UPDATE PROJECTS SET PROJ_NAME=?,MODIFIED_AT=?,MODIFIED_BY=?,PROJ_ACTIVE=?,PROJ_BUDGET=?,PROJ_DEADLINE=?,PROJ_DESCRIPTION=?,PROJ_FROM=?,PROJ_TYPE=?,PROJ_TO=?,CUST_ID=?,ROWVERSION=ROWVERSION+1"
+ "WHERE PROJ_ID=?";
prepStmt = conn.prepareStatement(sql);
prepStmt.setString(1, project.getName());
prepStmt.setTimestamp(2, currentdate);
prepStmt.setString(3, project.getModified_by());
if (project.isActive()) prepStmt.setString(4, "Y");
else prepStmt.setString(4, "N");
if (project.getBudget() != -1) prepStmt.setFloat(5, project.getBudget());
else prepStmt.setInt(5, 0);
if (project.getNextDeadline() == null) prepStmt.setDate(6, null);
else prepStmt.setDate(6, new java.sql.Date(project.getNextDeadline().getTime()));
prepStmt.setString(7, project.getDescription());
prepStmt.setDate(8, new java.sql.Date(project.getStartDate().getTime()));
prepStmt.setString(9, project.getProjectType());
if (project.getEndDate() == null) prepStmt.setDate(10, null);
else prepStmt.setDate(10, new java.sql.Date(project.getEndDate().getTime()));
prepStmt.setInt(11, project.getCustomerID());
prepStmt.setInt(12, project.getProjectID());
prepStmt.executeUpdate();
rtrn = project.getProjectID();
return rtrn;
}
public static Anotacao inserir(Anotacao a) {
Anotacao anot = null;
// Pegar data atual
java.util.Date today = new java.util.Date();
java.sql.Date date = new java.sql.Date(today.getTime());
try {
// Criação do insert
String sql =
"INSERT INTO anotacao(id_termo, termo_papel, id_classe, classe_papel, linha_termo, id_usuario, data_anot, propriedade) "
+ "VALUES(?,?,?,?,?,?,?,?)";
Conexao conex =
new Conexao(
"jdbc:postgresql://localhost:5432/teste",
"org.postgresql.Driver",
"postgres",
"123456");
Connection con = conex.obterConexao();
PreparedStatement comando = con.prepareStatement(sql);
comando.setInt(1, a.getTermo());
comando.setString(2, a.getTermo_papel());
comando.setInt(3, a.getClasse());
comando.setString(4, a.getClasse_papel());
comando.setString(5, a.getLinha());
comando.setInt(6, a.getUsuario().getId());
comando.setDate(7, date);
comando.setString(8, a.getPropriedade());
comando.executeUpdate();
} catch (Exception e) {
System.out.println(e.getMessage());
}
anot =
new Anotacao(
a.getTermo(),
a.getTermo_papel(),
a.getClasse(),
a.getClasse_papel(),
a.getLinha(),
a.getUsuario(),
a.getPropriedade());
return anot;
}
public Query addParameter(String name, java.util.Date value) {
Date sqlDate = value == null ? null : new Date(value.getTime());
if (sqlDate != null && this.connection.getSql2o().quirksMode == QuirksMode.DB2) {
// With the DB2 driver you can get an error if trying to put a date value into a timestamp
// column,
// but of some reason it works if using setObject().
return addParameter(name, (Object) sqlDate);
} else {
return addParameter(name, sqlDate);
}
}
// ---------------------------------------------------------------//
public int insertProject(Project project, Connection conn) throws SQLException {
PreparedStatement prepStmt = null;
int rtrn = -1;
java.util.Date date = new java.util.Date();
Timestamp currentdate = new Timestamp(date.getTime());
conn = select();
String sql =
"INSERT INTO PROJECTS(PROJ_ID,PROJ_NAME,INSERTED_AT,INSERTED_BY,MODIFIED_AT,MODIFIED_BY,PROJ_ACTIVE,PROJ_BUDGET,PROJ_DEADLINE,PROJ_DESCRIPTION,PROJ_FROM,PROJ_TYPE,PROJ_TO,CUST_ID,ROWVERSION)"
+ " VALUES(PROJ_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
String cols[] = {"PROJ_ID"};
prepStmt = conn.prepareStatement(sql, cols);
prepStmt.setString(1, project.getName());
prepStmt.setTimestamp(2, currentdate);
prepStmt.setString(3, project.getInserted_by());
prepStmt.setDate(4, null);
prepStmt.setString(5, null);
if (project.isActive()) prepStmt.setString(6, "Y");
else prepStmt.setString(6, "N");
if (project.getBudget() != -1) prepStmt.setFloat(7, project.getBudget());
else prepStmt.setInt(7, 0);
if (project.getNextDeadline() == null) prepStmt.setDate(8, null);
else prepStmt.setDate(8, new java.sql.Date(project.getNextDeadline().getTime()));
prepStmt.setString(9, project.getDescription());
prepStmt.setDate(10, new java.sql.Date(project.getStartDate().getTime()));
prepStmt.setString(11, project.getProjectType());
if (project.getEndDate() == null) prepStmt.setDate(12, null);
else prepStmt.setDate(12, new java.sql.Date(project.getEndDate().getTime()));
prepStmt.setInt(13, project.getCustomerID());
prepStmt.setInt(14, project.getRowversion());
prepStmt.executeUpdate();
ResultSet rs = prepStmt.getGeneratedKeys();
if (rs.next()) {
rtrn = rs.getInt(1);
}
rs.close();
return rtrn;
}
public ByteArrayOutputStream getReportCode() {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
Document document = new Document(PageSize.B4, 40, 200, 120, 20);
try {
PdfWriter writer = PdfWriter.getInstance(document, baos);
MyPageEvents events = new MyPageEvents();
writer.setPageEvent(events);
document.open();
if (data.size() == 0) {
PdfPTable table = new PdfPTable(1);
table.addCell(printStr("", 6, 8, false, PdfPCell.NO_BORDER, 0));
document.add(table);
}
PdfPTable table = new PdfPTable(1);
table.setWidthPercentage(100);
int border = 0;
// Receipt Infos
String row[] = (String[]) data.get(0);
table.addCell(printStr("", 1, 18, false, border, 1));
table.addCell(printStr(row[17], 1, 22, false, border, 1));
table.addCell(printStr(" ", 1, 18, false, border, 1));
table.addCell(printStr(" ", 1, 18, false, border, 1));
table.addCell(printStr("Name: " + row[0], 1, 22, false, border, 0));
table.addCell(printStr(" " + row[13], 1, 22, false, border, 0));
table.addCell(printStr("Sex: " + row[14], 1, 22, false, border, 0));
table.addCell(printStr("DOB: " + row[15], 1, 22, false, border, 0));
if (row[12].equals("A"))
table.addCell(
printStr(
"Nationality: " + GeneralDataInHtml.getCountryName(row[2]),
1,
22,
false,
border,
0));
table.addCell(printStr("PPT/Ref. No.: " + row[1], 1, 22, false, border, 0));
table.addCell(printStr(" ", 1, 18, false, border, 1));
table.addCell(printStr(" ", 1, 18, false, border, 1));
double cashTotal = 0;
double chequeTotal = 0;
table.addCell(printStr(row[3] + " x " + row[4] + " @$" + row[5], 1, 22, false, border, 0));
for (int i = 1; i < data.size(); i++) {
String item[] = (String[]) data.get(i);
table.addCell(
printStr(item[3] + " x " + item[4] + " @$" + item[5], 1, 22, false, border, 0));
}
table.addCell(printStr(" ", 1, 18, false, border, 1));
table.addCell(printStr(" ", 1, 18, false, border, 1));
double amt = Double.parseDouble(row[9]);
if (row[7].equals("cash")) {
table.addCell(printStr("Cash Total: HK$" + amt + "0", 1, 22, false, border, 0));
} else {
table.addCell(printStr("Cheque: HK$" + amt + "0", 1, 22, false, border, 0));
table.addCell(printStr("--" + row[8], 1, 22, false, border, 0));
}
table.addCell(printStr(" ", 1, 18, false, border, 1));
// Count Collection Date
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
int dayAdd = 2;
int dayCount = 2;
// int receiptDate = Integer.parseInt(row[18]);
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
java.util.Date rDate = sdf.parse(row[18]);
java.sql.Timestamp receiptDate = new java.sql.Timestamp(rDate.getTime());
// 86400000 milliseconds in a day
long oneDay = 1 * 24 * 60 * 60 * 1000;
try {
conn = JdbcConnection.getConnection();
for (int k = 1; k <= dayCount; k++) {
receiptDate.setTime(receiptDate.getTime() + oneDay);
// System.out.println("select 1 from holiday where date = "+(receiptDate));
ps = conn.prepareStatement("select 1 from holiday where date = ?");
ps.setTimestamp(1, receiptDate);
rs = ps.executeQuery();
if (rs.next()) {
dayCount += 1;
dayAdd += 1;
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null)
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
if (ps != null)
try {
ps.close();
} catch (Exception e) {
e.printStackTrace();
}
if (conn != null)
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Calendar cal = Calendar.getInstance();
cal.set(
Integer.parseInt(row[18].substring(0, 4)),
Integer.parseInt(row[18].substring(4, 6)) - 1,
Integer.parseInt(row[18].substring(6)));
cal.add(cal.DATE, dayAdd);
Date date = cal.getTime();
SimpleDateFormat df = new SimpleDateFormat("dd-MMM-yyyy (EEE)", Locale.US);
table.addCell(printStr(" ", 1, 18, false, border, 1));
if (row[12].equals("A")) {
table.addCell(printStr("Collection Date: " + df.format(date), 1, 22, false, border, 1));
table.addCell(printStr("Collection Time: 4:00pm to 5:00pm", 1, 22, false, border, 1));
table.addCell(printStr(" ", 1, 22, false, border, 1));
table.addCell(printStr(row[10] + " " + row[16], 1, 22, false, border, 1));
table.addCell(printStr(" ", 1, 22, false, border, 1));
table.addCell(
printStr(
"APPLICATION IS SUBJECTED TO APPROVAL, PLEASE BRING THIS RECEIPT ON COLLECTION DAY",
1,
22,
false,
border,
1));
table.addCell(printStr(row[11], 1, 32, false, border, 1));
table.addCell(printStr("(" + this.params + ")", 1, 22, false, border, 2));
} else {
table.addCell(printStr("THANK YOU", 1, 22, false, border, 1));
table.addCell(printStr(row[10] + " " + row[16], 1, 22, false, border, 1));
table.addCell(printStr(" ", 1, 22, false, border, 1));
table.addCell(printStr(row[11], 1, 22, false, border, 1));
table.addCell(printStr("(" + this.params + ")", 1, 22, false, border, 2));
}
float[] widths4 = {10};
table.setWidths(widths4);
document.add(table);
document.close();
} catch (Exception e) {
e.printStackTrace();
}
return baos;
}
public static int searchForTwits(int id, String text, Connection conn, String getDate)
throws TwitterException, SQLException, LangDetectException, ParseException {
Twitter twitter = new TwitterFactory().getInstance();
int countTweets = 0;
int pageNumber = 1;
int n = 0;
do {
Query query = new Query(text).rpp(100).page(pageNumber);
QueryResult result = twitter.search(query);
for (Tweet tweet : result.getTweets()) {
java.util.Date date = tweet.getCreatedAt();
Format formatter;
formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String newDate = formatter.format(date);
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
java.util.Date oldDate = df.parse(getDate);
Statement input = conn.createStatement();
countTweets++;
if (date.after(oldDate)) {
Detector detector = DetectorFactory.create();
detector.append(tweet.getText());
String lang;
try {
lang = detector.detect();
if (lang.equals("lv") || lang.equals("ru")) {
try {
input.executeUpdate(
"INSERT INTO tweet "
+ "VALUES (null, '"
+ tweet.getId()
+ "', '"
+ tweet.getFromUser()
+ "', '"
+ tweet.getText().replace("'", "’")
+ "', '"
+ newDate
+ "', null, null, null)");
input.executeUpdate(
"INSERT INTO tweet_brand "
+ "VALUES (null, '"
+ tweet.getId()
+ "', '"
+ id
+ "')");
} catch (SQLException ex) {
}
n++;
} else continue;
} catch (LangDetectException ex) {
}
} else continue;
}
pageNumber++;
if (countTweets == 100) countTweets = 0;
else break;
} while (pageNumber != 16);
System.out.print(text + " ");
return n;
}
void runProcTest() {
setProgressMinMax(0, m_nNumRuns - 1);
int nProgressStep = m_nNumRuns / 10 + 1;
CallableStatement stmt = null;
try {
boolean m_bTransactionsUsed = m_bTrans && m_conn.getMetaData().supportsTransactions();
stmt = m_conn.prepareCall("{call ODBC_BENCHMARK(?,?,?,?,?,?,?)}");
int nAccNum, nBranchNum, nTellerNum;
double dDelta, dBalance;
log(
"Starting procedure benchmark for " + m_nNumRuns + ((m_time > 0) ? " min.\n" : " runs\n"),
0);
java.util.Random rand = new java.util.Random(m_nMaxAccount + m_nMaxBranch + m_nMaxTeller);
for (int nRun = 0; (m_time > 0) ? true : nRun < m_nNumRuns; nRun++) {
if (m_time > 0) {
java.util.Date current = new java.util.Date();
if ((current.getTime() - m_time) > (m_nNumRuns * 60000)) break;
}
try {
nAccNum = (int) (rand.nextFloat() * rand.nextFloat() * (m_nMaxAccount - 1)) + 1;
nBranchNum = (int) (rand.nextFloat() * (m_nMaxBranch - 1)) + 1;
nTellerNum = (int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1;
dDelta =
((double)
((long)
((((int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1)
* (rand.nextFloat() > 0.5 ? -1 : 1))
* 100)))
/ 100;
stmt.clearParameters();
stmt.setInt(1, nRun + 1);
stmt.setInt(2, nAccNum);
stmt.setInt(3, nTellerNum);
stmt.setInt(4, nBranchNum);
stmt.setFloat(5, (float) dDelta);
stmt.registerOutParameter(6, Types.FLOAT);
stmt.setString(7, BenchPanel.strFiller.substring(0, 22));
java.util.Date startTime = new java.util.Date();
log(
"{call ODBC_BENCHMARK("
+ nRun
+ ", "
+ nAccNum
+ ","
+ nTellerNum
+ ","
+ nBranchNum
+ ","
+ dDelta
+ ",?,\'"
+ BenchPanel.strFiller.substring(0, 22)
+ "\')}\n",
2);
stmt.execute();
stmt.getFloat(6);
if (m_bQuery) executeQuery();
java.util.Date endTime = new java.util.Date();
m_nTrans += 1;
double diff = endTime.getTime() - startTime.getTime();
if (diff < 1000) m_nTrans1Sec += 1;
else if (diff < 2000) m_nTrans2Sec += 1;
m_nTimeSum += diff / 1000;
} catch (SQLException e1) {
// System.err.println(e1.getMessage());
// e1.printStackTrace();
break;
}
if (nRun % nProgressStep == 0) setProgressValue(nRun);
// yield();
}
setProgressValue(m_nNumRuns - 1);
} catch (SQLException e) {
// JOptionPane.showMessageDialog(null, e.getMessage(), "SQL Error in proc test",
// JOptionPane.ERROR_MESSAGE);
log("SQLError in procedure test : " + e.getMessage(), 0);
} finally {
if (stmt != null)
try {
stmt.close();
} catch (SQLException e) {
}
stmt = null;
}
}
public void gather(
String dbase,
String table1,
String table2,
String table3,
String field1,
String field2,
String field3) {
this.dbase = dbase;
this.table1 = table1;
this.table2 = table2;
this.table3 = table3;
this.field1 = field1;
this.field2 = field2;
this.field3 = field3;
nseer_db db = new nseer_db(dbase);
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date now = new java.util.Date();
try {
String sql3 = "delete from " + table1 + "";
db.executeUpdate(sql3);
String sql = "select * from " + table2 + " where tag='1'";
rs = db.executeQuery(sql);
int i = 0;
while (rs.next()) {
idgroup[i] = rs.getString(field1);
alarmgroup[i] = rs.getString(field2);
i++;
}
for (int j = 0; j < i; j++) {
java.util.Date date1 = new java.util.Date();
long Time = (now.getTime() / 1000) - 60 * 60 * 24 * Integer.parseInt(alarmgroup[j]);
date1.setTime(Time * 1000);
String time = formatter.format(date1);
sql1 =
"select * from "
+ table3
+ " where "
+ field1
+ "='"
+ idgroup[j]
+ "'&&"
+ field3
+ "<='"
+ time
+ "'&&tag='0'";
rs1 = db.executeQuery(sql1);
while (rs1.next()) {
java.util.Date date2 = formatter.parse(rs1.getString(field3));
long days = (long) ((date1.getTime() - date2.getTime()) / (1000 * 60 * 60 * 24) + 0.5);
String sql2 =
"insert into "
+ table1
+ "("
+ field1
+ ",ordernum,sumtotal,invoicetime,"
+ field2
+ ",days) values('"
+ idgroup[j]
+ "','"
+ rs1.getString("ordernum")
+ "','"
+ rs1.getString("sumtotal")
+ "','"
+ rs1.getString(field3)
+ "','"
+ alarmgroup[j]
+ "','"
+ days
+ "')";
db.executeUpdate(sql2);
}
}
db.close();
} catch (Exception ex) {
}
}
void runPrepareTest() {
setProgressMinMax(0, m_nNumRuns - 1);
int nProgressStep = m_nNumRuns / 10 + 1;
try {
boolean m_bTransactionsUsed = m_bTrans && m_conn.getMetaData().supportsTransactions();
boolean bCurrentAutoCommit = m_conn.getAutoCommit();
m_conn.setAutoCommit(!m_bTransactionsUsed);
int nAccNum, nBranchNum, nTellerNum;
double dDelta, dBalance;
log(
"Starting SQL prepare/execute benchmark for "
+ m_nNumRuns
+ ((m_time > 0) ? " min.\n" : " runs\n"),
0);
java.util.Random rand = new java.util.Random(m_nMaxAccount + m_nMaxBranch + m_nMaxTeller);
for (int nRun = 0; (m_time > 0) ? true : nRun < m_nNumRuns; nRun++) {
if (m_time > 0) {
java.util.Date current = new java.util.Date();
if ((current.getTime() - m_time) > (m_nNumRuns * 60000)) break;
}
PreparedStatement updAccStmt = null,
selAccStmt = null,
updTellerStmt = null,
updBranchStmt = null,
insHistStmt = null;
try {
nAccNum = (int) (rand.nextFloat() * rand.nextFloat() * (m_nMaxAccount - 1)) + 1;
nBranchNum = (int) (rand.nextFloat() * (m_nMaxBranch - 1)) + 1;
nTellerNum = (int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1;
dDelta =
((double)
((long)
((((int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1)
* (rand.nextFloat() > 0.5 ? -1 : 1))
* 100)))
/ 100;
// prepare statements
updAccStmt =
m_conn.prepareStatement(
"UPDATE "
+ m_Driver.getAccountName()
+ " SET balance = balance + ? WHERE account = ?");
selAccStmt =
m_conn.prepareStatement(
"SELECT balance FROM " + m_Driver.getAccountName() + " WHERE account = ?");
updTellerStmt =
m_conn.prepareStatement(
"UPDATE "
+ m_Driver.getTellerName()
+ " SET balance = balance + ? WHERE teller = ?");
updBranchStmt =
m_conn.prepareStatement(
"UPDATE "
+ m_Driver.getBranchName()
+ " SET balance = balance + ? WHERE branch = ?");
insHistStmt =
m_conn.prepareStatement(
"INSERT INTO "
+ m_Driver.getHistoryName()
+ " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES (? , ? , ? , ? , ? , "
+ m_nowFunction
+ " , ?)");
// bind parameters
updAccStmt.setDouble(1, dDelta);
updAccStmt.setInt(2, nAccNum);
// System.out.println(nAccNum);
selAccStmt.setInt(1, nAccNum);
updTellerStmt.setDouble(1, dDelta);
updTellerStmt.setInt(2, nTellerNum);
updBranchStmt.setDouble(1, dDelta);
updBranchStmt.setInt(2, nBranchNum);
insHistStmt.setInt(1, nRun);
insHistStmt.setInt(2, nAccNum);
insHistStmt.setInt(3, nTellerNum);
insHistStmt.setInt(4, nBranchNum);
insHistStmt.setDouble(5, dDelta);
insHistStmt.setString(6, BenchPanel.strFiller.substring(0, 21));
java.util.Date startTime = new java.util.Date();
// execute statements
log(
"UPDATE "
+ m_Driver.getAccountName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE account = "
+ nAccNum
+ "\n",
2);
updAccStmt.executeUpdate();
log(
"SELECT balance FROM "
+ m_Driver.getAccountName()
+ " WHERE account = "
+ nAccNum
+ "\n",
2);
ResultSet balanceSet = selAccStmt.executeQuery();
balanceSet.next();
dBalance = balanceSet.getFloat(1);
// if (balanceSet == null)
// System.out.println("balanceSet is NULL");
// else {
// String strBalance = balanceSet.getString(1);
// System.out.println(balanceSet.wasNull() ? "SQL NULL" : strBalance);
// }
balanceSet.close();
log(
"UPDATE "
+ m_Driver.getTellerName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE teller = "
+ nTellerNum
+ "\n",
2);
updTellerStmt.executeUpdate();
log(
"UPDATE "
+ m_Driver.getBranchName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE branch = "
+ nBranchNum
+ "\n",
2);
updBranchStmt.executeUpdate();
log(
"INSERT INTO "
+ m_Driver.getHistoryName()
+ " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES ("
+ nRun
+ " , "
+ nAccNum
+ " , "
+ nTellerNum
+ " , "
+ nBranchNum
+ " , "
+ dDelta
+ " , "
+ m_nowFunction
+ " , \'"
+ BenchPanel.strFiller.substring(0, 21)
+ "\')\n",
2);
insHistStmt.executeUpdate();
if (m_bQuery) executeQuery();
if (m_bTransactionsUsed) m_conn.commit();
java.util.Date endTime = new java.util.Date();
m_nTrans += 1;
double diff = endTime.getTime() - startTime.getTime();
if (diff < 1000) m_nTrans1Sec += 1;
else if (diff < 2000) m_nTrans2Sec += 1;
m_nTimeSum += diff / 1000;
} catch (SQLException e1) {
// System.err.println(e1.getMessage());
break;
} finally {
try {
if (updAccStmt != null) updAccStmt.close();
if (selAccStmt != null) selAccStmt.close();
if (updTellerStmt != null) updTellerStmt.close();
if (updBranchStmt != null) updBranchStmt.close();
if (insHistStmt != null) insHistStmt.close();
} catch (SQLException e) {
}
}
if (nRun % nProgressStep == 0) setProgressValue(nRun);
// yield();
}
setProgressValue(m_nNumRuns - 1);
m_conn.setAutoCommit(bCurrentAutoCommit);
} catch (SQLException e) {
// JOptionPane.showMessageDialog(null, e.getMessage(), "SQL Error in Text test",
// JOptionPane.ERROR_MESSAGE);
log("SQLError in prepare test : " + e.getMessage(), 0);
}
}
void runTextTest() {
Statement stmt = null;
try {
// System.out.println("Thread :" + getName() + " Text test entered");
setProgressMinMax(0, m_nNumRuns - 1);
int nProgressStep = m_nNumRuns / 10 + 1;
// System.out.println("Thread :" + getName() + " before commit state");
boolean m_bTransactionsUsed = m_bTrans && m_conn.getMetaData().supportsTransactions();
// System.out.println("Thread :" + getName() + " are transactions used");
boolean bCurrentAutoCommit = m_conn.getAutoCommit();
// System.out.println("Thread :" + getName() + "got currentCommit");
m_conn.setAutoCommit(!m_bTransactionsUsed);
// System.out.println("Thread :" + getName() + " commit state set");
stmt = m_conn.createStatement();
int nAccNum, nBranchNum, nTellerNum;
double dDelta, dBalance;
log(
"Starting SQL text benchmark for " + m_nNumRuns + ((m_time > 0) ? " min.\n" : " runs\n"),
0);
java.util.Random rand = new java.util.Random(m_nMaxAccount + m_nMaxBranch + m_nMaxTeller);
for (int nRun = 0; (m_time > 0) ? true : nRun < m_nNumRuns; nRun++) {
if (m_time > 0) {
java.util.Date current = new java.util.Date();
if ((current.getTime() - m_time) > (m_nNumRuns * 60000)) break;
}
try {
nAccNum = (int) (rand.nextFloat() * rand.nextFloat() * (m_nMaxAccount - 1)) + 1;
nBranchNum = (int) (rand.nextFloat() * (m_nMaxBranch - 1)) + 1;
nTellerNum = (int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1;
dDelta =
((double)
((long)
((((int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1)
* (rand.nextFloat() > 0.5 ? -1 : 1))
* 100)))
/ 100;
java.util.Date startTime = new java.util.Date();
log(
"UPDATE "
+ m_Driver.getAccountName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE account = "
+ nAccNum
+ "\n",
2);
stmt.executeUpdate(
"UPDATE "
+ m_Driver.getAccountName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE account = "
+ nAccNum);
log(
"SELECT balance FROM "
+ m_Driver.getAccountName()
+ " WHERE account = "
+ nAccNum
+ "\n",
2);
ResultSet balanceSet =
stmt.executeQuery(
"SELECT balance FROM "
+ m_Driver.getAccountName()
+ " WHERE account = "
+ nAccNum);
balanceSet.next();
dBalance = balanceSet.getDouble(1);
balanceSet.close();
log(
"UPDATE "
+ m_Driver.getTellerName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE teller = "
+ nTellerNum
+ "\n",
2);
stmt.executeUpdate(
"UPDATE "
+ m_Driver.getTellerName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE teller = "
+ nTellerNum);
log(
"UPDATE "
+ m_Driver.getBranchName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE branch = "
+ nBranchNum
+ "\n",
2);
stmt.executeUpdate(
"UPDATE "
+ m_Driver.getBranchName()
+ " SET balance = balance + "
+ dDelta
+ " WHERE branch = "
+ nBranchNum);
log(
"INSERT INTO "
+ m_Driver.getHistoryName()
+ " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES ("
+ nRun
+ " , "
+ nAccNum
+ " , "
+ nTellerNum
+ " , "
+ nBranchNum
+ " , "
+ dDelta
+ " , "
+ m_nowFunction
+ " , \'"
+ BenchPanel.strFiller.substring(0, 21)
+ "\')\n",
2);
stmt.executeUpdate(
"INSERT INTO "
+ m_Driver.getHistoryName()
+ " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES ("
+ nRun
+ " , "
+ nAccNum
+ " , "
+ nTellerNum
+ " , "
+ nBranchNum
+ " , "
+ dDelta
+ " , "
+ m_nowFunction
+ " , \'"
+ BenchPanel.strFiller.substring(0, 21)
+ "\')");
if (m_bQuery) executeQuery();
// System.out.println("Done query");
if (m_bTransactionsUsed) m_conn.commit();
java.util.Date endTime = new java.util.Date();
// System.out.println("Done");
m_nTrans += 1;
long diff = endTime.getTime() - startTime.getTime();
if (diff < 1000) m_nTrans1Sec += 1;
else if (diff < 2000) m_nTrans2Sec += 1;
m_nTimeSum += ((double) diff) / 1000;
} catch (SQLException e1) {
// System.err.println(e1.getMessage());
// e1.printStackTrace();
break;
}
if (nRun % nProgressStep == 0) setProgressValue(nRun);
// yield();
}
setProgressValue(m_nNumRuns - 1);
m_conn.setAutoCommit(bCurrentAutoCommit);
} catch (SQLException e) {
// e.printStackTrace();
// JOptionPane.showMessageDialog(null, e.getMessage(), "SQL Error in Text test",
// JOptionPane.ERROR_MESSAGE);
log("SQLError in text test : " + e.getMessage(), 0);
} finally {
if (stmt != null)
try {
stmt.close();
} catch (SQLException e) {
}
}
}
public boolean post(int id, String message) {
PreparedStatement stmt = null;
ResultSet rs = null;
Connection con = null;
// 1) create a java calendar instance
Calendar calendar = Calendar.getInstance();
// 2) get a java.util.Date from the calendar instance.
// this date will represent the current instant, or "now".
java.util.Date now = calendar.getTime();
// 3) a java current time (now) instance
java.sql.Timestamp currentTimestamp = new java.sql.Timestamp(now.getTime());
try {
// Get the connection to the database
con = getConnection();
// Execute the query
stmt = con.prepareStatement("insert into lab1 (id, message, time)" + " values(?, ?, ? )");
stmt.setInt(1, id);
rs = stmt.executeQuery();
while (rs.next()) {}
return true;
} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
} finally {
// it is a good idea to release
// resources in a finally{} block
// in reverse-order of their creation
// if they are no-longer needed
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) {
} // ignore
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) {
} // ignore
stmt = null;
}
if (con != null) {
try {
con.close();
} catch (SQLException sqlEx) {
} // ignore
con = null;
}
}
return false;
}
private static java.sql.Timestamp getCurrentTimeStamp() {
java.util.Date today = new java.util.Date();
return new java.sql.Timestamp(today.getTime());
}
protected java.sql.Date convertToSqlDate(java.util.Date date) {
return new java.sql.Date(date.getTime());
}
