1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
|
package edu.brown.cs.student.term;
import edu.brown.cs.student.term.hub.Holder;
import edu.brown.cs.student.term.profit.ProfitCalculation;
import edu.brown.cs.student.term.trade.Trade;
import java.sql.*;
import java.time.Instant;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
public class DatabaseQuerier {
private static Connection conn = null;
//TODO: Be prepared to overhaul this to account for IDs
/**
* Makes a database querier for a particular sqlite database
*
* @param filename - String representing filepath of database
* @throws SQLException
* @throws ClassNotFoundException
*/
public DatabaseQuerier(String filename) throws SQLException,
ClassNotFoundException {
Class.forName("org.sqlite.JDBC");
String urlToDB = "jdbc:sqlite:" + filename;
// AutoClosable TRY-WITH-RESOURCES ensures database connection will be closed when it is done
conn = DriverManager.getConnection(urlToDB);
}
public static Connection getConn() {
return conn;
}
/**
* Gets the names of all stocks traded between start and end ddate
*
* @param startDate - the start date
* @param endDate - the end date
* @return a list of stock names
* @throws SQLException
*/
public List<String> getRecentStocks(Instant startDate, Instant endDate) throws SQLException {
List<String> stocks = new ArrayList<>();
PreparedStatement prep = conn.prepareStatement(
"SELECT DISTINCT stock_name FROM trades WHERE trade_timestamp <= ? AND trade_timestamp >= ?");
prep.setLong(1, endDate.toEpochMilli());
prep.setLong(2, startDate.toEpochMilli());
ResultSet rs = prep.executeQuery();
while (rs.next()) {
String ticker = validateTicker(rs.getString(1));
if (!ticker.equals("")) {
stocks.add(ticker);
}
}
rs.close();
prep.close();
return stocks;
}
/**
* Gets all the trades in by stock and buy type, ordered by time
*
* @param startDate - the start date of these trades
* @param endDate - the end date of these trades
* @return a list of list of trades as specified above
* @throws SQLException - if something goes wrong with connection
*/
public List<List<Trade>> getAllTradesByStock(Instant startDate, Instant endDate)
throws SQLException {
List<List<Trade>> allTrades = new ArrayList<>();
List<String> stocks = getRecentStocks(startDate, endDate);
//get the buys and sells for each stock
for (String stock : stocks) {
allTrades.add(getTradeByStock(stock, 1, startDate, endDate));
allTrades.add(getTradeByStock(stock, 0, startDate, endDate));
}
return allTrades;
}
/**
* Gets a single stock's list of trades for that time period (either buy or sell)
*
* @param stock - string name of the stock to get the trades for
* @param isBuy - integer whether it's a buy or sell
* @param startDate - an Instant representing the start of the time period
* @param endDate - an Instant representing the end of the time period
* @return - a list of trades for that stock
* @throws SQLException - if issue getting connection
*/
public List<Trade> getTradeByStock(String stock, int isBuy, Instant startDate, Instant endDate)
throws SQLException {
List<Trade> trades = new ArrayList<>();
/*
if (isValidStock(stock)) {
return trades;
}*/
PreparedStatement prep = conn.prepareStatement(
"SELECT * FROM trades WHERE (stock_name = ? AND is_buy = ?) "
+ "AND (trade_timestamp <= ? AND trade_timestamp >= ?) ORDER BY trade_timestamp");
prep.setString(1, stock);
prep.setInt(2, isBuy);
prep.setLong(3, endDate.toEpochMilli());
prep.setLong(4, startDate.toEpochMilli());
ResultSet rs = prep.executeQuery();
while (rs.next()) {
trades.add(new Trade(rs.getInt(1), rs.getString(2),
rs.getLong(4), rs.getInt(5),
rs.getInt(6), new Holder(rs.getInt(7), rs.getString(3)),
rs.getDouble(8)));
}
rs.close();
prep.close();
return trades;
}
public List<Trade> getAllTradesByHolder(Integer holder_id, Date startDate, Date endDate) {
LinkedList<Trade> trades = new LinkedList<>();
try {
PreparedStatement prep;
prep =
conn.prepareStatement("SELECT * FROM \'trades\' WHERE holder_id = ?"
+ " AND trade_timestamp BETWEEN ? AND ?"
+ "order by trade_timestamp asc;");
prep.setInt(1, holder_id);
prep.setDate(2, startDate);
prep.setDate(3, endDate);
ResultSet rs = prep.executeQuery();
while (rs.next()) {
String ticker = rs.getString("stock_name");
//if (isValidTicker(ticker)) {
trades.addFirst(new Trade(rs.getInt("trade_id"), ticker,
rs.getDouble("trade_timestamp"),
rs.getInt("is_buy"),
rs.getInt("number_of_shares"),
new Holder(rs.getInt("holder_id"), rs.getString("holder_name")),
rs.getDouble("share_price")));
//}
}
prep.close();
} catch (SQLException e) {
System.out.println("ERROR: sql error getting trades");
}
return trades;
}
private String validateTicker(String ticker) {
//this is cleaning some improperly formatted tickers
ticker = ticker.replaceAll("[^a-zA-Z0-9]", "").toUpperCase();
if(ticker.contains("[0-9]") ||
ticker.length() > 5 ||
ticker.length() < 2 ||
ticker.contains("NONE")) {
return "";
}
return ticker;
}
}
|
