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 static void main(String[] args) {
try {
Connection con = DBopsMySql.setuptradesConnection();
con.setAutoCommit(false);
String ul = "AUD";
PreparedStatement pstmt = DBopsMySql.getExpirysForUpdate(con, ul, 20090300, 20100700);
PreparedStatement upDateStmt = DBopsMySql.updateBeginEndDatesForExpiry(con);
ResultSet res = pstmt.executeQuery();
res.next(); // To get a lastexpiry for loop, so should be one extra early expiry
int lastexp = res.getInt("expiry");
while (res.next()) {
int exp = res.getInt("expiry");
String bdate = DateOps.dbShortFormatString(lastexp - 5);
String edate = DateOps.dbShortFormatString(exp - 6);
upDateStmt.setString(1, bdate);
if (!res.isLast()) {
upDateStmt.setString(2, edate);
}
upDateStmt.setString(3, ul);
upDateStmt.setInt(4, exp);
upDateStmt.addBatch();
lastexp = exp;
}
int[] updateCounts = upDateStmt.executeBatch();
upDateStmt.close();
con.close();
} catch (SQLException ex) {
MsgBox.err2(ex);
}
}
public static String createCompressionTable(int compressionFactor) {
String dbTableName = "quotes" + compressionFactor + "min";
try {
PreparedStatement createCompressionTable =
DBopsMySql.setuptradesConnection()
.prepareStatement(
"CREATE TABLE IF NOT EXISTS `Trading`.`"
+ dbTableName
+ "` ("
+ "`symbol` VARCHAR( 15 ) NOT NULL , "
+ "`datetime` DATETIME NOT NULL , "
+ "`open` DOUBLE NOT NULL , "
+ "`high` DOUBLE NOT NULL , "
+ "`low` DOUBLE NOT NULL , "
+ "`close` DOUBLE NOT NULL , "
+ "`volume` BIGINT( 20 ) NOT NULL, "
+ "PRIMARY KEY(`symbol`, `datetime`))");
createCompressionTable.execute();
createCompressionTable.close();
} catch (SQLException ex) {
MsgBox.err2(ex);
dbTableName = null;
} finally {
return dbTableName;
}
}
public static CallableStatement distinctSymsProc() {
CallableStatement ret = null;
try {
ret = DBopsMySql.setuptradesConnection().prepareCall("CALL distinctQuoteSymbols()");
} catch (SQLException ex) {
MsgBox.err2(ex);
} finally {
return ret;
}
}
public static void insertIntoPaperTradesTable(PaperTrade paperTrade) {
PreparedStatement pstmt = null;
try {
pstmt =
DBopsMySql.setuptradesConnection()
.prepareStatement(
"INSERT INTO `PaperTrades` "
+ "(`id`, `EnteredInDB`, `BeginTradeDateTime`, `symbol`, `Position`, "
+ "`entry`, `stop loss`, `stop risk`, `Stop profit`, "
+ "`profitpotential`, `Outcome`, `ExitTradeDateTime`) "
+ "VALUES (NULL, CURRENT_TIMESTAMP, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
// BeginTradeDateTime
if (paperTrade.getBeginTradeDateTime() == null) {
pstmt.setNull(1, Types.DATE);
} else {
pstmt.setTimestamp(1, paperTrade.getBeginTradeTimestamp());
}
// symbol
if (paperTrade.getSymbol() == null) {
pstmt.setNull(2, Types.VARCHAR);
} else {
pstmt.setString(2, paperTrade.getSymbol());
}
// Position
if (paperTrade.getPosition() == null) {
pstmt.setNull(3, Types.VARCHAR);
} else {
pstmt.setString(3, paperTrade.getPosition());
}
// entry
pstmt.setDouble(4, paperTrade.getEntry());
// stop loss
pstmt.setDouble(5, paperTrade.getStopLoss());
// stop risk
pstmt.setDouble(6, paperTrade.getStopRisk());
// Stop profit
pstmt.setDouble(7, paperTrade.getProfitStop());
// profitpotential
pstmt.setDouble(8, paperTrade.getProfitpotential());
// Outcome
pstmt.setDouble(9, paperTrade.getOutcome());
// ExitTradeDateTime
pstmt.setTimestamp(10, paperTrade.getExitTradeTimestamp());
pstmt.execute();
pstmt.close();
} catch (SQLException ex) {
MsgBox.err2(ex);
} finally {
}
}
public static Connection setuptradesConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
tradesConnection =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/Trading", "rickcharon", "1plus7is8");
} catch (Exception ex) {
MsgBox.err2(ex);
} finally {
return tradesConnection;
}
}
public static void insertIntoPaperOrdersTable(Order order) {
PreparedStatement pstmt = null;
try {
pstmt =
DBopsMySql.setuptradesConnection()
. //// rpc - NOTE:8/15/10 11:35 AM - 22 PARAMS
prepareStatement(
"REPLACE INTO `PaperOrders` "
+ "(`idx`,`UL`, `Expiry`, `BuySell`, `TotalQuantity`, `FilledQuantity`, "
+ "`RemainingQuantity`, `LimitPrice`, `Auxprice`, `AvgFillPrice`, `OrderType`, "
+ "`TIF`, `TranslatedPrice`, `BarTime`, `LossOrGain`, `OCAGroup`, "
+ "`OcaType`, `OrderID`, `ParentID`, `PermID`, `EntryDateTime`, "
+ "`ExecutedDateTime`, `Status`)"
+ "VALUES (?, ? , ?, ?, ?, ?, ?, ?, ? , ?, ?,"
+ "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
// IDX
if (order.getIdx() == null) {
pstmt.setNull(1, Types.INTEGER);
} else {
pstmt.setInt(1, order.getIdx());
}
// UL
if (order.getUl() == null) {
pstmt.setNull(2, Types.VARCHAR);
} else {
pstmt.setString(2, order.getUl());
}
// Expiry
if (order.getExpiry() == null) {
pstmt.setNull(3, Types.INTEGER);
} else {
pstmt.setInt(3, order.getExpiry());
}
// BuySell
if (order.getExpiry() == null) {
pstmt.setNull(4, Types.VARCHAR);
} else {
pstmt.setString(4, order.getBuySell());
}
// TotalQuantity
if (order.getTotalQuantity() == null) {
pstmt.setNull(5, Types.INTEGER);
} else {
pstmt.setInt(5, order.getTotalQuantity());
}
// FilledQuantity
if (order.getFilledQuantity() == null) {
pstmt.setNull(6, Types.INTEGER);
} else {
pstmt.setInt(6, order.getFilledQuantity());
}
// RemainingQuantity
if (order.getRemainingQuantity() == null) {
pstmt.setNull(7, Types.INTEGER);
} else {
pstmt.setInt(7, order.getRemainingQuantity());
}
// LimitPrice
if (order.getLimitPrice() == null) {
pstmt.setNull(8, Types.DOUBLE);
} else {
pstmt.setDouble(8, order.getLimitPrice());
}
// AuxPrice
if (order.getAuxprice() == null) {
pstmt.setNull(9, Types.DOUBLE);
} else {
pstmt.setDouble(9, order.getAuxprice());
}
// AvgFillPrice
if (order.getAvgFillPrice() == null) {
pstmt.setNull(10, Types.DOUBLE);
} else {
pstmt.setDouble(10, order.getAvgFillPrice());
}
// OrderType
if (order.getOrderType() == null) {
pstmt.setNull(11, Types.VARCHAR);
} else {
pstmt.setString(11, order.getOrderType());
}
// TIF
if (order.getTif() == null) {
pstmt.setNull(12, Types.VARCHAR);
} else {
pstmt.setString(12, order.getTif());
}
// TranslatedPrice
if (order.getTranslatedPrice() == null) {
pstmt.setNull(13, Types.DOUBLE);
} else {
pstmt.setDouble(13, order.getTranslatedPrice());
}
// BarTime
if (order.getBarTime() == null) {
pstmt.setNull(14, Types.DATE);
} else {
pstmt.setTimestamp(14, (new Timestamp(order.getBarTime().getTime())));
}
// Loss or Gain
if (order.getLossOrGain() == null) {
pstmt.setNull(15, Types.DOUBLE);
} else {
pstmt.setDouble(15, order.getLossOrGain());
}
// OCAGroup
if (order.getOCAGroup() == null) {
pstmt.setNull(16, Types.VARCHAR);
} else {
pstmt.setString(16, order.getOCAGroup());
}
// OCAType
if (order.getOcaType() == null) {
pstmt.setNull(17, Types.SMALLINT);
} else {
pstmt.setShort(17, order.getOcaType());
}
// OrderID
if (order.getOrderID() == null) {
pstmt.setNull(18, Types.INTEGER);
} else {
pstmt.setInt(18, order.getOrderID());
}
// ParentID
if (order.getParentID() == null) {
pstmt.setNull(19, Types.INTEGER);
} else {
pstmt.setInt(19, order.getParentID());
}
// PermID
if (order.getPermID() == null) {
pstmt.setNull(20, Types.INTEGER);
} else {
pstmt.setInt(20, order.getPermID());
}
// EntryDateTime
if (order.getEntryDateTime() == null) {
pstmt.setNull(21, Types.DATE);
} else {
pstmt.setTimestamp(21, (new Timestamp(order.getEntryDateTime().getTime())));
}
// ExecutedDateTime
if (order.getExecutedDateTime() == null) {
pstmt.setNull(22, Types.DATE);
} else {
pstmt.setTimestamp(22, (new Timestamp(order.getExecutedDateTime().getTime())));
}
// Status
if (order.getStatus() == null) {
pstmt.setNull(23, Types.VARCHAR);
} else {
pstmt.setString(23, order.getStatus());
}
pstmt.execute();
pstmt.close();
} catch (SQLException ex) {
MsgBox.err2(ex);
} finally {
}
}