Web system construction (super basic) ②: AP server construction and basic operation and [Web system construction (super basic) ③: DB server construction and basic operation] Build a Web system using the Web / AP server and DB server built at (https://qiita.com/MARTOON/items/b037ba8161790c874e94).
--AP server - EC2：t2.micro - OS：Red Hat Enterprise Linux 8 (HVM), SSD Volume Type --Disk: General-purpose SSD (GP2) 10GB - Tomcat：Apache Tomcat 9 - Java：JDK 1.8 --DB server - EC2：t2.micro - OS：Red Hat Enterprise Linux 8 (HVM), SSD Volume Type --Disk: General-purpose SSD (GP2) 10GB - MySQL：MySQL 8
The security group settings are nice.
Follow the procedure below.
Ec2 on DB server-Login as user
Switch to root user
$ sudo su -
Log in to MySQL
#mysql -uroot -ppassword
Confirmation of authorized users
> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
Creating a connection user
> CREATE USER 'appuser'@'%' IDENTIFIED BY 'appuser';
Change appuser permissions
> GRANT ALL ON *.* TO 'appuser'@'%';
Make sure the appuser is ready to connect from everywhere
> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| appuser | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
Driver download
# curl -O https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.12/mysql-connector-java-8.0.12.jar
Place the driver in the tomcat lib directory
# mv mysql-connector-java-8.0.12.jar /opt/apache-tomcat-9.0.30/lib/
Put a symbolic link on the driver
# cd /opt/apache-tomcat-9.0.30/lib/
# ln -s mysql-connector-java-8.0.12.jar mysql-connector-java.jar
Get SQL information to register sample data in database
# curl -O https://downloads.mysql.com/docs/world.sql.zip
unzip installation
# yum install -y unzip
Unzip SQL file
# unzip world.sql.zip
Input sample data to DB
# mysql -uappuser -pappuser < world.sql
Log in to MySQL
# mysql -uappuser -pappuser
Make sure the world database has been added
> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
Check the tables contained in the world database
> use world;
> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
Each table definition is as follows,
> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
> desc country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | decimal(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | decimal(3,1) | YES | | NULL | |
| GNP | decimal(10,2) | YES | | NULL | |
| GNPOld | decimal(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
> desc countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | decimal(4,1) | NO | | 0.0 | |
+-------------+---------------+------+-----+---------+-------+
Build a simple Web application using JSP and Java program and run it on the AP server. If you enter the country name on the Web screen and search, you can find the 5 major cities and their population, as well as the official languages and their usage ratios. It is a program that displays while performing a DB search.
world.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<!DOCTYPE html>
<html>
<head>
<title>requestForm</title>
</head>
<body>
<p>Search for major cities and official languages in each country</p>
<%--Send text with GET method--%>
<form action="./WorldServlet">
<p>
Please enter the country name (in English):<input type="text" name="text1"><br>
Example) Enter Japan for Japan
</p>
<input type="submit" value="Search execution">
</form>
</html>
WorldServlet.java
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.NumberFormat;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/WorldServlet")
public class WorldServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
*constructor.
*/
public WorldServlet() {
super();
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String inputText = ""; //Text 1 storage variable
inputText = request.getParameter("text1");
String servername = "172.31.45.98";
String databasename = "world";
String user = "appuser";
String password = "appuser";
String serverencoding = "UTF-8";
String url = "jdbc:mysql://" + servername + "/" + databasename + "?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B9:00&rewriteBatchedStatements=true";
Connection con = null;
try {
Class.forName( "com.mysql.cj.jdbc.Driver" ).newInstance();
con = DriverManager.getConnection( url, user, password );
Statement stat = con.createStatement();
String sqlStr1 = "select country.name, city.name, city.population from city inner join country on city.countrycode=country.code where country.name='" + inputText + "' order by city.population desc limit 5;";
String sqlStr2 = "select country.name, countrylanguage.language, countrylanguage.percentage from country inner join countrylanguage on country.code=countrylanguage.countrycode where country.name='" + inputText + "' order by countrylanguage.percentage desc limit 1;";
System.out.println("sqlStr1: " + sqlStr1);
System.out.println("sqlStr2: " + sqlStr2);
ResultSet resultset1 = stat.executeQuery( sqlStr1 );
String country_name = null;
String city_name = null;
Integer city_population = 0;
String countrylanguage_language = null;
Double countrylanguage_percentage = 0.0;
//Create a NumberFormat instance
NumberFormat nfNum = NumberFormat.getNumberInstance(); //Comma separated format
NumberFormat nfPer = NumberFormat.getPercentInstance(); //Percentage format
//Setting the contents to be output to the screen
//Set that the output content is HTML
response.setContentType("text/html");
//UTF the character code of the output screen-Set to 8
response.setCharacterEncoding("UTF-8");
//Get Writer class instance to output to screen
PrintWriter pw = response.getWriter();
//Output HTML
pw.println("<html>");
pw.println("<head>");
pw.println("<title>Input result</title>");
pw.println("</head>");
pw.println("<body>");
pw.println("<h1>search results</h1>");
pw.println("<h2>" + inputText + "Major cities and their populations are:</h2>");
pw.println("<table border='1'>");
pw.println("<tr>");
pw.println("<th>Name of a city</th>");
pw.println("<th>population</th>");
pw.println("</tr>");
while( resultset1.next() )
{
/*getString()The method is the field name specified in the argument(Column)Get the value of as a String*/
city_name = resultset1.getString("city.name");
city_population = resultset1.getInt("city.population");
System.out.print("Name of a city:" + city_name);
System.out.print("Urban population:" + city_population);
pw.println("<tr>");
pw.println("<th>"+ city_name +"</th>");
pw.println("<th>"+ nfNum.format(city_population) +"Man</th>");
pw.println("</tr>");
}
pw.println("</table>");
resultset1.close();
ResultSet resultset2 = stat.executeQuery( sqlStr2 );
pw.println("<h2>" + inputText + "The official languages of and their usage rates are as follows:</h2>");
pw.println("<table border='1'>");
pw.println("<tr>");
pw.println("<th>Official terminology</th>");
pw.println("<th>Usage ratio</th>");
pw.println("</tr>");
while( resultset2.next() )
{
/*getString()The method is the field name specified in the argument(Column)Get the value of as a String*/
countrylanguage_language = resultset2.getString("countrylanguage.language");
countrylanguage_percentage = resultset2.getDouble("countrylanguage.percentage");
System.out.print("Official terminology:" + countrylanguage_language);
System.out.print("Usage ratio:" + countrylanguage_percentage);
pw.println("<tr>");
pw.println("<th>"+ countrylanguage_language +"</th>");
pw.println("<th>"+ nfPer.format(countrylanguage_percentage / 100) +"</th>");
pw.println("</tr>");
}
pw.println("</table>");
resultset2.close();
stat.close();
con.close();
pw.println("</body>");
pw.println("</html>");
}
catch( SQLException e ){
/*Error message output*/
System.out.println( "Connection Failed. : " + e.toString() );
/*I'll throw an exception*/
try {
throw new Exception();
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}catch (ClassNotFoundException e){
/*Error message output*/
System.out.println("The driver could not be loaded" + e);
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
try{
if( con != null ){
con.close();
}
}
catch(Exception e){
/*Error message output*/
System.out.println( "Exception2! :" + e.toString() );
/*I'll throw an exception*/
try {
throw new Exception();
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
}
}
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
version="3.1">
<servlet>
<servlet-name>WorldServlet</servlet-name>
<servlet-class>WorldServlet.WorldServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>WorldServlet</servlet-name>
<url-pattern>/*</url-pattern>
</servlet-mapping>
</web-app>
Place the application as follows.
/opt/apache-tomcat-9.0.30/webapps/world
world
├── WEB-INF
│ ├── lib
│ │ └── World.jar
│ └── web.xml
└── world.jsp
Restart tomcat service
# service tomcat restart
Redirecting to /bin/systemctl start tomcat.service
From the browser to the web server "Public DNS":8080/world/world.Connect to "jsp"
Confirm that the created Web page is displayed and the processing is performed correctly.