· 10 years ago · Jul 07, 2016, 07:12 PM
1import java.util.*;
2import java.io.*;
3import java.sql.*;
4
5public class RawData {
6 private int numberOfLocs = -1;
7 private int numberOfRows = -1;
8
9 private String tablename = " Agnes1 ";
10// private String tablename = " Data ";
11// private String tablename = " Visitors ";
12
13
14 //ling public String[] getNodeNames(String restriction, String classNumber, String dateRestriction) {
15 public String[] getNodeNames(String restriction, int classNumber, String dateRestriction) {
16 Connection C = getConnection();
17 String[] names = null;
18
19
20 try {
21 Statement Stmt = C.createStatement(
22 ResultSet.TYPE_SCROLL_INSENSITIVE,
23 ResultSet.CONCUR_UPDATABLE
24 );
25
26// Stmt.executeUpdate("CREATE TEMPORARY TABLE temp (loc VARCHAR(30))");
27//ling
28 Stmt.executeUpdate("CREATE TABLE IF NOT EXISTS #temp (loc VARCHAR(30))");
29//end ling
30
31 String s = "WHERE class='" + classNumber + "'";
32 if (!restriction.equals("")) {
33 s += " AND student like '" + restriction + "'";
34 }
35 if (!dateRestriction.equals("")) {
36 s += " AND timedate='" + dateRestriction + "'";
37 }
38
39 Stmt.executeUpdate("INSERT INTO #temp SELECT DISTINCT fromLoc FROM Data " + s);
40// Stmt.executeUpdate(" SELECT DISTINCE loc FROM Data " + s);
41 Stmt.executeUpdate("INSERT INTO #temp SELECT DISTINCT toLoc FROM Data " + s);
42
43 ResultSet RS = Stmt.executeQuery("SELECT DISTINCT loc FROM #temp ORDER BY loc");
44
45 RS.last();
46
47 int n = RS.getRow();
48 RS.beforeFirst();
49
50 if (n < 1) return null;
51
52 names = new String[n];
53 int i = 0;
54 while (RS.next()) {
55 names[i++] = RS.getString("loc");
56 }
57 }
58 catch (SQLException E) {
59 System.out.println("SQLException: " + E.getMessage());
60 System.out.println("SQLState: " + E.getSQLState());
61 System.out.println("VendorError: " + E.getErrorCode());
62 }
63
64 return names;
65 }
66
67 public String[] getStudentNames(String restriction, int classNumber, String dateRestriction) {
68 Connection C = getConnection();
69 String[] names = null;
70
71 try {
72 Statement Stmt = C.createStatement(
73 ResultSet.TYPE_SCROLL_INSENSITIVE,
74 ResultSet.CONCUR_UPDATABLE
75 );
76
77 Stmt.executeUpdate("CREATE TABLE IF NOT EXISTS #temp (student VARCHAR(30))");
78
79 String s = "WHERE class='" + classNumber + "'";
80 if (!restriction.equals("")) {
81 s += " AND student like '" + restriction + "'";
82 }
83 if (!dateRestriction.equals("")) {
84 s += " AND timedate='" + dateRestriction + "'";
85 }
86
87 Stmt.executeUpdate("INSERT INTO #temp SELECT DISTINCT student FROM Data " + s);
88
89 ResultSet RS2 = Stmt.executeQuery("SELECT DISTINCT student FROM #temp ORDER BY student");
90
91// ResultSet RS2 = Stmt.executeQuery("SELECT distinct student FROM Data ");
92// ResultSet RS2 = Stmt.executeQuery("SELECT distinct Student FROM data " + s);
93
94 RS2.last();
95 int n = RS2.getRow();
96 System.out.print("n has " + n + " students");
97
98 RS2.beforeFirst();
99
100 if (n < 1) return null;
101
102 names = new String[n];
103 int i = 0;
104 while (RS2.next()) {
105 names[i++] = RS2.getString("student");
106 }
107
108 }
109 catch (SQLException E) {
110 System.out.println("SQLException: " + E.getMessage());
111 System.out.println("SQLState: " + E.getSQLState());
112 System.out.println("VendorError: " + E.getErrorCode());
113 }
114
115 return names;
116 }
117
118 public double[][] getTransitionMatrix(int classNumber, String dateRestriction, String studentName, Hashtable invNodeNames) {
119 int n = invNodeNames.size();
120 double[][] matrix = new double[n][n];
121
122 Connection C = getConnection();
123
124 try {
125 Statement Stmt = C.createStatement(
126 ResultSet.TYPE_SCROLL_INSENSITIVE,
127 ResultSet.CONCUR_UPDATABLE
128 );
129
130 String s;
131 s = "SELECT fromLoc,toLoc ";
132 s += "FROM data ";
133 s += "WHERE class='" + classNumber + "' ";
134 s += " AND student like '" + studentName + "' ";
135 s += " AND fromLoc != 'none' ";
136 s += " AND toLoc != 'none' ";
137
138 if (!dateRestriction.equals("")) {
139 s += " AND timedate='" + dateRestriction + "'";
140 }
141
142 int fromLoc, toLoc;
143 ResultSet RS = Stmt.executeQuery(s);
144 while (RS.next()) {
145 fromLoc = ((Integer)invNodeNames.get(RS.getString("fromLoc").toLowerCase())).intValue();
146 toLoc = ((Integer)invNodeNames.get(RS.getString("toLoc").toLowerCase())).intValue();
147 matrix[fromLoc][toLoc]++;
148 }
149
150 for (int i = 0; i < n; i++) {
151 for (int j = 0; j < n; j++) {
152 if (matrix[i][j] < .5) matrix[i][j] = -1;
153 }
154 }
155
156
157 }
158 catch (SQLException E) {
159 System.out.println("SQLException: " + E.getMessage());
160 System.out.println("SQLState: " + E.getSQLState());
161 System.out.println("VendorError: " + E.getErrorCode());
162 }
163
164 return matrix;
165 }
166
167
168
169 public ResultSet query(String q) {
170 ResultSet RS = null;
171
172 Connection C = getConnection();
173
174 try {
175 Statement Stmt = C.createStatement(
176 ResultSet.TYPE_SCROLL_INSENSITIVE,
177 ResultSet.CONCUR_UPDATABLE
178 );
179
180 String s;
181 s = "CREATE TABLE IF NOT EXISTS #temp (";
182 s += "id INT, ";
183 s += "class INT, ";
184 s += "student VARCHAR(30), ";
185 s += "purpose VARCHAR(50), ";
186 s += "fromLoc VARCHAR(30), ";
187 s += "toLoc VARCHAR(30)";
188 s += ")";
189
190 Stmt.executeUpdate(s);
191
192 Stmt.executeUpdate("INSERT INTO #temp " + q);
193
194 Stmt.executeUpdate("CREATE TABLE IF NOT EXISTS #temp1 (loc VARCHAR(30))");
195
196 Stmt.executeUpdate("INSERT INTO #temp1 SELECT DISTINCT fromLoc FROM #temp");
197 Stmt.executeUpdate("INSERT INTO #temp1 SELECT DISTINCT toLoc FROM #temp");
198
199 RS = Stmt.executeQuery("SELECT COUNT(DISTINCT loc) AS n FROM #temp1");
200 if (RS.next()) {
201 numberOfLocs = RS.getInt("n");
202 } else {
203 numberOfLocs = -1;
204 }
205
206 RS = Stmt.executeQuery(q);
207
208 RS.last();
209 numberOfRows = RS.getRow();
210 RS.first();
211 }
212 catch (SQLException E) {
213 System.out.println("SQLException: " + E.getMessage());
214 System.out.println("SQLState: " + E.getSQLState());
215 System.out.println("VendorError: " + E.getErrorCode());
216 }
217
218 return RS;
219 }
220
221
222 private Connection getConnection() {
223 Connection C = null;
224 try {
225
226 } catch (Exception E) {
227 System.err.println("Unable to load driver.");
228 E.printStackTrace();
229 }
230 String dbname = "pathfinderdata";
231 String username = "root";
232 String password = "newpass";
233
234 try {
235
236 //mona C = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433", "path", "path");
237 String DB_URL = "jdbc:mysql://localhost:3306/pathfinderdata";
238 C = DriverManager.getConnection(DB_URL, username, password);
239
240 }
241 catch (SQLException E) {
242 System.out.println("SQLException: " + E.getMessage());
243 System.out.println("SQLState: " + E.getSQLState());
244 System.out.println("VendorError: " + E.getErrorCode());
245 }
246
247// System.out.println("LingWang: End");
248 return C;
249 }
250
251
252 public int getNumberOfLocs() { return numberOfLocs; }
253
254 public int getNumberOfRows() { return numberOfRows; }
255}