· 9 years ago · Aug 09, 2017, 04:54 PM
1package net.coderodde.web.db.fun.controllers;
2
3import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
4import java.io.IOException;
5import java.io.PrintWriter;
6import java.sql.Connection;
7import java.sql.PreparedStatement;
8import java.sql.SQLException;
9import javax.servlet.ServletException;
10import javax.servlet.annotation.WebServlet;
11import javax.servlet.http.HttpServlet;
12import javax.servlet.http.HttpServletRequest;
13import javax.servlet.http.HttpServletResponse;
14import static net.coderodde.web.db.fun.controllers.DBUtils.close;
15
16/**
17 * This controller is responsible for creating new persons.
18 *
19 * @author Rodion "rodde" Efremov
20 * @version 1.6 (Aug 8, 2017)
21 */
22@WebServlet(name = "AddPersonController", urlPatterns = {"/add_person"})
23public class AddPersonController extends HttpServlet {
24
25 /**
26 * The SQL command for inserting a person.
27 */
28 private static final String INSERT_PERSON_SQL =
29 "INSERT INTO funny_persons (first_name, last_name, email) VALUES " +
30 "(?, ?, ?);";
31
32 /**
33 * Handles the HTTP <code>GET</code> method.
34 *
35 * @param request the servlet request.
36 * @param response the servlet response.
37 * @throws ServletException if a servlet-specific error occurs.
38 * @throws IOException if an I/O error occurs.
39 */
40 @Override
41 protected void doGet(HttpServletRequest request, HttpServletResponse response)
42 throws ServletException, IOException {
43 response.setContentType("text/html;charset=UTF-8");
44 try (PrintWriter out = response.getWriter()) {
45 out.println("Please use the POST method!");
46 }
47 }
48
49 /**
50 * Handles the HTTP <code>POST</code> method.
51 *
52 * @param request the servlet request.
53 * @param response the servlet response.
54 * @throws ServletException if a servlet-specific error occurs.
55 * @throws IOException if an I/O error occurs.
56 */
57 @Override
58 protected void doPost(HttpServletRequest request, HttpServletResponse response)
59 throws ServletException, IOException {
60 response.setContentType("text/html;charset=UTF-8");
61
62 try (PrintWriter out = response.getWriter()) {
63 String firstName = request.getParameter("first_name");
64 String lastName = request.getParameter("last_name");
65 String email = request.getParameter("email");
66
67 if (firstName.isEmpty()) {
68 out.println("The first name is empty.");
69 return;
70 }
71
72 if (lastName.isEmpty()) {
73 out.println("The last name is empty.");
74 return;
75 }
76
77 if (email.isEmpty()) {
78 out.println("The email is empty.");
79 return;
80 }
81
82 MysqlDataSource mysql = DefaultDataSourceCreator.create();
83 Connection connection = null;
84 PreparedStatement statement = null;
85
86 try {
87 connection = mysql.getConnection();
88 statement = connection.prepareStatement(INSERT_PERSON_SQL);
89 statement.setString(1, firstName);
90 statement.setString(2, lastName);
91 statement.setString(3, email);
92 statement.executeUpdate();
93 } catch (SQLException ex) {
94 out.println("Error: " + ex.getMessage());
95 } finally {
96 close(null, statement, connection);
97 }
98 }
99 }
100
101 /**
102 * Returns a short description of the servlet.
103 *
104 * @return a String containing servlet description
105 */
106 @Override
107 public String getServletInfo() {
108 return "This servlet adds new persons to the database.";
109 }
110}
111
112package net.coderodde.web.db.fun.controllers;
113
114import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
115import java.io.IOException;
116import java.io.PrintWriter;
117import java.sql.Connection;
118import java.sql.PreparedStatement;
119import java.sql.SQLException;
120import javax.servlet.ServletException;
121import javax.servlet.annotation.WebServlet;
122import javax.servlet.http.HttpServlet;
123import javax.servlet.http.HttpServletRequest;
124import javax.servlet.http.HttpServletResponse;
125import static net.coderodde.web.db.fun.controllers.DBUtils.close;
126
127/**
128 * This controller creates the database if it is not yet created.
129 *
130 * @author Rodion "rodde" Efremov
131 * @version 1.6 (Aug 8, 2017)
132 */
133@WebServlet(name = "CreateDatabaseController", urlPatterns = {"/create"})
134public class CreateDatabaseController extends HttpServlet {
135
136 /**
137 * Creates a new database if not already created.
138 */
139 private static final String CREATE_DATABASE_SQL =
140 "CREATE DATABASE IF NOT EXISTS funny_db;";
141
142 /**
143 * Switches to 'funny_db'.
144 */
145 private static final String USE_DATABASE_SQL = "USE funny_db";
146
147 /**
148 * Creates the table if not already created.
149 */
150 private static final String CREATE_TABLE_SQL =
151 "CREATE TABLE IF NOT EXISTS funny_persons (n" +
152 "id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,n" +
153 "first_name VARCHAR(40) NOT NULL,n" +
154 "last_name VARCHAR(40) NOT NULL,n" +
155 "email VARCHAR(50) NOT NULL,n" +
156 "created TIMESTAMP);";
157
158 /**
159 * If not yet created, this request creates the database and the table.
160 *
161 * @param request the servlet request.
162 * @param response the servlet response.
163 * @throws ServletException if a servlet-specific error occurs.
164 * @throws IOException if an I/O error occurs.
165 */
166 protected void processRequest(HttpServletRequest request, HttpServletResponse response)
167 throws ServletException, IOException {
168 response.setContentType("text/html;charset=UTF-8");
169
170 try (PrintWriter out = response.getWriter()) {
171 MysqlDataSource mysql = DefaultDataSourceCreator.create();
172 Connection connection = null;
173 PreparedStatement statement = null;
174 boolean error = false;
175
176 try {
177 connection = mysql.getConnection();
178 statement = connection.prepareStatement(CREATE_DATABASE_SQL);
179 statement.execute();
180
181 statement = connection.prepareStatement(USE_DATABASE_SQL);
182 statement.execute();
183
184 statement = connection.prepareStatement(CREATE_TABLE_SQL);
185 statement.execute();
186 } catch (SQLException ex) {
187 error = true;
188 out.println("Error: " + ex.getMessage());
189 } finally {
190 close(null, statement, connection);
191 }
192
193 if (!error) {
194 out.println("Database 'funny_db' is created!");
195 }
196 }
197 }
198
199 /**
200 * Handles the HTTP <code>GET</code> method.
201 *
202 * @param request the servlet request.
203 * @param response the servlet response.
204 * @throws ServletException if a servlet-specific error occurs.
205 * @throws IOException if an I/O error occurs.
206 */
207 @Override
208 protected void doGet(HttpServletRequest request, HttpServletResponse response)
209 throws ServletException, IOException {
210 processRequest(request, response);
211 }
212
213 /**
214 * Handles the HTTP <code>POST</code> method.
215 *
216 * @param request the servlet request.
217 * @param response the servlet response.
218 * @throws ServletException if a servlet-specific error occurs.
219 * @throws IOException if an I/O error occurs.
220 */
221 @Override
222 protected void doPost(HttpServletRequest request, HttpServletResponse response)
223 throws ServletException, IOException {
224 processRequest(request, response);
225 }
226
227 /**
228 * Returns a short description of the servlet.
229 *
230 * @return a String containing servlet description.
231 */
232 @Override
233 public String getServletInfo() {
234 return "Creates the database and the table.";
235 }
236}
237
238package net.coderodde.web.db.fun.controllers;
239
240import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
241import java.sql.Connection;
242import java.sql.PreparedStatement;
243import java.sql.ResultSet;
244import java.sql.SQLException;
245
246/**
247 * This class contains some common utilities for dealing with databases.
248 *
249 * @author Rodion "rodde" Efremov
250 * @version 1.6 (Aug 8, 2017)
251 */
252public final class DBUtils {
253
254 /**
255 * Creates and sets a MySQL data source.
256 *
257 * @param user the user name.
258 * @param password the password.
259 * @param url the database URL.
260 * @param port the database port.
261 * @param serverName the name of the server.
262 *
263 * @return the MySQL data source.
264 */
265 public static MysqlDataSource getMysqlDataSource(String user,
266 String password,
267 String url,
268 int port,
269 String serverName) {
270 MysqlDataSource dataSource = new MysqlDataSource();
271
272 if (user != null) {
273 dataSource.setUser(user);
274 }
275
276 if (password != null) {
277 dataSource.setPassword(password);
278 }
279
280 if (url != null) {
281 dataSource.setURL(url);
282 }
283
284 if (port >= 0) {
285 dataSource.setPort(port);
286 }
287
288 if (serverName != null) {
289 dataSource.setServerName(serverName);
290 }
291
292 return dataSource;
293 }
294
295 /**
296 * Closes a {@code ResultSet}.
297 *
298 * @param resultSet the result set to close.
299 */
300 public static void close(ResultSet resultSet) {
301 try {
302 if (resultSet != null) {
303 resultSet.close();
304 }
305 } catch (SQLException ex) {
306 throw new RuntimeException("Could not close a ResultSet.", ex);
307 }
308 }
309
310 /**
311 * Closes a {@code PreparedStatement}.
312 *
313 * @param preparedStatement the prepared statement to close.
314 */
315 public static void close(PreparedStatement preparedStatement) {
316 try {
317 if (preparedStatement != null) {
318 preparedStatement.close();
319 }
320 } catch (SQLException ex) {
321 throw new RuntimeException(
322 "Could not close a PreparedStatement.",
323 ex);
324 }
325 }
326
327 /**
328 * Closes a {@code Connection}.
329 *
330 * @param connection the connection to close.
331 */
332 public static void close(Connection connection) {
333 try {
334 if (connection != null) {
335 connection.close();
336 }
337 } catch (SQLException ex) {
338 throw new RuntimeException(
339 "Could not close a Connection.",
340 ex);
341 }
342 }
343
344 /**
345 * Closes the data base related resources.
346 *
347 * @param resultSet the result set to close.
348 * @param preparedStatement the prepared statement to close.
349 * @param connection the connection to close.
350 */
351 public static void close(ResultSet resultSet,
352 PreparedStatement preparedStatement,
353 Connection connection) {
354 close(resultSet);
355 close(preparedStatement);
356 close(connection);
357 }
358}
359
360package net.coderodde.web.db.fun.controllers;
361
362import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
363
364public class DefaultDataSourceCreator {
365
366 public static MysqlDataSource create() {
367 MysqlDataSource mysql =
368 DBUtils.getMysqlDataSource(
369 "root",
370 "your_password",
371 "jdbc:mysql://localhost:3306/funny_db",
372 -1,
373 null);
374
375 return mysql;
376 }
377}
378
379package net.coderodde.web.db.fun.controllers;
380
381import java.io.IOException;
382import javax.servlet.FilterChain;
383import javax.servlet.FilterConfig;
384import javax.servlet.ServletException;
385import javax.servlet.ServletRequest;
386import javax.servlet.ServletResponse;
387import javax.servlet.http.HttpServletResponse;
388
389public class MyFilter implements javax.servlet.Filter {
390
391 private final int id;
392 private final String firstName;
393
394 public MyFilter(int id, String firstName) {
395 this.id = id;
396 this.firstName = firstName;
397 }
398
399 @Override
400 public void init(FilterConfig filterConfig)
401 throws ServletException {
402
403 }
404
405 @Override
406 public void doFilter(ServletRequest request,
407 ServletResponse response,
408 FilterChain chain)
409 throws IOException, ServletException {
410 ((HttpServletResponse) response)
411 .sendRedirect("/DBWebFun/show/" + id + "/" + firstName);
412 }
413
414 @Override
415 public void destroy() {
416
417 }
418}
419
420package net.coderodde.web.db.fun.controllers;
421
422import com.google.gson.Gson;
423import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
424import java.io.IOException;
425import java.io.PrintWriter;
426import java.sql.Connection;
427import java.sql.PreparedStatement;
428import java.sql.ResultSet;
429import java.sql.SQLException;
430import javax.servlet.ServletException;
431import javax.servlet.annotation.WebServlet;
432import javax.servlet.http.HttpServlet;
433import javax.servlet.http.HttpServletRequest;
434import javax.servlet.http.HttpServletResponse;
435import net.coderodde.web.db.fun.model.FunnyPerson;
436
437import static net.coderodde.web.db.fun.controllers.DBUtils.close;
438
439/**
440 * This controller is responsible for viewing persons.
441 *
442 * @author Rodion "rodde" Efremov
443 * @version 1.6 (Aug 8, 2017)
444 */
445@WebServlet(name = "ShowPersonController", urlPatterns = {"/show/*"})
446public class ShowPersonController extends HttpServlet {
447
448 private static final String GET_USER_BY_ID_SQL =
449 "SELECT * FROM funny_persons WHERE id = ?;";
450
451 /**
452 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
453 * methods.
454 *
455 * @param request the servlet request.
456 * @param response the servlet response.
457 * @throws ServletException if a servlet-specific error occurs.
458 * @throws IOException if an I/O error occurs.
459 */
460 protected void processRequest(HttpServletRequest request, HttpServletResponse response)
461 throws ServletException, IOException {
462 response.setContentType("text/html;charset=UTF-8");
463
464 try (PrintWriter out = response.getWriter()) {
465 MysqlDataSource mysql = DefaultDataSourceCreator.create();
466 String path = request.getPathInfo();
467
468 if (path.startsWith("/")) {
469 path = path.substring(1);
470 }
471
472 String[] tokens = path.split("/");
473
474 if (tokens.length == 0) {
475 out.println("At least the user ID is required.");
476 return;
477 }
478
479 String idString = tokens[0];
480 int id = -1;
481
482 try {
483 id = Integer.parseInt(idString);
484 } catch (NumberFormatException ex) {
485 out.println(idString + ": not an integer.");
486 return;
487 }
488
489 FunnyPerson person = new FunnyPerson();
490
491 Connection connection = null;
492 PreparedStatement statement = null;
493 ResultSet resultSet = null;
494
495 try {
496 connection = mysql.getConnection();
497 statement = connection.prepareStatement(GET_USER_BY_ID_SQL);
498 statement.setInt(1, id);
499 resultSet = statement.executeQuery();
500
501 if (!resultSet.next()) {
502 close(resultSet, statement, connection);
503 out.println("{"status": "error"}");
504 return;
505 }
506
507 String matchFirstName = null;
508
509 if (tokens.length == 2) {
510 matchFirstName = tokens[1];
511 }
512
513 if (!matchFirstName.equals(resultSet.getString("first_name"))) {
514 MyFilter myFilter =
515 new MyFilter(id, resultSet.getString("first_name"));
516
517 myFilter.doFilter(request, response, null);
518 return;
519 }
520
521 person.setId(resultSet.getInt("id"));
522 person.setFirstName(resultSet.getString("first_name"));
523 person.setLastName(resultSet.getString("last_name"));
524 person.setEmail(resultSet.getString("email"));
525 person.setCreated(resultSet.getDate("created"));
526
527 Gson gson = new Gson();
528 out.println(gson.toJson(person));
529 } catch (SQLException ex) {
530 throw new RuntimeException("SQLException thrown.", ex);
531 } finally {
532 close(resultSet, statement, connection);
533 }
534 }
535 }
536
537 private void handleInvalidRequest(HttpServletRequest request,
538 HttpServletResponse response) {
539
540 }
541
542 /**
543 * Handles the HTTP <code>GET</code> method.
544 *
545 * @param request servlet request
546 * @param response servlet response
547 * @throws ServletException if a servlet-specific error occurs
548 * @throws IOException if an I/O error occurs
549 */
550 @Override
551 protected void doGet(HttpServletRequest request, HttpServletResponse response)
552 throws ServletException, IOException {
553 processRequest(request, response);
554 }
555
556 /**
557 * Handles the HTTP <code>POST</code> method.
558 *
559 * @param request servlet request
560 * @param response servlet response
561 * @throws ServletException if a servlet-specific error occurs
562 * @throws IOException if an I/O error occurs
563 */
564 @Override
565 protected void doPost(HttpServletRequest request, HttpServletResponse response)
566 throws ServletException, IOException {
567 processRequest(request, response);
568 }
569
570 /**
571 * Returns a short description of the servlet.
572 *
573 * @return a String containing servlet description
574 */
575 @Override
576 public String getServletInfo() {
577 return "Shows the user info via ID/first_name";
578 }
579}
580
581package net.coderodde.web.db.fun.model;
582
583import java.sql.Date;
584
585public final class FunnyPerson {
586
587 private int id;
588 private String firstName;
589 private String lastName;
590 private String email;
591 private Date created;
592
593 public int getId() {
594 return id;
595 }
596
597 public String getFirstName() {
598 return firstName;
599 }
600
601 public String getLastName() {
602 return lastName;
603 }
604
605 public String getEmail() {
606 return email;
607 }
608
609 public Date getCreated() {
610 return created;
611 }
612
613 public void setId(int id) {
614 this.id = id;
615 }
616
617 public void setFirstName(String firstName) {
618 this.firstName = firstName;
619 }
620
621 public void setLastName(String lastName) {
622 this.lastName = lastName;
623 }
624
625 public void setEmail(String email) {
626 this.email = email;
627 }
628
629 public void setCreated(Date created) {
630 this.created = created;
631 }
632}