My Dumb App
07/13/2017
How to set up MySQL
-
Login as root. Create a database and table.
CREATE DATABASE web_student_tracker; USE web_student_tracker; CREATE TABLE student ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(45), last_name VARCHAR(45), email VARCHAR(45));
-
Create a new User
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
-
Set permission for the new user
GRANT ALL PRIVILEGES ON web_student_tracker . student TO 'newuser'@'localhost';
we have created a table for our web-app. The web-app will login as a new user who can only manipulate that specific table.
Use JDBC to Access Data from the Database
-
META-INF/context.xml
<?xml version="1.0" encoding="UTF-8"?> <Context path=""> <Resource name="jdbc/web_student_tracker" auth="Container" type="javax.sql.DataSource" maxActive="20" maxIdle="5" maxWait="10000" username="newuser" password="password" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/web_student_tracker?useSSL=false&serverTimezone=UTC"/> </Context>
The file contains login info for the new user to access the database. Still don’t know how the path works.
-
The info above can be injected into a DataSource variable through annotation.
@Resource(name="jdbc/web_student_tracker") private DataSource dataSource;
-
Establish connection and read data to a list.
List<String> students = new LinkedList<>(); Connection myConn; Statement myStmt; ResultSet myRs; try{ myConn = dataSource.getConnecton(); myStmt = myConn.createStatment(); myRs = myStmt.query("select * from student order by last_name"); } while(myRs.next()) { String firstName = myRs.getString("first_name"); String lastName = myRs.getString("last_name"); String email = myRs.getString("email"); Student tempStudent = new Student(firstName, lastName, email); students.add(tempStudent); }
-
A servlet will pass the String list to a JSP page.
doGet (HttpServletRequest request, HttpServletResponse response) { request.setAttribute("STUDENT_LIST", students); RequsetDispatcher dispatcher = request.getRequestDispatch("/list.jsp"); dispatcher.forward(request, response); }
-
Display data on a jsp page
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <table> <tr> <th>First Name</th> <th>Last Name</th> <th>Email</th> </tr> <c:forEach var="tempStudent" items="${STUDENT_LIST}"> <tr> <td>${tempStudent.firstName}</td> <td>${tempStudent.lastName}</td> <td>${tempStudent.email}</td> </tr> </c:forEach> </table>
Deployment
-
Start a new maven project. Add dependency to a pom.xml file
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.11</version> </dependency>
-
Add all the java files. (Better do this on eclipse.)
/src/main/java/com/yifeng/app/file1.java /src/main/java/com/yifeng/app/file2.java /src/main/java/com/yifeng/app/file3.java /src/main/webapp/list.jsp /src/main/META-INF/context.xml
-
Build maven project. (more commands need to be understand!!!!!)
mvn clean build;
-
Deploy the war file on the Tomcat server
#!/bin/bash cp /target/myapp.war /opt/tomcat/webapp