1 |
package dk.daoas.daoadresseservice.db;
|
2 |
|
3 |
|
4 |
import java.sql.Connection;
|
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 |
import dk.daoas.daoadresseservice.Address;
|
12 |
import dk.daoas.daoadresseservice.AddressUtils;
|
13 |
import dk.daoas.daoadresseservice.DaekningsType;
|
14 |
|
15 |
public class DatabaseLayer {
|
16 |
|
17 |
public static List<Address> getAllAdresses() throws SQLException {
|
18 |
|
19 |
String sql = "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,distributor,dbkbane,koreliste,rute "
|
20 |
+ "FROM fulddaekning.adressetabel "
|
21 |
+ "WHERE postnr BETWEEN 8700 and 8899" //DEBUG only
|
22 |
;
|
23 |
|
24 |
Connection conn = DBConnection.getConnection();
|
25 |
Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
|
26 |
ResultSet res = stmt.executeQuery(sql);
|
27 |
|
28 |
List<Address> list = new ArrayList<Address>();
|
29 |
|
30 |
while (res.next()) {
|
31 |
Address a = new Address();
|
32 |
a.id = res.getInt(1);
|
33 |
a.vejnavn = res.getString(2);
|
34 |
a.husnr = res.getInt(3);
|
35 |
a.husnrbogstav = res.getString(4);
|
36 |
a.kommunekode = res.getInt(5);
|
37 |
a.vejkode = res.getInt(6);
|
38 |
a.postnr = res.getInt(7);
|
39 |
a.gadeid = res.getLong(8);
|
40 |
a.distributor = res.getString(9);
|
41 |
a.dbkBane = res.getInt(10);
|
42 |
a.koreliste = res.getString(11);
|
43 |
a.rute = res.getString(12);
|
44 |
|
45 |
a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn);
|
46 |
|
47 |
if (a.rute != null && a.rute.length()> 0) {
|
48 |
a.daekningsType = DaekningsType.DAEKNING_DIREKTE;
|
49 |
} else {
|
50 |
a.daekningsType = DaekningsType.DAEKNING_IKKEDAEKKET;
|
51 |
}
|
52 |
|
53 |
list.add(a);
|
54 |
}
|
55 |
res.close();
|
56 |
stmt.close();
|
57 |
conn.close();
|
58 |
|
59 |
System.out.println("Loaded " + list.size() + " adresses");
|
60 |
|
61 |
return list;
|
62 |
}
|
63 |
}
|