My Dumb App

07/13/2017

How to set up MySQL

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';

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&amp;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

07/13/2017

How to set up MySQL

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

Deployment

Done