世界疫情可视化开发（四）
今天把剩余的所有的全部开发完了
dao层：访问数据库
package Dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import DBUtil.Util;
import bin.Country;
import bin.Info;
public class Dao {
public List<Country> getAllCountry() {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<Country> countryList = new ArrayList<>();
try {
con = Util.getConnection();
String sql = "select * from yiqing_world order by confirmed_num DESC";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
Country country = new Country();
country.setId(rs.getString(1));
country.setName(rs.getString(2));
country.setConfirmed_num(rs.getString(3));
country.setYisi_num(rs.getString(6));
country.setCured_num(rs.getString(4));
country.setDead_num(rs.getString(5));
country.setDate(rs.getString(7));
countryList.add(country);
}
return countryList;
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
Util.release(con, ps);
}
}
public List<Country> getAllChinaConfirmed() {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<Country> countryList = new ArrayList<>();
try {
con = Util.getConnection();
String sql = "select * from world_";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
Country country = new Country();
country.setName(rs.getString(1));
country.setConfirmed_num(rs.getString(2));
country.setYisi_num(rs.getString(3));
country.setCured_num(rs.getString(4));
country.setDead_num(rs.getString(5));
country.setDate(rs.getString(7));
countryList.add(country);
}
return countryList;
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
Util.release(con, ps);
}
}
// 查询全世界所有的确诊人数
public int countryConfirmedCount() {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = Util.getConnection();
String sql = "select sum(confirmed_num) as count from yiqing_world";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
return rs.getInt("count");
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
Util.release(con, ps);
}
}
}
Servlet：
获取世界所有的数据
package Servlet;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.catalina.Session;
import com.google.gson.Gson;
import Dao.Dao;
import bin.Country;
/**
* Servlet implementation class GetAllWorldConfirmedServlet
*/
@WebServlet("/GetAllWorldConfirmedServlet")
public class GetAllWorldConfirmedServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
Dao dao = new Dao();
/**
* @see HttpServlet#HttpServlet()
*/
public GetAllWorldConfirmedServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
request.setCharacterEncoding("UTF-8");
List<Country> countryList = dao.getAllCountry();
int countryConfirmedCount=0,countryCuredCount=0,countryDeadCount=0;
for(int i=0;i<countryList.size();i++) {
countryConfirmedCount += Integer.valueOf(countryList.get(i).getConfirmed_num());
countryCuredCount += Integer.valueOf(countryList.get(i).getCured_num());
countryDeadCount += Integer.valueOf(countryList.get(i).getDead_num());
}
HttpSession session = request.getSession();
session.setAttribute("countryList",countryList);
session.setAttribute("countryConfirmedCount",countryConfirmedCount);
session.setAttribute("countryCuredCount",countryCuredCount);
session.setAttribute("countryDeadCount",countryDeadCount);
session.setAttribute("lastUpdateTime",countryList.get(3).getDate());
Gson gson = new Gson();
String json = gson.toJson(countryList);
response.getWriter().write(json);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
获取中国历史所有的数据：（本来应该是全世界的，但是我爬下来的数据没对全世界的历史数据，只有更新数据）
package Servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.google.gson.Gson;
import Dao.Dao;
import bin.Country;
/**
* Servlet implementation class GetAllChinaConfirmedServlet
*/
@WebServlet("/GetAllChinaConfirmedServlet")
public class GetAllChinaConfirmedServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
Dao dao = new Dao();
/**
* @see HttpServlet#HttpServlet()
*/
public GetAllChinaConfirmedServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
request.setCharacterEncoding("UTF-8");
List<Country> ChinaList = dao.getAllChinaConfirmed();
Gson gson = new Gson();
String json = gson.toJson(ChinaList);
response.getWriter().write(json);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
连接数据的就不发了。
结尾：做的过程没有遇到太多的问题。遇到的问题自己最后都解决了。之前做echarts的测试感觉很难毫无头绪，耗费时间长。现在还是比较上手。人都是会进步，只有学了就有效果。如果读者知道哪个网址有世界历史数据，可以给我留言一下谢谢。任何对于代码有问题的、不懂得，留言。大佬还需要多给我提意见。
