情况概述：
今天对WeatherDaoImpl.Java进行了完善，并进行了测试。
代码如下：
package com.zlc.dao.impl; 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 com.mysql.jdbc.Statement; /** * @author Zhao Lucang * @version 创建时间：2021年2月26日 下午4:07:57 * 类说明 */ import com.zlc.dao.IWeatherDao; import com.zlc.entity.WeatherBean; import com.zlc.entity.WeatherCountBean; import com.zlc.util.CommonMethod; public class WeatherDaoImpl implements IWeatherDao { Connection conn = null; PreparedStatement ps = null; // 查询此ID是否存在 @Override public boolean isExist(int ID) { // TODO Auto-generated method stub return queryWeatherByID(ID) == null ? false : true; } // 根据编号查询天气 @SuppressWarnings("restriction") @Override public WeatherBean queryWeatherByID(int ID) { WeatherBean weathers = null; PreparedStatement ps = null; ResultSet rs = null; String sql = "SELECT * FROM table_city_degree_2011 WHERE ID=?"; // System.out.println(sql); try { Object[] params = { ID }; rs = CommonMethod.executeQuery(sql, params); if (rs.next()) { int id = rs.getInt("ID"); String province = rs.getString("Province"); String city = rs.getString("City"); int date = rs.getInt("Date"); String week = rs.getString("Week"); int mind = rs.getInt("MinD"); int maxd = rs.getInt("MaxD"); String weather = rs.getString("Weather"); String winddirection = rs.getString("WindDirection"); String windforce = rs.getString("WindForce"); weathers = new WeatherBean(id, province, city, date, week, mind, maxd, weather, winddirection, windforce); } return weathers; } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } finally { try { CommonMethod.closeAll(rs, (Statement) ps, CommonMethod.conn); } catch (SQLException e) { e.printStackTrace(); } } } // 根据地点，时间查询 @SuppressWarnings("restriction") @Override public WeatherBean queryWeatherByCityDate(String City, int Date) { WeatherBean weathers = null; PreparedStatement ps = null; ResultSet rs = null; String sub_date = String.valueOf(Date).substring(0, 4); String sql = "SELECT * FROM table_city_degree_" + sub_date + " WHERE City=? AND Date=?"; // System.out.println(sql); try { Object[] params = { City, Date }; rs = CommonMethod.executeQuery(sql, params); if (rs.next()) { int id = rs.getInt("ID"); String province = rs.getString("Province"); String city = rs.getString("City"); int date = rs.getInt("Date"); String week = rs.getString("Week"); int mind = rs.getInt("MinD"); int maxd = rs.getInt("MaxD"); String weather = rs.getString("Weather"); String winddirection = rs.getString("WindDirection"); String windforce = rs.getString("WindForce"); weathers = new WeatherBean(id, province, city, date, week, mind, maxd, weather, winddirection, windforce); } return weathers; } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } finally { try { CommonMethod.closeAll(rs, (Statement) ps, CommonMethod.conn); } catch (SQLException e) { e.printStackTrace(); } } } // 根据时间遍历全国 @SuppressWarnings("restriction") @Override public List<WeatherBean> queryAllWeathersByDate(int Date) { // TODO Auto-generated method stub PreparedStatement ps = null; ResultSet rs = null; String sub_date = String.valueOf(Date).substring(0, 4); String sql = "SELECT * FROM table_city_degree_" + sub_date + " WHERE Date=?"; List<WeatherBean> weathers = new ArrayList<WeatherBean>(); // System.out.println(sql); try { Object[] params = { Date }; rs = CommonMethod.executeQuery(sql, params); while (rs.next()) { // 实例化StudentBean WeatherBean weather = new WeatherBean(); weather.setID(rs.getInt("ID")); weather.setProvince(rs.getString("Province")); weather.setCity(rs.getString("City")); weather.setDate(rs.getInt("Date")); weather.setWeek(rs.getString("Week")); weather.setMinD(rs.getInt("MinD")); weather.setMaxD(rs.getInt("MaxD")); weather.setWeather(rs.getString("Weather")); weather.setWindDirection(rs.getString("WindDirection")); weather.setWindForce(rs.getString("WindForce")); weathers.add(weather); } return weathers; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } finally { try { CommonMethod.closeAll(rs, (Statement) ps, CommonMethod.conn); } catch (SQLException e) { e.printStackTrace(); } } } //查询该地区前*天的温度变化 @SuppressWarnings("restriction") @Override public List<WeatherBean> queryAllWeathersByCityDate(String City, int Date) { // TODO Auto-generated method stub PreparedStatement ps = null; ResultSet rs = null; String sub_date = String.valueOf(Date).substring(0, 4); String sql = "SELECT * FROM table_city_degree_" + sub_date + " WHERE City LIKE '%"+City+"%' AND Date>=? LIMIT 365"; List<WeatherBean> weathers = new ArrayList<WeatherBean>(); // System.out.println(sql); try { Object[] params = { Date }; rs = CommonMethod.executeQuery(sql, params); while (rs.next()) { // 实例化StudentBean WeatherBean weather = new WeatherBean(); weather.setID(rs.getInt("ID")); weather.setProvince(rs.getString("Province")); weather.setCity(rs.getString("City")); weather.setDate(rs.getInt("Date")); weather.setWeek(rs.getString("Week")); weather.setMinD(rs.getInt("MinD")); weather.setMaxD(rs.getInt("MaxD")); weather.setWeather(rs.getString("Weather")); weather.setWindDirection(rs.getString("WindDirection")); weather.setWindForce(rs.getString("WindForce")); weathers.add(weather); } return weathers; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } finally { try { CommonMethod.closeAll(rs, (Statement) ps, CommonMethod.conn); } catch (SQLException e) { e.printStackTrace(); } } } //查询该地区本年的天气类型数量 @Override public List<WeatherCountBean> queryAllWeathersByCityDateCount(String City, int MinDate,int MaxDate) { // TODO Auto-generated method stub PreparedStatement ps = null; ResultSet rs = null; String sub_date = String.valueOf(MinDate).substring(0, 4); String sql = "SELECT Province,City,Weather,count(*) AS DayNum FROM table_city_degree_"+sub_date+" WHERE City Like '%"+City+"%' AND Date BETWEEN ? AND ? GROUP BY City,Weather ORDER BY ID"; List<WeatherCountBean> weathers = new ArrayList<WeatherCountBean>(); try { Object[] params = { MinDate,MaxDate }; rs = CommonMethod.executeQuery(sql, params); while (rs.next()) { // 实例化StudentBean WeatherCountBean weather = new WeatherCountBean(); weather.setProvince(rs.getString("Province")); weather.setCity(rs.getString("City")); weather.setWeather(rs.getString("Weather")); weather.setDayNum(rs.getInt("DayNum")); weathers.add(weather); } return weathers; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } finally { try { CommonMethod.closeAll(rs, (Statement) ps, CommonMethod.conn); } catch (SQLException e) { e.printStackTrace(); } } } //省份最高/最低平均温度 @SuppressWarnings("restriction") @Override public List<WeatherBean> queryAllWeathersByDateHighLow(int Date) { // TODO Auto-generated method stub PreparedStatement ps = null; ResultSet rs = null; String sub_date = String.valueOf(Date).substring(0, 4); String sql = "SELECT Province,AVG(MaxD) as AvgMaxD,AVG(MinD) as AvgMinD,Date FROM table_city_degree_" + sub_date + " WHERE Date=? GROUP BY Province ORDER BY ID"; List<WeatherBean> weathers = new ArrayList<WeatherBean>(); try { Object[] params = { Date }; rs = CommonMethod.executeQuery(sql, params); while (rs.next()) { // 实例化WeatherBean WeatherBean weather = new WeatherBean(); weather.setProvince(rs.getString("Province")); weather.setDate(rs.getInt("Date")); weather.setMaxD(rs.getInt("AvgMaxD")); weather.setMinD(rs.getInt("AvgMinD")); weathers.add(weather); } return weathers; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } finally { try { CommonMethod.closeAll(rs, (Statement) ps, CommonMethod.conn); } catch (SQLException e) { e.printStackTrace(); } } } @Override public List<WeatherBean> queryAllWeathersByDateMid(int Date) { // TODO Auto-generated method stub PreparedStatement ps = null; ResultSet rs = null; String sub_date = String.valueOf(Date).substring(0, 4); String sql = "SELECT Province,AVG((MaxD+MinD)/2) as AvgD,Date FROM table_city_degree_" + sub_date + " WHERE Date=? GROUP BY Province ORDER BY ID"; List<WeatherBean> weathers = new ArrayList<WeatherBean>(); try { Object[] params = { Date }; rs = CommonMethod.executeQuery(sql, params); while (rs.next()) { // 实例化WeatherBean WeatherBean weather = new WeatherBean(); weather.setProvince(rs.getString("Province")); weather.setDate(rs.getInt("Date")); weather.setMinD(rs.getInt("AvgD")); weathers.add(weather); } return weathers; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } finally { try { CommonMethod.closeAll(rs, (Statement) ps, CommonMethod.conn); } catch (SQLException e) { e.printStackTrace(); } } } @SuppressWarnings("restriction") @Override public List<WeatherCountBean> queryAllWeathersByCityDateCountWind(String City, int MinDate, int MaxDate) { // TODO Auto-generated method stub PreparedStatement ps = null; ResultSet rs = null; String sub_date = String.valueOf(MinDate).substring(0, 4); String sql = "SELECT Province,City,WindDirection,count(*) AS DayNum FROM table_city_degree_"+sub_date+" WHERE City Like '%"+City+"%' AND Date BETWEEN ? AND ? GROUP BY City,WindDirection ORDER BY ID"; List<WeatherCountBean> weathers = new ArrayList<WeatherCountBean>(); try { Object[] params = { MinDate,MaxDate }; rs = CommonMethod.executeQuery(sql, params); while (rs.next()) { // 实例化StudentBean WeatherCountBean weather = new WeatherCountBean(); weather.setProvince(rs.getString("Province")); weather.setCity(rs.getString("City")); weather.setDayNum(rs.getInt("DayNum")); weather.setWindDirection(rs.getString("WindDirection")); weathers.add(weather); } return weathers; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } finally { try { CommonMethod.closeAll(rs, (Statement) ps, CommonMethod.conn); } catch (SQLException e) { e.printStackTrace(); } } } @Override public List<WeatherCountBean> queryAllWeathersByCityDateCountForce(String City, int MinDate, int MaxDate, String Force) { // TODO Auto-generated method stub PreparedStatement ps = null; ResultSet rs = null; String sub_date = String.valueOf(MinDate).substring(0, 4); String sql = "SELECT Province,City,WindDirection,WindForce,count(*) AS DayNum FROM table_city_degree_"+sub_date+" WHERE City Like '%"+City+"%' AND WindDirection=? AND Date BETWEEN ? AND ? GROUP BY City,WindDirection,WindForce ORDER BY ID"; List<WeatherCountBean> weathers = new ArrayList<WeatherCountBean>(); try { Object[] params = { Force,MinDate,MaxDate }; rs = CommonMethod.executeQuery(sql, params); while (rs.next()) { // 实例化StudentBean WeatherCountBean weather = new WeatherCountBean(); weather.setProvince(rs.getString("Province")); weather.setCity(rs.getString("City")); weather.setDayNum(rs.getInt("DayNum")); weather.setWindDirection(rs.getString("WindDirection")); weather.setWindForce(rs.getString("WindForce")); weathers.add(weather); } return weathers; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } finally { try { CommonMethod.closeAll(rs, (Statement) ps, CommonMethod.conn); } catch (SQLException e) { e.printStackTrace(); } } } }
Test.java
package com.zlc.test; import java.util.ArrayList; import java.util.List; import com.zlc.dao.IWeatherDao; import com.zlc.dao.impl.WeatherDaoImpl; import com.zlc.entity.WeatherCountBean; import com.zlc.service.IWeatherService; import com.zlc.service.impl.WeatherServiceImpl; import com.zlc.util.CommonMethod; /** * @author Zhao Lucang * @version 创建时间：2021年2月26日 下午4:33:18 * 类说明 */ public class Test { public static void main(String[] args) { CommonMethod common=new CommonMethod(); IWeatherService service=new WeatherServiceImpl(); IWeatherDao dao=new WeatherDaoImpl(); //WeatherBean weathers=service.queryWeatherByID(1); //WeatherBean weathers=service.queryWeatherByCityDate("北京市",20150506); //weathers=service.queryAllWeathersByDate(20150506); /* * List<WeatherBean> weathers = new ArrayList<WeatherBean>(); * weathers=service.queryAllWeathersByCityDate("北京", 20150506); for(WeatherBean * w:weathers) { * System.out.println(w.getID()+" "+w.getProvince()+" "+w.getCity()+" "+w. * getDate()+" "+w.getWeek()+" "+w.getWeather()+" "+w.getMinD()+" "+w.getMaxD() * +" "+w.getWindDirection()+" "+w.getWindForce()); * * } */ /* * List<WeatherCountBean> weathers = new ArrayList<WeatherCountBean>(); * weathers=service.queryAllWeathersByCityDateCount("北京", 20150106,20150208); * for(WeatherCountBean w:weathers) { * System.out.println(w.getProvince()+w.getCity()+w.getWeather()+w.getDayNum()); * * } */ List<WeatherCountBean> weathers = new ArrayList<WeatherCountBean>(); List<WeatherCountBean> weatherforce = new ArrayList<WeatherCountBean>(); weathers=service.queryAllWeathersByCityDateCountWind("北京", 20180106,20181008); for(WeatherCountBean w:weathers) { //System.out.println(w.getProvince()+w.getCity()+w.getWindDirection()+w.getDayNum()); weatherforce=service.queryAllWeathersByCityDateCountForce("北京", 20180106, 20181008, w.getWindDirection()); for(WeatherCountBean wf:weatherforce) { System.out.println(wf.getProvince()+wf.getCity()+" "+wf.getWindDirection()+" "+wf.getWindForce()+" "+wf.getDayNum()); } } /* * List<WeatherBean> weathers = new ArrayList<WeatherBean>(); * weathers=service.queryAllWeathersByDateMid(20150105); for(WeatherBean * w:weathers) { * System.out.println(w.getProvince()+w.getDate()+w.getMaxD()+w.getMinD()); * * } */ //System.out.println(weathers.getID()+" "+weathers.getProvince()+" "+weathers.getCity()+" "+weathers.getDate()+" "+weathers.getWeek()+" "+weathers.getWeather()+" "+weathers.getMinD()+" "+weathers.getMaxD()+" "+weathers.getWindDirection()+" "+weathers.getWindForce()); } }
测试结果：
