thoerup/traininfoservice/SavedStatistics.java

package dk.thoerup.traininfoservice;


import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
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 dk.thoerup.traininfoservice.db.DBConnection;


@WebServlet(urlPatterns={"/SavedStatistics"})
public class SavedStatistics extends HttpServlet {
        private static final long serialVersionUID = 1L;
        
        
        protected SavedStatBean convertRow(ResultSet res) throws SQLException {
                SavedStatBean bean = new SavedStatBean();

                bean.label = res.getString(1);
                bean.station = res.getString(2);
                bean.location = res.getString(3);
                bean.name = res.getString(4);
                bean.favorites = res.getString(5);
                bean.departure = res.getString(6);
                bean.depcache = res.getString(7);
                bean.deperror = res.getString(8);
                bean.timetable = res.getString(9);
                bean.timecache = res.getString(10);
                bean.timeerror = res.getString(11);
                
                return bean;
                
        }
        
        
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
                
                Connection conn = null;
                try {
                        conn = DBConnection.getConnection();
                        Statement stmt = conn.createStatement();
                        List<SavedStatBean> stats = new ArrayList<SavedStatBean>();
                        
                        
                        String sqlAvg = "SELECT count(*) AS count, round(avg(location+name+favorites),1) AS station, round(avg(location),1) as location, round(avg(name),1) as name, round(avg(favorites),1) as favorites, " +
                        "round(avg(departure),1) as departure, round(avg(depcache),1) as depcache, round(avg(deperror),1) as deperror, " +
                        "round(avg(timetable),1) as timetable, round(avg(timecache),1) as timecache, round(avg(timeerror),1) as timeerror " +
                        "FROM trainstatistics " +
                        "WHERE statisticsdate <> now()::date ";
                        
                        ResultSet resAvg = stmt.executeQuery(sqlAvg);
                        resAvg.next();
                        SavedStatBean avgBean = convertRow(resAvg);
                        
                        ////////////////////////////////////////////////
                        
                        String SQL = "SELECT statisticsdate, location+name+favorites AS station, location,name,favorites,departure,depcache,deperror,timetable,timecache,timeerror " +
                        "FROM trainstatistics ORDER BY statisticsdate DESC limit 14";
                        
                        
                        ResultSet res = stmt.executeQuery(SQL);
                        
                        
                        while (res.next()) {
                                SavedStatBean bean = convertRow(res);                                                           
                                stats.add(bean);
                                
                        }

                        conn.close();                   
                        
                        
                        request.setAttribute("avg", avgBean);
                        request.setAttribute("stats", stats);
                        request.getRequestDispatcher("/savedstatistics.jsp").forward(request, response);
                        
                        
                } catch (SQLException e) {
                        try { 
                                if (conn != null) conn.close();
                        } catch (SQLException se) {}
                        throw new ServletException(e);
                }
        }

}
1	package dk.thoerup.traininfoservice;
2
3
4	import java.io.IOException;
5	import java.sql.Connection;
6	import java.sql.ResultSet;
7	import java.sql.SQLException;
8	import java.sql.Statement;
9	import java.util.ArrayList;
10	import java.util.List;
11
12	import javax.servlet.ServletException;
13	import javax.servlet.annotation.WebServlet;
14	import javax.servlet.http.HttpServlet;
15	import javax.servlet.http.HttpServletRequest;
16	import javax.servlet.http.HttpServletResponse;
17
18	import dk.thoerup.traininfoservice.db.DBConnection;
19
20
21	@WebServlet(urlPatterns={"/SavedStatistics"})
22	public class SavedStatistics extends HttpServlet {
23	private static final long serialVersionUID = 1L;
24
25
26	protected SavedStatBean convertRow(ResultSet res) throws SQLException {
27	SavedStatBean bean = new SavedStatBean();
28
29	bean.label = res.getString(1);
30	bean.station = res.getString(2);
31	bean.location = res.getString(3);
32	bean.name = res.getString(4);
33	bean.favorites = res.getString(5);
34	bean.departure = res.getString(6);
35	bean.depcache = res.getString(7);
36	bean.deperror = res.getString(8);
37	bean.timetable = res.getString(9);
38	bean.timecache = res.getString(10);
39	bean.timeerror = res.getString(11);
40
41	return bean;
42
43	}
44
45
46	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
47
48	Connection conn = null;
49	try {
50	conn = DBConnection.getConnection();
51	Statement stmt = conn.createStatement();
52	List<SavedStatBean> stats = new ArrayList<SavedStatBean>();
53
54
55	String sqlAvg = "SELECT count(*) AS count, round(avg(location+name+favorites),1) AS station, round(avg(location),1) as location, round(avg(name),1) as name, round(avg(favorites),1) as favorites, " +
56	"round(avg(departure),1) as departure, round(avg(depcache),1) as depcache, round(avg(deperror),1) as deperror, " +
57	"round(avg(timetable),1) as timetable, round(avg(timecache),1) as timecache, round(avg(timeerror),1) as timeerror " +
58	"FROM trainstatistics " +
59	"WHERE statisticsdate <> now()::date ";
60
61	ResultSet resAvg = stmt.executeQuery(sqlAvg);
62	resAvg.next();
63	SavedStatBean avgBean = convertRow(resAvg);
64
65	////////////////////////////////////////////////
66
67	String SQL = "SELECT statisticsdate, location+name+favorites AS station, location,name,favorites,departure,depcache,deperror,timetable,timecache,timeerror " +
68	"FROM trainstatistics ORDER BY statisticsdate DESC limit 14";
69
70
71	ResultSet res = stmt.executeQuery(SQL);
72
73
74
75
76	while (res.next()) {
77	SavedStatBean bean = convertRow(res);
78	stats.add(bean);
79
80	}
81
82	conn.close();
83
84
85	request.setAttribute("avg", avgBean);
86	request.setAttribute("stats", stats);
87	request.getRequestDispatcher("/savedstatistics.jsp").forward(request, response);
88
89
90	} catch (SQLException e) {
91	try {
92	if (conn != null) conn.close();
93	} catch (SQLException se) {}
94	throw new ServletException(e);
95	}
96	}
97
98	}