traininfoservice/db/StationDAO.java

package dk.thoerup.traininfoservice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class StationDAO {
        private StationBean convertSingleRow(ResultSet res) throws SQLException {
                StationBean station = new StationBean();
                
                station.setId( res.getInt(1) );
                station.setName( res.getString(2) );
                station.setLatitude( res.getDouble(3) );
                station.setLongitude( res.getDouble(4) );
                station.setRegional( res.getString(5) );
                station.setStrain( res.getString(6) );
                station.setMetro( res.getString(7) );
                station.setAddress( res.getString(8) );
                station.setCalcdist( (int)res.getDouble(9) );
                
                return station;
        }
        
        private List<StationBean> convertResultset(ResultSet res) throws SQLException {
                List<StationBean> stations = new ArrayList<StationBean>();
                while (res.next()) {
                        stations.add( convertSingleRow(res) );
                }
                return stations;
                
        }
        
        
        public StationBean getById(int id) throws SQLException {
                String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " +
                      "FROM trainstations WHERE id=" + id + " AND enabled=true";
                
                Connection conn = null;
                StationBean result;
                
                try {
                        conn = DBConnection.getConnection();
                
                        Statement stmt = conn.createStatement();
                        ResultSet res = stmt.executeQuery(SQL);         
                        res.next();
                        result = convertSingleRow(res);
                } finally {
                        if (conn != null)
                                conn.close();
                }
                
                return result;
        }
        
        
        public List<StationBean> getByName(String name) throws SQLException {
                String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + 
                "FROM trainstations " + 
                "WHERE name ILIKE ? AND enabled = true " +
                "ORDER BY name ";

                
                List<StationBean> result;
                Connection conn = null;
                try {
                        conn = DBConnection.getConnection();
                        PreparedStatement stmt = conn.prepareStatement(SQL);
                        
                        stmt.setString(1, name + "%");
                        
                        ResultSet rs = stmt.executeQuery();
                        result = convertResultset(rs);
                        
                } finally {
                        if (conn != null)
                                conn.close();
                }
                return result;
        }
         
        public List<StationBean> getByLocation(double latitude, double longitude) throws SQLException {
                String SQL = "SELECT * FROM ( "+
                "               SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address," + 
                "                     earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + 
                "               FROM trainstations " +
                "               WHERE enabled = true " +
                "       ) AS trainstations2 " +
                "ORDER BY calcdist ASC " + 
                "LIMIT 4 ";
                List<StationBean> result;
                Connection conn = null;
                try {
                        conn = DBConnection.getConnection();
                        PreparedStatement stmt = conn.prepareStatement(SQL);
                        stmt.setDouble(1, latitude);
                        stmt.setDouble(2, longitude);
                        ResultSet rs = stmt.executeQuery();
                        result = convertResultset(rs);
                        
                } finally {
                        if (conn != null)
                                conn.close();
                }
                return result;
        }
         
         public List<StationBean> getByList(String list) throws SQLException {
                        String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " + 
                        "FROM trainstations " + 
                        "WHERE id IN " + list + " AND enabled = true " +
                        "ORDER BY name ";
                        
                        Connection conn = null;
                        List<StationBean> result;
                        
                        try {
                                conn = DBConnection.getConnection();
                                Statement stmt = conn.createStatement();
                                ResultSet res = stmt.executeQuery(SQL);
                                result = convertResultset(res);
                        } finally {
                                if (conn!= null)
                                        conn.close();
                        }
                        
                        return result;
                        
         }
         
}
1	torben	588	package dk.thoerup.traininfoservice;
2
3			import java.sql.Connection;
4			import java.sql.PreparedStatement;
5			import java.sql.ResultSet;
6			import java.sql.SQLException;
7			import java.sql.Statement;
8			import java.util.ArrayList;
9			import java.util.List;
10
11			public class StationDAO {
12			private StationBean convertSingleRow(ResultSet res) throws SQLException {
13			StationBean station = new StationBean();
14
15			station.setId( res.getInt(1) );
16			station.setName( res.getString(2) );
17			station.setLatitude( res.getDouble(3) );
18			station.setLongitude( res.getDouble(4) );
19			station.setRegional( res.getString(5) );
20			station.setStrain( res.getString(6) );
21			station.setMetro( res.getString(7) );
22			station.setAddress( res.getString(8) );
23			station.setCalcdist( (int)res.getDouble(9) );
24
25			return station;
26			}
27
28			private List<StationBean> convertResultset(ResultSet res) throws SQLException {
29			List<StationBean> stations = new ArrayList<StationBean>();
30			while (res.next()) {
31			stations.add( convertSingleRow(res) );
32			}
33			return stations;
34
35			}
36
37
38			public StationBean getById(int id) throws SQLException {
39			String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " +
40			"FROM trainstations WHERE id=" + id + " AND enabled=true";
41
42			Connection conn = null;
43			StationBean result;
44
45			try {
46			conn = DBConnection.getConnection();
47
48			Statement stmt = conn.createStatement();
49			ResultSet res = stmt.executeQuery(SQL);
50			res.next();
51			result = convertSingleRow(res);
52			} finally {
53			if (conn != null)
54			conn.close();
55			}
56
57			return result;
58			}
59
60
61			public List<StationBean> getByName(String name) throws SQLException {
62			String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " +
63			"FROM trainstations " +
64			"WHERE name ILIKE ? AND enabled = true " +
65			"ORDER BY name ";
66
67
68			List<StationBean> result;
69			Connection conn = null;
70			try {
71			conn = DBConnection.getConnection();
72			PreparedStatement stmt = conn.prepareStatement(SQL);
73
74			stmt.setString(1, name + "%");
75
76			ResultSet rs = stmt.executeQuery();
77			result = convertResultset(rs);
78
79			} finally {
80			if (conn != null)
81			conn.close();
82			}
83			return result;
84			}
85
86			public List<StationBean> getByLocation(double latitude, double longitude) throws SQLException {
87			String SQL = "SELECT * FROM ( "+
88			" SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address," +
89			" earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " +
90			" FROM trainstations " +
91			" WHERE enabled = true " +
92			" ) AS trainstations2 " +
93			"ORDER BY calcdist ASC " +
94			"LIMIT 4 ";
95			List<StationBean> result;
96			Connection conn = null;
97			try {
98			conn = DBConnection.getConnection();
99			PreparedStatement stmt = conn.prepareStatement(SQL);
100			stmt.setDouble(1, latitude);
101			stmt.setDouble(2, longitude);
102			ResultSet rs = stmt.executeQuery();
103			result = convertResultset(rs);
104
105			} finally {
106			if (conn != null)
107			conn.close();
108			}
109			return result;
110			}
111
112			public List<StationBean> getByList(String list) throws SQLException {
113			String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " +
114			"FROM trainstations " +
115			"WHERE id IN " + list + " AND enabled = true " +
116			"ORDER BY name ";
117
118			Connection conn = null;
119			List<StationBean> result;
120
121			try {
122			conn = DBConnection.getConnection();
123			Statement stmt = conn.createStatement();
124			ResultSet res = stmt.executeQuery(SQL);
125			result = convertResultset(res);
126			} finally {
127			if (conn!= null)
128			conn.close();
129			}
130
131			return result;
132
133			}
134
135			}