· 9 years ago · Aug 08, 2017, 06:40 AM
1import java.io.File;
2import java.io.FileNotFoundException;
3import java.sql.*;
4import java.util.HashMap;
5import java.util.Map;
6import java.util.Scanner;
7
8
9
10public class DataLoader {
11
12 //manages the connection to the marketData database
13 public void connectDBandLoad(){
14 Connection conn = null;
15
16 try
17 {
18 String userName = "root";
19 String password = "root";
20 String url = "jdbc:mysql://localhost:8889/marketData";
21 Class.forName("com.mysql.jdbc.Driver").newInstance();
22 conn = DriverManager.getConnection(url, userName, password);
23 System.out.println ("Database connection established");
24 loadTextFilesToDB(conn);
25
26 }
27 catch (Exception e)
28 {
29 System.err.println ("Cannot connect to database server");
30 }
31 finally
32 {
33 if (conn != null)
34 {
35 try
36 {
37 conn.close ();
38 System.out.println ("Database connection terminated");
39 }
40 catch (Exception e) {}
41 }
42 }
43 }
44
45 //reads through the text files and creates data lines
46 private void loadTextFilesToDB(Connection conn) throws SQLException {
47
48 String currentDate = "20070101";
49
50 //for each file
51 while(!currentDate.equals("20080101")){
52
53 Map<String, Object> dataLine = new HashMap<String, Object>();
54 File file = new File("Resources/TSXData/TSX_" + currentDate + ".txt");
55
56 try {
57 Scanner scanner = new Scanner(file);
58 scanner.useDelimiter(",|\r");
59 scanner.nextLine();
60
61 //for each line
62 while(scanner.hasNextLine()){
63
64 dataLine.put("SYMBOL", scanner.next());
65 dataLine.put("DATE", scanner.next());
66 dataLine.put("OPEN", scanner.nextFloat());
67 dataLine.put("HIGH", scanner.nextFloat());
68 dataLine.put("LOW", scanner.nextFloat());
69 dataLine.put("CLOSE", scanner.nextFloat());
70 dataLine.put("VOLUME", scanner.nextInt());
71 scanner.nextLine();
72
73 storeLineInDB(dataLine, conn);
74 }
75
76 scanner.close();
77
78 } catch (FileNotFoundException e) {
79 System.out.println("No market data for " + currentDate);
80 }
81
82 currentDate = incrementCurrentDate(currentDate);
83 }
84
85 }
86
87 //Returns a string that is an incremented version of the given date string
88 private String incrementCurrentDate(String currentDate) {
89
90 Integer day = Integer.valueOf(currentDate.substring(6));
91 Integer month = Integer.valueOf(currentDate.substring(4,6));
92 Integer year = Integer.valueOf(currentDate.substring(0,4));
93
94 if(++day > 31){ day = 1; month++; }
95 if(month > 12){ month = 1; year++; }
96
97 return year + String.format("%02d", month) + String.format("%02d", day);
98 }
99
100 //Stores the info found in the data line in the appropriate tables
101 private void storeLineInDB(Map<String, Object> _dataLine, Connection _conn) {
102
103 System.out.println(_dataLine);
104 String symbol = (String)_dataLine.get("SYMBOL");
105 String date = (String)_dataLine.get("DATE");
106 Float open = (Float)_dataLine.get("OPEN");
107 Float high = (Float)_dataLine.get("HIGH");
108 Float low = (Float)_dataLine.get("LOW");
109 Float close = (Float)_dataLine.get("CLOSE");
110 Integer volume = (Integer)_dataLine.get("VOLUME");
111
112 try{
113 Statement stmt = _conn.createStatement();
114 String query = "SELECT * FROM commodity WHERE symbol = '" + symbol + "'";
115 ResultSet rs = stmt.executeQuery(query);
116
117 //if symbol exists in commodity table, do nothing, else create new table
118 if(!rs.next())
119 createNewCommodityEntry(symbol, _conn);
120
121 //add data to table
122 String insert = "INSERT INTO `marketData`.`" + symbol + "` " +
123 "(date, open, high, low, close, volume) VALUES (" +
124 "'" + date + "', " +
125 open + ", " +
126 high + ", " +
127 low + ", " +
128 close + ", " +
129 volume + ")";
130
131 stmt = _conn.createStatement();
132 Integer result = stmt.executeUpdate(insert);
133
134 // close the result set and the statement
135 rs.close();
136 stmt.close();
137 } catch(SQLException s){ s.printStackTrace(); }
138
139 }
140
141 //creates a new entry in the commodity table, and also creates a table with the symbols name
142 private void createNewCommodityEntry(String _symbol, Connection _conn) {
143 try{
144 //insert the commodity name into the commodity table
145 Statement stmt = _conn.createStatement();
146 String insert = "INSERT INTO commodity VALUES('" + _symbol + "', NULL)";
147 stmt.executeUpdate(insert);
148
149 //create a new table with the commodity name
150 stmt = _conn.createStatement();
151 String update = "CREATE TABLE `" + _symbol + "` (" +
152 "`date` DATE NOT NULL ," +
153 "`open` FLOAT NOT NULL ," +
154 "`high` FLOAT NOT NULL ," +
155 "`low` FLOAT NOT NULL ," +
156 "`close` FLOAT NOT NULL ," +
157 "`volume` INT NOT NULL ," +
158 "PRIMARY KEY ( `date` ))";
159 stmt.executeUpdate(update);
160
161 // close the result set and the statement
162
163 stmt.close();
164 } catch(SQLException s){ s.printStackTrace(); }
165
166 }
167
168 //main method for data loader
169 public static void main(String[] args) {
170 DataLoader loader = new DataLoader();
171 loader.connectDBandLoad();
172
173 }
174
175}