Web system construction (super basic) ④: Web system construction

Purpose

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).

Environmental condition

--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.

Construction procedure

Follow the procedure below.

  1. Make it possible to connect to MySQL on the DB server from the Web / AP server OS.
  2. Make it possible to connect to MySQL on the DB server from the Web / AP server.
  3. Data registration in the database
  4. Application preparation
  5. Operation check

1. Make it possible to connect to MySQL on the DB server from the Web / AP server OS.

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 |
+------------------+-----------+

2. Make it possible to connect to MySQL on the DB server from the Web / AP server.

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

3. Data registration in the database

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     |       |
+-------------+---------------+------+-----+---------+-------+

4. Application preparation

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

5. Operation check

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.

Recommended Posts

Web system construction (super basic) ④: Web system construction
swift CollectionView Super basic usage
Super basic usage of Eclipse
[Super easy] Ruby environment construction