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;
                
        }
        
        List<SavedStatBean> convertResultset(ResultSet res) throws SQLException{
                List<SavedStatBean> stats = new ArrayList<SavedStatBean>();
                while (res.next()) {
                        SavedStatBean bean = convertRow(res);                                                           
                        stats.add(bean);
                        
                }               
                return stats;
        }
        
        
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
                
                Connection conn = null;
                try {
                        conn = DBConnection.getConnection();
                        Statement stmt = conn.createStatement();
                        
                        
                        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 sql30Avg = "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 AND statisticsdate >= (now()::date - interval '30 days')";
                        
                        ResultSet res30Avg = stmt.executeQuery(sql30Avg);
                        res30Avg.next();
                        SavedStatBean avg30Bean = convertRow(res30Avg);                 
                        
                        ////////////////////////////////////////////////
                        
                        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);
                        List<SavedStatBean> stats = convertResultset(res);                                      
                        

                        //////////////////////////////////////////////
                        
                        SQL = "select statisticsdate,location+name+favorites AS station,location,name,favorites,departure,depcache,deperror,timetable,timecache,timeerror " +
                        "FROM trainstatistics order by departure - deperror desc limit 10";                     
                        res = stmt.executeQuery(SQL);
                        
                        List<SavedStatBean> top10 = convertResultset(res); 


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

}
1	torben	961	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	torben	1255	import dk.thoerup.traininfoservice.db.DBConnection;
19	torben	961
20	torben	1255
21	torben	961	@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	torben	964	bean.label = res.getString(1);
30	torben	961	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	torben	1400	List<SavedStatBean> convertResultset(ResultSet res) throws SQLException{
46			List<SavedStatBean> stats = new ArrayList<SavedStatBean>();
47			while (res.next()) {
48			SavedStatBean bean = convertRow(res);
49			stats.add(bean);
50
51			}
52			return stats;
53			}
54	torben	961
55	torben	1400
56	torben	961	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
57
58			Connection conn = null;
59			try {
60			conn = DBConnection.getConnection();
61			Statement stmt = conn.createStatement();
62
63
64	torben	1400
65
66	torben	961	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, " +
67			"round(avg(departure),1) as departure, round(avg(depcache),1) as depcache, round(avg(deperror),1) as deperror, " +
68			"round(avg(timetable),1) as timetable, round(avg(timecache),1) as timecache, round(avg(timeerror),1) as timeerror " +
69	torben	968	"FROM trainstatistics " +
70			"WHERE statisticsdate <> now()::date ";
71	torben	961
72			ResultSet resAvg = stmt.executeQuery(sqlAvg);
73			resAvg.next();
74			SavedStatBean avgBean = convertRow(resAvg);
75	torben	1812
76			//////
77			String sql30Avg = "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, " +
78			"round(avg(departure),1) as departure, round(avg(depcache),1) as depcache, round(avg(deperror),1) as deperror, " +
79			"round(avg(timetable),1) as timetable, round(avg(timecache),1) as timecache, round(avg(timeerror),1) as timeerror " +
80			"FROM trainstatistics " +
81			"WHERE statisticsdate < now()::date AND statisticsdate >= (now()::date - interval '30 days')";
82	torben	961
83	torben	1812	ResultSet res30Avg = stmt.executeQuery(sql30Avg);
84			res30Avg.next();
85			SavedStatBean avg30Bean = convertRow(res30Avg);
86
87	torben	961	////////////////////////////////////////////////
88
89			String SQL = "SELECT statisticsdate, location+name+favorites AS station, location,name,favorites,departure,depcache,deperror,timetable,timecache,timeerror " +
90			"FROM trainstatistics ORDER BY statisticsdate DESC limit 14";
91
92
93			ResultSet res = stmt.executeQuery(SQL);
94	torben	1400	List<SavedStatBean> stats = convertResultset(res);
95	torben	961
96	torben	1400
97			//////////////////////////////////////////////
98	torben	961
99	torben	2091	SQL = "select statisticsdate,location+name+favorites AS station,location,name,favorites,departure,depcache,deperror,timetable,timecache,timeerror " +
100	torben	2092	"FROM trainstatistics order by departure - deperror desc limit 10";
101	torben	1400	res = stmt.executeQuery(SQL);
102	torben	961
103	torben	2091	List<SavedStatBean> top10 = convertResultset(res);
104	torben	967
105	torben	1400
106	torben	967	conn.close();
107	torben	961
108
109	torben	962	request.setAttribute("avg", avgBean);
110	torben	1812	request.setAttribute("avg30", avg30Bean);
111	torben	961	request.setAttribute("stats", stats);
112	torben	2091	request.setAttribute("top10", top10);
113	torben	961	request.getRequestDispatcher("/savedstatistics.jsp").forward(request, response);
114
115
116			} catch (SQLException e) {
117			try {
118			if (conn != null) conn.close();
119			} catch (SQLException se) {}
120			throw new ServletException(e);
121			}
122			}
123
124			}