<span role="heading" aria-level="2">移动端疫情展示</span>

一、题目要求

要求开发一款移动端的全世界疫情实时查询系统。

要求将前两周的项目合并为一个完整的项目。

采用统一的数据库。（建议MySQL数据库）

实现从数据采集、数据存储、数据查询（WEB端和移动端）一体全世界实时疫情查询系统。

以本机数据库为服务器端，web端和移动端连接远程数据库实现数据共享，要求数据库表格式统一化。

查询显示当前最新时间的数据，可以查询任一时间任一地点的数据。该系统要求在服务器端发布，可以通过IP地址访问。

二、设计思路

仿照之前的web端程序，连接mysql数据库获取数据，并加以展示

三、程序代码

package com.example.crawlapplication.entity;

public class Info {
     private int id;
     private String province;
     private String Confirmed_num;
     private String Cured_num;
     private String Dead_num;
     private String datetime;
    
    

    
    public Info(int id, String prov***ring confirmed_num, String cured_num, String dead_num, String datetime) {
        this.id = id;
        this.province = province;
        Confirmed_num = confirmed_num;
        Cured_num = cured_num;
        Dead_num = dead_num;
        this.datetime = datetime;
    }




    public Info(String prov***ring confirmed_num, String cured_num, String dead_num, String datetime) {
        this.province = province;
        Confirmed_num = confirmed_num;
        Cured_num = cured_num;
        Dead_num = dead_num;
        this.datetime = datetime;
    }




    public int getId() {
        return id;
    }




    public void setId(int id) {
        this.id = id;
    }




    public String getDatetime() {
        return datetime;
    }




    public void setDatetime(String datetime) {
        this.datetime = datetime;
    }




    public String getProvince() {
        return province;
    }




    public void setProv***ring province) {
        this.province = province;
    }




    public String getConfirmed_num() {
        return Confirmed_num;
    }




    public void setConfirmed_num(String confirmed_num) {
        Confirmed_num = confirmed_num;
    }




    public String getCured_num() {
        return Cured_num;
    }




    public void setCured_num(String cured_num) {
        Cured_num = cured_num;
    }




    public String getDead_num() {
        return Dead_num;
    }




    public void setDead_num(String dead_num) {
        Dead_num = dead_num;
    }






    @Override
    public String toString() {
        return "Info [id=" + id + ", datetime=" + datetime + ", province=" + province + ", Confirmed_num="
                + Confirmed_num + ", Cured_num=" + Cured_num + ", Dead_num=" + Dead_num + "]";
    }




    public Info() {
    }
     
}

info.java

package com.example.crawlapplication.utils;

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


public class DBUtil {
    //���ݿ�URL���˺�����
            private static final String URL="jdbc:mysql://47.94.229.72:3306/crawl?serverTimezone=UTC&characterEncoding=utf-8&autoReconnect=true&&useSSL=false";
            private static final String UNAME="crawl";
            private static final String UPWD="crawl";
            public static PreparedStatement  pstmt=null;
            public static ResultSet  rs = null;
            public static Connection  connection=null;
            
            
     //��ɾ��
     public static boolean executeUpdate(String sql,Object [] params) {
         boolean flag = false;
         try {
             
         //a.�������������ؾ����������
         Class.forName("com.mysql.cj.jdbc.Driver");
         //b.�����ݿ⽨������
         connection = DriverManager.getConnection(URL,UNAME,UPWD);
         
         pstmt = connection.prepareStatement(sql);
         for(int i=0;i<params.length;i++) {
             pstmt.setObject(i+1, params[i]);
         }
         int count=pstmt.executeUpdate();//����ֵ��ʾ����ɾ�ļ�������
         //������
         if(count>0)
         {
             System.out.println("�����ɹ�������");
             flag=true;
         }
         
         }catch(ClassNotFoundException e) {
             e.printStackTrace();
         }catch(SQLException e) {
             e.printStackTrace();
         }catch(Exception e){
             e.printStackTrace();
         }finally {
             try {
                 //�ȿ��ĺ�أ��󿪵��ȹ�
             if(pstmt!=null)pstmt.close();
             if(connection !=null)connection.close();
             }catch(SQLException e) {
                 e.printStackTrace();
             }finally {
                 
             }
         }
        return flag;
     }
    //��
     public static ResultSet executeQuery(String sql,Object [] params) {
         
                 try {
                     
                 //a.�������������ؾ����������
                 Class.forName("com.mysql.cj.jdbc.Driver");
                 //b.�����ݿ⽨������
                 connection = DriverManager.getConnection(URL,UNAME,UPWD);
                 
                 pstmt = connection.prepareStatement(sql);
                 if(params!=null) {
                 for(int i=0;i<params.length;i++) {
                     pstmt.setObject(i+1, params[i]);
                 }
                 }
                  rs = pstmt.executeQuery();
                  return rs;
                 }catch(ClassNotFoundException e) {
                     e.printStackTrace();
                     return null;
                 }catch(SQLException e) {
                     e.printStackTrace();
                     return null;
                 }catch(Exception e){
                     e.printStackTrace();
                     return null;
                 }
             
        }
}

DBUtil.java

package com.example.crawlapplication.dao;

import com.example.crawlapplication.entity.Info;
import com.example.crawlapplication.utils.DBUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class dao {
    //����������
    public boolean add(Info info) {
        String sql="insert into crawl (province,Confirmed_num,Cured_num,Dead_num,datetime) values (?,?,?,?,?)";
        Object obj[]= {info.getProvince(),info.getConfirmed_num(),info.getCured_num(),info.getDead_num(),info.getDatetime()};
        return DBUtil.executeUpdate(sql, obj);
    }
    
    //��ѯ����
     public List<Info> Query() {
         List<Info> infos=new ArrayList<>();
         Info info= null;
           ResultSet rs = null; 
         try {
             String sql="select * from crawl  " ;
             Object [] params= {};
             rs=DBUtil.executeQuery(sql, params);
             while(rs.next()) {
                 int Id=rs.getInt("id");
                 String Province=rs.getString("province");
                 String Confirmed_num=rs.getString("Confirmed_num");
                 String Cured_num=rs.getString("Cured_num");
                 String Dead_num=rs.getString("Dead_num");
                 String Datetime=rs.getString("datetime");
                 info=new Info(Id,Province,Confirmed_num,Cured_num,Dead_num,Datetime);
                 infos.add(info);
             }
         }catch(SQLException e) {
             e.printStackTrace();
         }catch(Exception e) {
             e.printStackTrace();
         }finally {
             try {
                 //�ȿ��ĺ�أ��󿪵��ȹ�
             if(rs!=null)rs.close();
             if(DBUtil.pstmt!=null)DBUtil.pstmt.close();
             if(DBUtil.connection !=null)DBUtil.connection.close();
             }catch(SQLException e) {
                 e.printStackTrace();
             }finally {
                 
             }
            
         }
         return infos;
     }
}

dao.java

package com.example.crawlapplication;

import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;

import androidx.appcompat.app.AppCompatActivity;

public class MainActivity extends AppCompatActivity {
private  Button mCrawl;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        initView();

        initListener();
    }

    private void initListener() {
        mCrawl.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                crawl();
            }
        });
    }

    private void crawl() {

        Intent intent =new Intent(this,ShowActivity.class);
        startActivity(intent);
    }

    private void initView() {
        mCrawl=(Button) this.findViewById(R.id.crawl);
    }


}

MainActivity.java

package com.example.crawlapplication;


import android.app.Activity;
import android.os.Bundle;
import android.webkit.WebChromeClient;
import android.webkit.WebView;

import androidx.annotation.Nullable;

public class ShowActivity extends Activity {
    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.show_activity);

        //
        WebView show=(WebView)this.findViewById(R.id.show);
       show.getSettings().setUseWideViewPort(true);
       show.getSettings().setLoadWithOverviewMode(true);
       show.getSettings().setJavaScriptEnabled(true);
       show.setWebChromeClient( new WebChromeClient());
       show.loadUrl("http://47.94.229.72:8080/Crawl/frame.jsp");
    }


}

ShowActivity.java

四、开发记录

五、项目总结

Android无法直接连接mysql数据库，最好的解决方法是通过异步加载Servlet，从中获取到所需要的数据。