Foreword: This small project uses Java Tomcat local server to realize the function of reading SQL Server data and comparing the input account password with the existing account password in the database. Java source code download link: (Resources pending review)

1 System overview

1.1 System Introduction

Based on the WeChat applet, we can easily view school notices, public announcements, school news and academic lectures in the school information portal.

1.2 Glossary

The terminology involved in this document is shown in Table 1.

Table 1 Detailed design glossary

Sequence Number	Terms or abbreviations	Descriptive Definition
1	WeChat Applet	The WeChat applet is an app that can be used without downloading or installing it. Users can open the app with a sweep or a search of WeChat.
2	data base	A database is a "warehouse that organizes, stores, and manages data in accordance with the data structure". Is a collection of organized, shared, and uniformly managed large amounts of data that has been stored on a computer for a long time.
3	Information Portal	Information portal refers to a single entry to access key information inside and outside the organization by using a web browser, to solve the problem of information overload, and to build an intranet environment using advanced search and index technology to search and obtain information from different information systems and the Internet.
4	Reptiles	A crawler is a program or script that automatically grabs information from the World Wide Web according to certain rules. Other names that are not commonly used are ants, automatic indexing, simulation programs, or worms.
5	PM	Project Manager, Project Manager
6	Logical Layer	The logical layer of the applet development framework uses the JavaScript engine to provide the applet with the environment in which the developer's JavaScript code runs and the unique functionality of the WeChat applet. The logical layer processes the data and sends it to the view layer, receiving event feedback from the view layer.
7	View Layer	The view layer of the framework is written by WXML and WXSS and is presented by components. Reflects data from the logical layer as a view and sends events from the view layer to the logical layer.

1.3 System Running Environment

1. Software environment:

The software environment required by the system is shown in Table 2.

Table 2 Software environment for system operation

classification	Name	Edition	Languages
PC Operating System	Windows 10	Family Chinese Version	Simplified Chinese
Database Platform	Microsoft SQL Server	2019	Sql
Server Side	IntelliJ IDEA	2020.1	Java
	Tomcat	9.0.44
development environment	Java	14.0.1	Java

1. hardware platform

The hardware platform on which the system runs is shown in Table 3.

Table 3 Hardware Platform for System Running

Device Name	Equipment Requirements
PC	Install Java 14.0.1 and above, corresponding Apache Tomcat, Microsoft SQL Server database 2019 and above

1.4 Development Environment

The program development environment is shown in Table 4.

Table 4 Program Development Environment

classification	Name	Edition	Languages
PC Operating System	Windows 10	Family Chinese Version	Simplified Chinese
Development Platform	IntelliJ IDEA	2020.1	Java
Development Platform	Tomcat	9.0.44	Java
Database Platform	Microsoft SQL Server	2019	Sql

2 Module Design

2.1 Module 2 Java Tomcat Server Module

2.1.1 Design Drawing

The design diagram of the module is shown in Figure 1.

Figure 1 Java Tomcat server module design

2.1.2 Functional Description

This part contains nine categories, which are the bridge module between the database and the WeChat applet, and set up the server as the communication center between the database and the WeChat applet.

2.1.3 Input Data

If you need to verify the user's login name and password, you need to pass both username and password data when the GET specifies the URL.

2.1.4 Output Data

If the user logon authentication interface is accessed, success or error is returned based on the data passed by the user.

If the interface is accessed for each type of notification, the notification data in JSON format is returned, including the title of the information, the date of publication, the article body article, and the URL address image of the picture contained in the article.

2.1.5 Data Design

The data design for this section is shown in Table 5.

Table 5 Java Tomcat Server Module Data Design

Name	data structure	Element type	function
information	ArrayList	String	Store information obtained from the database
ct	Database	Table	Reading data from a database
jsonOutput	String	char	Data in JSON format for WeChat applets to read

2.1.6 Algorithms and Processes

1.  **if** Access Notification Getting Interface:  {
2.    	Get Database Connection  
3.   	Reads information from a specified table in the database  
4.   	Store data in the corresponding format based on different column names List  
5.   	take List Convert data in to JSON format  
6.   **if** The interface is GET or POST Format access:  
7.       Output on Web Page JSON Format information  }
8.   **if** Access login authentication interface:  {
9.   	Get Database Connection  
10.  	Get the information passed by the front end  
11.  	Compare the information passed to the information in the database  
12.  	**if** Compare Success:  
13.      	Output on Web Page success Information  
14.  	**else**:  
15.      	Output on Web Page error Information }

3.4.7 Function Implementation

The functional description for this section is shown in Table 6.

Table 6 Description of Java Tomcat server module functions

Function Name	function	parameter	Return value	Algorithmic Description	global variable
informationDatabase	Constructor	String lableName	None	Complete linking to the database	None
getInformation	Get notification information and store it in Notification array	None	Notification String in JSON Format	Gets information from a specified table in the database, stores it in different columns, and converts it to JSON format	None
Information	Constructor	title of the message, date of publication, article body and URL address image of the picture in the article	None	Assignment statement	None
doGet	Messages from webpages and responses	HttpServletRequest request, HttpServletResponse response	None	Respond appropriately when user accesses using GET	None
doGet	Messages from webpages and responses	HttpServletRequest request, HttpServletResponse response	None	Give a response when the user accesses it using POST	None
userDatabase	Constructor	None	None	Assignment statement	None
checkUser	Confirm login information	User login username, user login password	If found, return the object User, otherwise return null	Compare the information passed in with the database information by querying, if found, return information, otherwise return null	None
getTitle	assignment	None	title	Assignment statement	None
setTitle	Value	title	None	Assignment statement	None
getDate	assignment	None	date	Assignment statement	None
setDate	Value	date	None	Assignment statement	None
getArticle	assignment	None	article	Assignment statement	None
setArticle	Value	article	None	Assignment statement	None
getImage	Value	None	image	Assignment statement	None
setImage	assignment	image	None	Assignment statement	None
getUsername	assignment	None	username	Assignment statement	None
setUsername	Value	username	None	Assignment statement	None
getPassword	assignment	None	password	Assignment statement	None
setPassword	Value	password	None	Assignment statement	None

Code:

/**
 *  Name:       Information.java
 *  Author:     LIN Guocheng
 *  Date:       2021-3-28
 *  Function:   Title, publishing time, and article body for accessing in-school notifications.
 */
public class Information {

    private String title;
    private String date;
    private String article;
    private String[] image;

    public Information(String title, String date, String article, String[] image) {
        this.title = title;
        this.date = date;
        this.article = article;
        this.image = image;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public String getArticle() {
        return article;
    }

    public void setArticle(String article) {
        this.article = article;
    }

    public void setImage(String[] image) {
        this.image = image;
    }

    public String[] getImage() {
        return image;
    }
}

package myServlet;

import java.io.IOException;
import java.io.Writer;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import database.informationDatabase;



public class announcementServlet extends HttpServlet {

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        response.setContentType("text/html;charset=utf-8");
        //Set response header to allow ajax cross-domain access
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setHeader("Access-Control-Allow-Methods", "GET,POST");

        Writer out = response.getWriter();
        String jsonOutput = "";
        informationDatabase nd = new informationDatabase("schoolAnnouncements");
        try {
            jsonOutput = nd.getInformation();
            out.write(jsonOutput);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        out.flush();
    }
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doGet(request,response);

    }

}

package database;

import model.Information;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import com.alibaba.fastjson.*;



public class informationDatabase {
    String lableName = "";
    Connection ct = null;
    PreparedStatement pestmt = null;
    String jsonOutput = "";
    private List<Information> information = new ArrayList<Information>();
    public informationDatabase(String lableName) {
        try {
            this.lableName = lableName;
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            ct = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=schoolNews,""Enter database user name here,""Enter database password here";
            if (ct != null) {
                System.out.println("Database Connection Successful");
            } else {
                System.out.println("Database Connection Failure");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // Get notification information and store it in Notification array
    public String getInformation() throws SQLException {
        try {
            System.out.println("Packing to send in-school notifications");

            pestmt = ct.prepareStatement("select * from " + this.lableName + " order by date desc");
            ResultSet rs = pestmt.executeQuery(); // Place the query results of the database response in rs

            while(rs.next()) {

            	//System.out.println(rs.getString(1)+ ","); 	// Title
            	//System.out.println(rs.getString(2)+",");
            	//System.out.println(rs.getString(3)+",");
                String[] imgUrls = rs.getString(4).split(","); // Separate image strings into arrays by commas
                Information information = new Information(rs.getString(1),rs.getString(2),rs.getString(3),imgUrls);
                this.information.add(information);
            }
            jsonOutput = JSON.toJSONString(information);
            System.out.println(jsonOutput);
            return jsonOutput;
        } catch (Exception e) {
            e.printStackTrace();
            return "Transformation JSON error";
        } finally {
            ct.close();
            pestmt.close();
        }

    }


}

package database;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.User;

public class userDatabase {
    Connection ct = null;
    PreparedStatement pestmt = null;
    public userDatabase(){
        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=schoolNews,""database username,""database password";
            if(ct != null){
                System.out.println("Database Connection Successful");
            }
            else{
                System.out.println("Database Connection Failure");
            }
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    // Confirm login information
    public User checkUser(String username,String password) throws SQLException{
        try{
            System.out.println("Confirming login information");
            pestmt=ct.prepareStatement("select * from [User] where convert(nvarchar(255),username)=? and convert(nvarchar(255),password)=?");
            pestmt.setString(1, username);
            pestmt.setString(2, password);
            ResultSet rs=pestmt.executeQuery(); // Place the query results of the database response in rs
            System.out.println("The database response results are:" + rs.toString());

            User user = new User();

            while(rs.next()){
                user.setUsername(rs.getString(1));//First property
                user.setPassword(rs.getString(2));//Second attribute
                System.out.println("User information is:" + user.getUsername() + " " + user.getPassword());
                return user;	///Return object when found
            }
            return null;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }finally{
            ct.close();
            pestmt.close();
        }
    }
}

public class User {
    private String username;
    private String password;
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
        public void setPassword(String password) {
        this.password = password;
    }

}

package myServlet;

import java.io.IOException;
import java.io.Writer;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import model.User;
import database.userDatabase;


public class userServlet extends HttpServlet {

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        response.setContentType("text/html;charset=utf-8");
        //Set response header to allow ajax cross-domain access
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setHeader("Access-Control-Allow-Methods", "GET,POST");

        //Get the parameter values of the WeChat applet get and print them
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        System.out.println("The content obtained in the WeChat applet is: username:" + username + " password:" + password);

        userDatabase userDatabase =new userDatabase(); // Establish input information comparison objects
        HttpSession session=request.getSession(); // Create Save Information Object
        User user=(User) session.getAttribute("user");

        if(user == null){//First Entry
            try {
                user= userDatabase.checkUser(username, password);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }///If the account password is correct, throw the returned object to the user and the incorrect object is empty
        }

        session.setAttribute("user", user);///Save Objects
        Writer out = response.getWriter();
        if(user!=null){///Object, correct username password
            out.write("success");//Return results to applet
        }else{//Object is empty
            out.write("error");
        }
        out.flush();
    }
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doGet(request,response);

    }

}

2.1.8 Relationship between global data structure and the module

This module is the central module for the communication between the database and the WeChat applet. The communication with the database is through JDBC. The communication with the WeChat applet depends on the active access of the WeChat applet.

2.2 Database Design

2.2.1 Databases and Datasheets

The database system used in this system is Microsoft SQL Server 2019 database, which contains tables
User, school Announcements, school Articles, school Lectures, and school News
The five tables are used for user login authentication, announcement, in-school announcement, academic lecture and in-school news storage.

2.2.2 Data structure design

In the five tables, the data structure of the User table is designed as shown in Table 7, School Announcements, School Articles.
The data structure design of the schoolLectures and schoolNews tables is shown in Table 8.

Table 7 Data structure design of User table

Column Name	data type	Allow Null Values
username	nchar(10)	√
password	ntext	√

Table 8 Data structure design of school Announcements, school Articles, school Lectures, and school News tables

Column Name	data type	Allow Null Values
title	nvarchar(50)	√
date	nvarchar(50)	√
article	ntext	√
image	ntext	√