· 9 years ago · Mar 26, 2017, 01:06 PM
1<dependency>
2 <groupId>com.google.api-client</groupId>
3 <artifactId>google-api-client</artifactId>
4 <version>1.22.0</version>
5 </dependency>
6
7import com.google.api.client.googleapis.auth.oauth2.GoogleIdToken;
8import com.google.api.client.googleapis.auth.oauth2.GoogleIdToken.Payload;
9import com.google.api.client.googleapis.auth.oauth2.GoogleIdTokenVerifier;
10
11GoogleIdTokenVerifier verifier=new GoogleIdTokenVerifier.Builder(transport, JsonFactory).setAudience(Arrays.asList(CLIENT_ID)).setIssuer("https://accounts.google.com")
12 // Or, if multiple clients access the backend:
13 //.setAudience(Arrays.asList(CLIENT_ID_1, CLIENT_ID_2, CLIENT_ID_3)).build();
14
15package backend.bigville.edu
16
17
18
19import java.sql.*;
20import java.util.*;
21import java.util.Date;
22import com.google.gson.Gson;
23
24import com.google.api.client.googleapis.auth.oauth2.GoogleIdToken;
25import com.google.api.client.googleapis.auth.oauth2.GoogleIdToken.Payload;
26import com.google.api.client.googleapis.auth.oauth2.GoogleIdTokenVerifier;
27
28import static spark.Spark.*;
29
30
31
32
33
34
35
36/**
37 * @author Students, Student2
38 * @version 1.1
39 */
40
41
42/**
43 * Datum object is the base object for storing our data on the backend. We store an index, title, comment,
44 * number of likes, upload date, and like date.
45 */
46class Datum {
47 int index;
48 String title;
49 String comment;
50 int numLikes;
51 java.util.Date uploadDate;
52 java.util.Date lastLikeDate;
53}
54
55
56/**
57 * The App class creates an App object which passes in a MySQL database and stores the
58 * data in rows to be pulled from later.
59 */
60public class App {
61 // Final static strings, used throughout program.
62 final static String goodData = "{"res":"ok"}";
63 final static String badData = "{"res":"bad data"}";
64 final static String sFileLocation = "/web"; // FIX FOR WHATEVER THE HIERARCHY IT IS IN FINAL VERSION
65
66 // Only one gson instantiation, for efficiency.
67 final Gson gson;
68
69 // Environment variables.
70 static Map<String, String> env = System.getenv();
71 static String ip = env.get("MYSQL_IP");
72 static String port = env.get("MYSQL_PORT");
73 static String user = env.get("MYSQL_USER");
74 static String pass = env.get("MYSQL_PASS");
75 static String db = env.get("MYSQL_DB");
76
77 /**
78 * Get all data from our database and returns it in JSON format.
79 * @return JSON object from SQL frontend
80 */
81 String getAllData() {
82 // get the MYSQL configuration from the environment
83 Class.forName("org.postgresql.Driver");
84 Connection conn = null;
85
86 // Use these to connect to the database and issue commands
87 // Connect to the database; fail if we can't
88 try {
89 // Open a connection, fail if we cannot get one
90 conn = DriverManager.getConnection("jdbc:mysql://" + ip + ":" + port + "/" + db + "?useSSL=false", user, pass);
91 if (conn == null) {
92 System.out.println("Error: getConnection returned null object in getAllData");
93 return null;
94 }
95 } catch (SQLException e) {
96 System.out.println("Error: getConnection threw an exception in getAllData");
97 e.printStackTrace();
98 return null;
99 }
100
101 String result = "";
102 // watch multiples
103 ArrayList<Datum> results = new ArrayList<>();
104 try {
105 // get all data into a ResultSet
106 String getStmt = "SELECT * FROM tblData";
107 PreparedStatement stmt = conn.prepareStatement(getStmt);
108
109 ResultSet rs = stmt.executeQuery();
110 // iterate through the java ResultSet
111 while (rs.next()) {
112 // convert the RS to Data objects.
113 Datum currentDatum = new Datum();
114 currentDatum.index = rs.getInt("id");
115 currentDatum.title = rs.getString("title");
116 currentDatum.comment = rs.getString("comment");
117 currentDatum.numLikes = rs.getInt("numLikes");
118 currentDatum.uploadDate = sqlDateToJavaDate(rs.getTimestamp("uploadDate"));
119 currentDatum.lastLikeDate = sqlDateToJavaDate(rs.getTimestamp("lastLikeDate"));
120 results.add(currentDatum);
121 }
122 stmt.close();
123 //conn.close();
124 } catch (SQLException e) {
125 System.out.println("Error: query failed");
126 e.printStackTrace();
127 }
128 // Convert the array of results to a JSON string and return it
129 result = gson.toJson(results);
130 return result;
131 }
132
133 /**
134 * This insert takes new data from the frontend sever and adds it into the datum database
135 * @param d A Datum object retrieved by and built through Spark framework
136 * @return command telling server if addition was a success or not
137 */
138 String insertDatum(Datum d) {
139 // get the MYSQL configuration from the environment
140 Connection conn = null;
141
142 // Use these to connect to the database and issue commands
143 // Connect to the database; fail if we can't
144 //System.out.println("Connecting to " + ip + ":" + port + "/" + db);
145 try {
146 // Open a connection, fail if we cannot get one
147 conn = DriverManager.getConnection("jdbc:mysql://" + ip + ":" +
148 port + "/" + db + "?useSSL=false", user, pass);
149 if (conn == null) {
150 System.out.println("Error: getConnection returned null object");
151 return null;
152 }
153 } catch (SQLException e) {
154 System.out.println("Error: getConnection threw an exception in insertDatum");
155 e.printStackTrace();
156 return null;
157 }
158 // Only insert if whole datum is not null
159 if (d != null && d.title != null && d.comment != null && d.numLikes == 0 && d.uploadDate != null && d.lastLikeDate != null) {
160 try {
161 // (id, title, comment, numLikes, uploadDate, lastLikeDate)
162 String insertStmt = "INSERT INTO tblData VALUES (default, ?, ?, ?, ?, ?)";
163 PreparedStatement stmt = conn.prepareStatement(insertStmt);
164 stmt.setString(1,d.title);
165 stmt.setString(2,d.comment);
166 stmt.setInt(3,d.numLikes);
167 stmt.setTimestamp(4,javaDateToSqlDate(d.uploadDate));
168 stmt.setTimestamp(5,javaDateToSqlDate(d.lastLikeDate));
169 stmt.executeUpdate();
170 stmt.close();
171 } catch (SQLException e) {
172 System.out.println("Error: insertion failed");
173 e.printStackTrace();
174 }
175 return goodData;
176 } else {
177 return badData;
178 }
179 }
180
181 /**
182 * Execute an UPDATE query to modify table contents. Done by passing a boolean isLiked to indicate
183 * whether it's a LIKE or DISLIKE.
184 * @param idNum This is the index of the values to change.
185 * @param numLikes This is the original number of likes.
186 * @param newLastLikeDate This is the value to update time of last like/dislike.
187 * @param isLiked If true, it's a LIKE. If false, it's a DISLIKE.
188 */
189 void updateLike(int idNum, int numLikes, Date newLastLikeDate, Boolean isLiked) {
190 // get the MYSQL configuration from the environment
191 Connection conn = null;
192 int newNumLikes = 0;
193
194 // Check if it's like/dislike and change numLikes accordingly.
195 if (isLiked) newNumLikes = ++numLikes;
196 else newNumLikes = --numLikes;
197
198 try {
199 // Open a connection, fail if we cannot get one
200 conn = DriverManager.getConnection("jdbc:mysql://" + ip + ":" + // HERE IS WHERE WE CONNECT
201 port + "/" + db, user, pass);
202 if (conn == null) {
203 System.out.println("Error: getConnection returned null object");
204 return;
205 }
206 } catch (SQLException e) {
207 System.out.println("Error: getConnection threw an exception in updateLike");
208 e.printStackTrace();
209 return;
210 }
211
212 //
213 try {
214 String updateStmt = "UPDATE tblData SET numLikes = ?, lastLikeDate = ? WHERE id = ?";
215 PreparedStatement stmt = conn.prepareStatement(updateStmt);
216 stmt.setInt(1, newNumLikes);
217 stmt.setTimestamp(2, javaDateToSqlDate(newLastLikeDate));
218 stmt.setInt(3, idNum);
219 stmt.executeUpdate();
220 stmt.close();
221 //conn.close(); I don't think we need this
222 } catch (SQLException e) {
223 System.out.println("Error: unable to update row");
224 e.printStackTrace();
225 }
226 }
227
228
229 /**
230 * Constructs an App object which creates a new Database and Gson Object to be used later by the different routes
231 * This object is used to store the Database for each instance.
232 */
233 public App() {
234 createDB();
235 gson = new Gson();
236 }
237
238 /**
239 * This method runs a command that drops the database tblData. Made private to ensure "adversaries"
240 * can't access it since it is highly dangerous in the wrong hands. Called within createDB() to ensure
241 * that our Docker instance is cleared. Especially useful for tests.
242 */
243 private static void dropDB() {
244 Connection conn = null;
245
246 // Connect to the database; fail if we can't
247 try {
248 // Open a connection, fail if we cannot get one
249 conn = DriverManager.getConnection("jdbc:mysql://" + ip + ":" +
250 port + "/" + db, user, pass);
251 if (conn == null) {
252 System.out.println("Error: getConnection returned null object in createDB");
253 return;
254 }
255 } catch (SQLException e) {
256 System.out.println("Error: getConnection in createDB threw an exception");
257 e.printStackTrace();
258 return;
259 }
260
261 try {
262 PreparedStatement stmt = null;
263 String createStatement = "DROP TABLE IF EXISTS tblData";
264 stmt = conn.prepareStatement(createStatement);
265 stmt.execute();
266 stmt.close();
267 } catch (SQLException e) {
268 System.out.println("Error: droptable error");
269 e.printStackTrace();
270 return;
271 }
272 }
273
274
275 /**
276 * This method actually creates the database in Docker that is used to store values passed from
277 * MySQL commands.
278 */
279 public void createDB() {
280 // Quickly make sure to drop table if exists. Used primarily for ease of testing.
281 // NOTE: This only works at the beginning of this because we create a new App object each time
282 // we need this connection. MAIN only uses one instance of App, so it won't accidentally delete.
283 dropDB();
284
285 Connection conn = null;
286
287 // Connect to the database; fail if we can't
288 try {
289 // Open a connection, fail if we cannot get one
290 conn = DriverManager.getConnection("jdbc:mysql://" + ip + ":" +
291 port + "/" + db, user, pass);
292 if (conn == null) {
293 System.out.println("Error: getConnection returned null object in createDB");
294 return;
295 }
296 } catch (SQLException e) {
297 System.out.println("Error: getConnection in createDB threw an exception");
298 e.printStackTrace();
299 return;
300 }
301 // Create a table to store data. It matches the 'Datum' type from the
302 // previous tutorial.
303
304 PreparedStatement stmt = null;
305 String createStatement = "CREATE TABLE tblData (id INT(64) NOT NULL AUTO_INCREMENT, title VARCHAR(200), comment VARCHAR(200), numLikes INT(64), uploadDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, lastLikeDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(id))";
306 try {
307 stmt = conn.prepareStatement(createStatement);
308 stmt.execute();
309 stmt.close();
310 //conn.close();
311 } catch (SQLException e) {
312 // Should we handle this in a better way?
313 System.out.println("Table not created (it may already exist)");
314 }
315 }
316
317 /**
318 * Method that converts a Java Date to a Timestamp object.
319 * @param myDate Date formatted as java.util.Date, to be converted to java.sql.Date
320 * @return Returns Date as Timestamp object.
321 */
322 public static java.sql.Timestamp javaDateToSqlDate(java.util.Date myDate) {
323 return new java.sql.Timestamp(myDate.getTime());
324 }
325
326 /**
327 * Method that converts a Timestamp to a Java Date object.
328 * @param myDate Date formatted as java.sql.Date, to be converted to java.util.Date
329 * @return Returns Date as Timestamp object.
330 */
331 public static java.util.Date sqlDateToJavaDate(java.sql.Timestamp myDate) {
332 // This implementation is sort of annoying. I'm using a Calendar between the Timestamp
333 // and Date objects. So I create a Calendar, set it using the timestamp, and finally
334 // convert the Calendar to a Date object, which will be returned.
335 Calendar curCal = Calendar.getInstance();
336 curCal.setTimeInMillis( myDate.getTime() );
337 return curCal.getTime();
338 }
339
340 /**
341 * Main method which holds all get and post routes for updating and sending the database to the server.
342 * Each route is formed as a lambda function which returns a GSON object to be passed.
343 * @param args Standard Java main class argument.
344 */
345 public static void main( String[] args ) {
346 App app = new App();
347 // Set up static file service WHAT IS THE HIERARCHY HERE
348 staticFileLocation(sFileLocation);
349
350 // GET '/' returns the index page
351 // (Leaving this alone, at least for now.)
352 get("/", (req, res) -> {
353 res.redirect("/index.html");
354 return "";
355 });
356
357 // GET '/data' returns a JSON string with all of the data in
358 // the MySQL database.
359 get("/data", (req, res) -> {
360 String result = app.getAllData();
361 // send a JSON object back
362 res.status(200);
363 res.type("application/json");
364 return result;
365 });
366
367 // POST a new item into the database
368 post("/data", (req, res) -> {
369 // Try to create a Datum from the request object
370 Datum d = app.gson.fromJson(req.body(), Datum.class);
371 String result = app.insertDatum(d);
372 res.status(200);
373 res.type("application/json");
374 return result;
375 });
376
377
378 // Route for RECORDING A LIKE. ":id" is used for getting index,
379 // NEW DATUM IS IDENTICAL FOR EVERYTHING EXCEPT LIKE AND LLDATE
380 post("/data/like/up/:id", (req, res) -> {
381 // Call the update method above with the new datum object
382 Datum d = app.gson.fromJson(req.body(), Datum.class);
383 int idx = Integer.parseInt(req.params("id"));
384 app.updateLike(idx, d.numLikes, d.lastLikeDate, true);
385 return goodData;
386 });
387
388 // Route for RECORDING A DISLIKE. ":id" is used for getting index,
389 // NEW DATUM IS IDENTICAL to like but decrements numlikes instead of incrementing it
390 post("/data/like/down/:id", (req, res) -> {
391 // Call the update method above with the new datum object
392 Datum d = app.gson.fromJson(req.body(), Datum.class);
393 int idx = Integer.parseInt(req.params("id"));
394 app.updateLike(idx, d.numLikes, d.lastLikeDate, false);
395 return goodData;
396 });
397
398
399 GoogleIdTokenVerifier verifier = new GoogleIdTokenVerifier.Builder(transport, jsonFactory)
400 .setAudience(Arrays.asList(CLIENT_ID)).setIssuer("https://accounts.google.com")
401 // Or, if multiple clients access the backend:
402 //.setAudience(Arrays.asList(CLIENT_ID_1, CLIENT_ID_2, CLIENT_ID_3))
403 .build();
404
405
406 }
407}