package engine.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;
import org.apache.commons.configuration.Configuration;
import org.apache.log4j.Logger;
import engine.core.MTData;
import engine.core.MTServer;
import engine.core.OrderPoint;
import engine.core.Utils;
public class DBUtil {
private static Logger log = Logger.getLogger(DBUtil.class.getName());
private static final String SQL_MTDATA_SELECT = "SELECT * FROM users WHERE username=? AND password=?";
private static final String SQL_MTDATA_INSERT = " insert into raw (mtId, currency, order_tp, lots)"
+ " values (?, ?, ?, ?)";
private static final String SQL_ORDERPOINT_SELECT = "select currency, order_tp, sum(lots) * (select weight from weight where eaId = ? and mtId = raw.mtId) from raw group by order_tp, currency";
private static final String SQL_EAIDARAY_SELECT = "select distinct eaId from weight";
private static final String SQL_ORDERPOINT_INSERT = "INSERT INTO point (eaId, currency,order_tp,point) VALUES (?, ?, ?, ?);";
private static final String SQL_POINT_SELECT = "SELECT currency, order_tp, point FROM point WHERE eaId=?";
public List<MTData> getMTData(MTData data) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
ArrayList<MTData> list = new ArrayList<MTData>();
try {
connection = DB.getConnection();
statement = connection.prepareStatement(SQL_MTDATA_SELECT);
rs = statement.executeQuery();
while (rs.next()) {
list.add(new MTData(rs.getString(1), rs.getString(2), rs
.getString(3), rs.getFloat(4)));
}
} finally {
if (rs != null)
try {
rs.close();
} catch (SQLException ignore) {
}
if (statement != null)
try {
statement.close();
} catch (SQLException ignore) {
}
if (connection != null)
try {
connection.close();
} catch (SQLException ignore) {
}
}
return list;
}
public static void insertMTData(List<MTData> mtDataList) throws SQLException {
for (MTData data : mtDataList) {
insertMTData(data);
}
}
public static void insertMTData(MTData data) throws SQLException {
Connection conn = null;
PreparedStatement rs = null;
try {
conn = DB.getConnection();
// create the mysql insert preparedstatement
rs = conn.prepareStatement(SQL_MTDATA_INSERT);
rs.setString(1, data.getMtId());
rs.setString(2, data.getCurrency());
rs.setString(3, data.getOrderTp());
rs.setFloat(4, data.getLots());
// execute the preparedstatement
rs.execute();
conn.close();
} finally {
if (rs != null)
try {
rs.close();
} catch (SQLException ignore) {
}
if (conn != null)
try {
conn.close();
} catch (SQLException ignore) {
}
}
}
public static void clearMTData(MTData data) throws SQLException {
Connection conn = DB.getConnection();
String query = "delete from raw";
PreparedStatement preparedStmt = conn.prepareStatement(query);
preparedStmt.setInt(1, 3);
// execute the preparedstatement
preparedStmt.execute();
conn.close();
}
public static List<OrderPoint> getMTData(String eaId) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
ArrayList<OrderPoint> list = new ArrayList<OrderPoint>();
try {
connection = DB.getConnection();
statement = connection.prepareStatement(SQL_ORDERPOINT_SELECT);
statement.setString(1, eaId);
rs = statement.executeQuery();
while (rs.next()) {
list.add(new OrderPoint(rs.getString(1), rs.getString(2), rs.getFloat(3)));
}
} finally {
if (rs != null)
try {
rs.close();
} catch (SQLException ignore) {
}
if (statement != null)
try {
statement.close();
} catch (SQLException ignore) {
}
if (connection != null)
try {
connection.close();
} catch (SQLException ignore) {
}
}
return list;
}
public static List<String> getEaIdArray() throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
ArrayList<String> list = new ArrayList<String>();
try {
connection = DB.getConnection();
statement = connection.prepareStatement(SQL_EAIDARAY_SELECT);
rs = statement.executeQuery();
while (rs.next()) {
list.add(rs.getString(1));
}
} finally {
if (rs != null)
try {
rs.close();
} catch (SQLException ignore) {
}
if (statement != null)
try {
statement.close();
} catch (SQLException ignore) {
}
if (connection != null)
try {
connection.close();
} catch (SQLException ignore) {
}
}
return list;
}
public static void insertPointData(String eaId, List<OrderPoint> pointList) throws SQLException {
Connection conn = null;
PreparedStatement rs = null;
for (OrderPoint data: pointList) {
try {
conn = DB.getConnection();
// create the mysql insert preparedstatement
rs = conn.prepareStatement(SQL_ORDERPOINT_INSERT);
rs.setString(1, eaId);
rs.setString(2, data.getCurrency());
rs.setString(3, data.getOrder_tp());
rs.setFloat(4, data.getPoint());
// execute the preparedstatement
rs.execute();
conn.close();
} finally {
if (rs != null)
try {
rs.close();
} catch (SQLException ignore) {
}
if (conn != null)
try {
conn.close();
} catch (SQLException ignore) {
}
}
}
}
public static List<OrderPoint> getPointData(String eaId) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
ArrayList<OrderPoint> list = new ArrayList<OrderPoint>();
try {
connection = DB.getConnection();
statement = connection.prepareStatement(SQL_POINT_SELECT);
statement.setString(1, eaId);
rs = statement.executeQuery();
while (rs.next()) {
log.debug(rs.getString(1) + ":" + rs.getString(2) + ":" + rs.getString(3));
list.add(new OrderPoint(rs.getString(1), rs.getString(2), Utils.toLotFloat(rs.getString(3))));
}
} finally {
if (rs != null)
try {
rs.close();
} catch (SQLException ignore) {
}
if (statement != null)
try {
statement.close();
} catch (SQLException ignore) {
}
if (connection != null)
try {
connection.close();
} catch (SQLException ignore) {
}
}
return list;
}
public static void clearPointTable() throws SQLException {
Connection conn = DB.getConnection();
String query = "delete from raw";
PreparedStatement preparedStmt = conn.prepareStatement(query);
//preparedStmt.setInt(1, 3);
// execute the preparedstatement
preparedStmt.execute();
conn.close();
}
}