指定时间范围的数据提取,并做统计:
用servlet来提取数据,并作统计,然后用jfreechart画图。
使用的话,需要在web.xml里面配置相应的servlet,并且在index.jsp页面做引导
package drawtest_zwj; import java.awt.Font; import java.io.FileOutputStream; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.jfree.chart.ChartFactory; import org.jfree.chart.ChartFrame; import org.jfree.chart.ChartUtilities; import org.jfree.chart.JFreeChart; import org.jfree.chart.axis.CategoryAxis; import org.jfree.chart.axis.NumberAxis; import org.jfree.chart.axis.ValueAxis; import org.jfree.chart.labels.StandardCategoryItemLabelGenerator; import org.jfree.chart.plot.CategoryPlot; import org.jfree.chart.plot.PlotOrientation; import org.jfree.chart.renderer.category.BarRenderer; import org.jfree.chart.title.TextTitle; import org.jfree.data.category.CategoryDataset; import org.jfree.data.category.DefaultCategoryDataset; public class Zxt_ShowChartServlet_seasonaver extends HttpServlet { /** * 获取一个演示用的组合数据集对象 * * @return */ private static CategoryDataset getDataSet() { DefaultCategoryDataset dataset = new DefaultCategoryDataset(); // 从数据库中获取 Connection conn = null; PreparedStatement ps_wd = null;//温度 PreparedStatement ps_ph = null;//PH PreparedStatement ps_rjy=null;//溶解氧 PreparedStatement ps_ddl=null;//电导率 PreparedStatement ps_zd=null;//浊度 PreparedStatement ps_gmsyzs=null;//高锰酸盐指数 PreparedStatement ps_zl=null;//总磷 PreparedStatement ps_zongdan=null;//总氮 PreparedStatement ps_andan=null;//氨氮 // PreparedStatement ps_zyjt=null;//总有机碳 ResultSet rs_wd = null; ResultSet rs_ph = null; ResultSet rs_rjy=null; ResultSet rs_ddl = null; ResultSet rs_zd= null; ResultSet rs_gmsyzs=null; ResultSet rs_zl = null; ResultSet rs_zongdan = null; ResultSet rs_andan=null; // ResultSet rs_zyjt=null; try { Class.forName("com.mysql.jdbc.Driver"); // 加载驱动程序 conn = DriverManager .getConnection( "jdbc:mysql://localhost:3306/dqz_eh_sdgc?useUnicode=true&characterEncoding=utf8", "root", "root"); //ps_wd = conn.prepareStatement("select p.name, count(pcount) from product p join salesitem si on (p.id = si.productid) group by p.id"); //ps_wd = conn.prepareStatement("select p.name, pcount from product p join salesitem si on (p.id = si.productid) group by p.id"); ps_wd = conn.prepareStatement("select f1, f2 from river_ey_data"); ps_ph = conn.prepareStatement("select f1, f3 from river_ey_data"); ps_rjy = conn.prepareStatement("select f1, f4 from river_ey_data"); ps_ddl = conn.prepareStatement("select f1, f5 from river_ey_data"); ps_zd = conn.prepareStatement("select f1, f6 from river_ey_data"); ps_gmsyzs = conn.prepareStatement("select f1, f7 from river_ey_data"); ps_zl = conn.prepareStatement("select f1, f8 from river_ey_data"); ps_zongdan = conn.prepareStatement("select f1, f9 from river_ey_data"); ps_andan = conn.prepareStatement("select f1, f10 from river_ey_data"); // ps_zyjt= conn.prepareStatement("select f1, f11 from river_ey_data"); rs_wd = ps_wd.executeQuery(); rs_ph=ps_ph.executeQuery(); rs_rjy=ps_rjy.executeQuery(); rs_ddl = ps_ddl.executeQuery(); rs_zd=ps_zd.executeQuery(); rs_gmsyzs=ps_gmsyzs.executeQuery(); rs_zl= ps_zl.executeQuery(); rs_zongdan=ps_zongdan.executeQuery(); rs_andan=ps_andan.executeQuery(); // rs_zyjt=ps_zyjt.executeQuery(); /////////////////////////温度/////////////////////////////////////// //循环前定义温度变量, Double wd_value=0.00; Double jan_wd_sumvalue=0.00;//一月份温度总和值 Double jan_wd_aver=0.00;//一月份平均温度值 Integer jan_wd_count=0; //一月份记录条数 Double feb_wd_sumvalue=0.00;//2月份温度总和值 Double feb_wd_aver=0.00;//2月份平均温度值 Integer feb_wd_count=0; //2月份记录条数 Double mar_wd_sumvalue=0.00;//3月份温度总和值 Double mar_wd_aver=0.00;//3月份平均温度值 Integer mar_wd_count=0; //3月份记录条数 Double apr_wd_sumvalue=0.00;//4月份温度总和值 Double apr_wd_aver=0.00;//4月份平均温度值 Integer apr_wd_count=0; //4月份记录条数 Double may_wd_sumvalue=0.00;//5月份温度总和值 Double may_wd_aver=0.00;//5月份平均温度值 Integer may_wd_count=0; //5月份记录条数 Double jun_wd_sumvalue=0.00;//6月份温度总和值 Double jun_wd_aver=0.00;//6月份平均温度值 Integer jun_wd_count=0; //6月份记录条数 Double jul_wd_sumvalue=0.00;//7月份温度总和值 Double jul_wd_aver=0.00;//7月份平均温度值 Integer jul_wd_count=0; //7月份记录条数 Double aug_wd_sumvalue=0.00;//8月份温度总和值 Double aug_wd_aver=0.00;//8月份平均温度值 Integer aug_wd_count=0; //8月份记录条数 Double sep_wd_sumvalue=0.00;//9月份温度总和值 Double sep_wd_aver=0.00;//9月份平均温度值 Integer sep_wd_count=0; //9月份记录条数 Double oct_wd_sumvalue=0.00;//10月份温度总和值 Double oct_wd_aver=0.00;//10月份平均温度值 Integer oct_wd_count=0; //10月份记录条数 Double nov_wd_sumvalue=0.00;//11月份温度总和值 Double nov_wd_aver=0.00;//11月份平均温度值 Integer nov_wd_count=0; //11月份记录条数 Double dec_wd_sumvalue=0.00;//12月份温度总和值 Double dec_wd_aver=0.00;//12月份平均温度值 Integer dec_wd_count=0; //12月份记录条数 Double first_season_aver=0.00;//第一季度均值 Double second_season_aver=0.00;//第二季度均值 Double third_season_aver=0.00;//第三季度均值 Double fouth_season_aver=0.00;//第四季度均值 //=================循环读取时间-温度================================= while (rs_wd.next()) { /////////////////////////日期字符串处理/////////////////////// String DateStr=rs_wd.getString(1);//获取日期字符串 String year=DateStr.substring(0, 4); //获取年 String month=DateStr.substring(5,7);//获取月 String day=DateStr.substring(8, 10);//获取日 ///////////////////获取温度值/////////////////////////// wd_value=rs_wd.getDouble(2);//获取温度值 //System.out.println(year+"-"+month+"-"+day);//testOK ////////////////////////////处理规定日期内数据/////////////////////////// //===============================暂时处理2013年全年数据,计算月平均,画折线======================================= if(year.equals("2013")) { if(month.equals("01")) { //System.out.println(year+"-"+month+"-"+day);//test_ok,年月份测试 jan_wd_count=jan_wd_count+1;//记录条数 jan_wd_sumvalue=jan_wd_sumvalue+wd_value;//1月温度累加 } else if(month.equals("02")) { feb_wd_count=feb_wd_count+1;//记录条数 feb_wd_sumvalue=feb_wd_sumvalue+wd_value;//2月温度累加 } else if(month.equals("03")) { mar_wd_count=mar_wd_count+1;//记录条数 mar_wd_sumvalue=mar_wd_sumvalue+wd_value;//3月温度累加 } else if(month.equals("04")) { apr_wd_count=apr_wd_count+1;//记录条数 apr_wd_sumvalue=apr_wd_sumvalue+wd_value;//4月温度累加 } else if(month.equals("05")) { may_wd_count=may_wd_count+1;//记录条数 may_wd_sumvalue=may_wd_sumvalue+wd_value;//5月温度累加 } else if(month.equals("06")) { jun_wd_count=jun_wd_count+1;//记录条数 jun_wd_sumvalue=jun_wd_sumvalue+wd_value;//6月温度累加 } else if(month.equals("07")) { jul_wd_count=jul_wd_count+1;//记录条数 jul_wd_sumvalue=jul_wd_sumvalue+wd_value;//7月温度累加 } else if(month.equals("08")) { aug_wd_count=aug_wd_count+1;//记录条数 aug_wd_sumvalue=aug_wd_sumvalue+wd_value;//8月温度累加 } else if(month.equals("09")) { sep_wd_count=sep_wd_count+1;//记录条数 sep_wd_sumvalue=sep_wd_sumvalue+wd_value;//9月温度累加 } else if(month.equals("10")) { oct_wd_count=oct_wd_count+1;//记录条数 oct_wd_sumvalue=oct_wd_sumvalue+wd_value;//10月温度累加 } else if(month.equals("11")) { nov_wd_count=nov_wd_count+1;//记录条数 nov_wd_sumvalue=nov_wd_sumvalue+wd_value;//11月温度累加 } // if(month.equals("12")) else { dec_wd_count=dec_wd_count+1;//记录条数 dec_wd_sumvalue=dec_wd_sumvalue+wd_value;//12月温度累加 } } } //循环结束,计算月份平均温度 jan_wd_aver=jan_wd_sumvalue/jan_wd_count; //dataset.addValue(jan_wd_aver, "月平均温度", "2013-01"); feb_wd_aver=feb_wd_sumvalue/feb_wd_count; //dataset.addValue(feb_wd_aver, "月平均温度", "2013-02"); mar_wd_aver=mar_wd_sumvalue/mar_wd_count; //dataset.addValue(mar_wd_aver, "月平均温度", "2013-03"); apr_wd_aver=apr_wd_sumvalue/apr_wd_count; //dataset.addValue(apr_wd_aver, "月平均温度", "2013-04"); may_wd_aver=may_wd_sumvalue/may_wd_count; //dataset.addValue(may_wd_aver, "月平均温度", "2013-05"); jun_wd_aver=jun_wd_sumvalue/jun_wd_count; //dataset.addValue(jun_wd_aver, "月平均温度", "2013-06"); jul_wd_aver=jul_wd_sumvalue/jul_wd_count; //dataset.addValue(jul_wd_aver, "月平均温度", "2013-07"); aug_wd_aver=aug_wd_sumvalue/aug_wd_count; //dataset.addValue(aug_wd_aver, "月平均温度", "2013-08"); sep_wd_aver=sep_wd_sumvalue/sep_wd_count; //dataset.addValue(sep_wd_aver, "月平均温度", "2013-09"); oct_wd_aver=oct_wd_sumvalue/oct_wd_count; //dataset.addValue(oct_wd_aver, "月平均温度", "2013-10"); nov_wd_aver=nov_wd_sumvalue/nov_wd_count; //dataset.addValue(nov_wd_aver, "月平均温度", "2013-11"); dec_wd_aver=dec_wd_sumvalue/dec_wd_count; //dataset.addValue(dec_wd_aver, "月平均温度", "2013-12"); first_season_aver=(jan_wd_aver+feb_wd_aver+mar_wd_aver)/3; dataset.addValue(first_season_aver,"季度平均温度","第1季度"); second_season_aver=(apr_wd_aver+may_wd_aver+jun_wd_aver)/3; dataset.addValue(second_season_aver,"季度平均温度","第2季度"); third_season_aver=(jul_wd_aver+aug_wd_aver+sep_wd_aver)/3; dataset.addValue(third_season_aver,"季度平均温度","第3季度"); fouth_season_aver=(oct_wd_aver+nov_wd_aver+dec_wd_aver)/3; dataset.addValue(fouth_season_aver,"季度平均温度","第4季度"); rs_wd.close(); rs_wd = null; /* //===========================PH============================================== while (rs_ph.next()) { dataset.addValue(rs_ph.getDouble(2), "PH", rs_ph.getString(1)); } rs_ph.close(); rs_ph = null; //============================溶解氧============================================ while(rs_rjy.next()) { dataset.addValue(rs_rjy.getDouble(2), "溶解氧", rs_rjy.getString(1)); } rs_rjy.close(); rs_rjy=null; //==============================电导率=============================================== //直接画图数值太大 // while (rs_ddl.next()) { // dataset.addValue(rs_ddl.getDouble(2), "电导率", rs_ddl.getString(1)); // } // rs_ddl.close(); // rs_ddl = null; //===================================浊度================================================ //有大数 /* while(rs_zd.next()) { dataset.addValue(rs_zd.getDouble(2), "浊度", rs_zd.getString(1)); } rs_zd.close(); rs_zd=null; */ //==============================高锰酸盐指数=============================================== /* while (rs_gmsyzs.next()) { dataset.addValue(rs_gmsyzs.getDouble(2), "高锰酸盐指数", rs_gmsyzs.getString(1)); } rs_gmsyzs.close(); rs_gmsyzs = null; */ //=============================总磷====================================================== //有大数 /* while(rs_zl.next()) { dataset.addValue(rs_zl.getDouble(2), "总磷", rs_zl.getString(1)); } rs_zl.close(); rs_zl=null; */ //=========================总氮============================== //有大数 /* while (rs_zongdan.next()) { dataset.addValue(rs_zongdan.getDouble(2), "总氮", rs_zongdan.getString(1)); } rs_zongdan.close(); rs_zongdan = null; */ //========================氨氮=============================== /* while(rs_andan.next()) { dataset.addValue(rs_andan.getDouble(2), "氨氮", rs_andan.getString(1)); } rs_andan.close(); rs_andan=null; */ //========================总有机碳=============================== // while (rs_zyjt.next()) { // dataset.addValue(rs_zyjt.getDouble(2), "总有机碳", rs_zyjt.getString(1)); // } // rs_zyjt.close(); // rs_zyjt = null; } catch (SQLException e) { e.printStackTrace(); }catch (ClassNotFoundException e) { e.printStackTrace(); } finally { if(ps_wd != null){ try { ps_wd.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } return dataset; } public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { System.out.println("xxxx");//test CategoryDataset dataset = getDataSet(); //调用前面的函数,取得数据集 JFreeChart chart = ChartFactory.createLineChart( "折线图", // 图表标题 "时间", // 目录轴的显示标签 "数值", // 数值轴的显示标签 dataset, // 数据集 PlotOrientation.VERTICAL, // 图表方向:水平、垂直 true, // 是否显示图例(对于简单的柱状图必须是 false) true, // 是否生成工具 false // 是否生成 URL 链接 ); //中文乱码 CategoryPlot mplot = (CategoryPlot) chart.getPlot();//获取坐标 chart.getLegend().setItemFont(new Font("宋体", Font.PLAIN, 12));//图列 CategoryAxis mdomainAxis = mplot.getDomainAxis(); //X坐标 mdomainAxis.setTickLabelFont(new Font("sans-serif", Font.PLAIN, 11)); //X mdomainAxis.setLabelFont(new Font("宋体", Font.PLAIN, 12)); ValueAxis mValueAxis = mplot.getRangeAxis(); mValueAxis.setTickLabelFont(new Font("sans-serif", Font.PLAIN, 12)); //Y mValueAxis.setLabelFont(new Font("黑体", Font.PLAIN, 12)); TextTitle textTitle = chart.getTitle();//坐标标题设置 textTitle.setFont(new Font("黑体", Font.PLAIN, 20)); FileOutputStream fos_jpg = null; //存为图片文件 try { fos_jpg = new FileOutputStream("G:\JAVA\JFreeChart\WebRoot\reports\seasonaver_wd.jpg"); // fos_jpg = new FileOutputStream("G:\JAVA\JFreeChart\WebRoot\reports\monthaver_wd.jpg"); ChartUtilities.writeChartAsJPEG(fos_jpg, 1.0f,chart,1000,500,null); String path = this.getServletContext().getContextPath(); System.out.println(path+"mmmmmmmmmmm");//test this.getServletContext().getRequestDispatcher("/showJfreeChart.jsp").forward(request, response); } finally { try { fos_jpg.close(); } catch (Exception e) {} } } }