--- dao/DaoAdresseService/src/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/02/26 13:11:45 2384
+++ dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/03/24 11:05:24 2489
@@ -13,6 +13,7 @@
import dk.daoas.daoadresseservice.DaekningsType;
import dk.daoas.daoadresseservice.beans.Address;
+import dk.daoas.daoadresseservice.beans.AliasBean;
import dk.daoas.daoadresseservice.beans.ExtendedBean;
import dk.daoas.daoadresseservice.beans.HundredePctBean;
import dk.daoas.daoadresseservice.beans.LoggedAddress;
@@ -26,7 +27,8 @@
public static List
getAllAdresses() throws SQLException {
String debugFilter = DatabaseLayer.DEBUG ? " AND postnr = 8700 " : "";
- String sql = "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,koreliste,rute "
+ String sql =
+ "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,koreliste,rute,latitude,longitude "
+ "FROM fulddaekning.adressetabel "
+ "WHERE gadeid IS NOT NULL "
+ debugFilter
@@ -52,16 +54,18 @@
Address a = new Address();
a.id = res.getInt(1);
a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
- a.husnr = res.getInt(3);
+ a.husnr = (short) res.getInt(3);
a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
- a.kommunekode = res.getInt(5);
- a.vejkode = res.getInt(6);
- a.postnr = res.getInt(7);
+ a.kommunekode = (short) res.getInt(5);
+ a.vejkode = (short)res.getInt(6);
+ a.postnr = (short)res.getInt(7);
a.gadeid = res.getLong(8);
a.distributor = distributorCache.getInstance(res.getString(9));
- a.dbkBane = res.getInt(10);
+ a.dbkBane = (short) res.getInt(10);
a.koreliste = korelisteCache.getInstance( res.getString(11) );
a.rute = ruteCache.getInstance( res.getString(12) );
+ a.latitude = (float) res.getDouble(13);
+ a.longitude = (float) res.getDouble(14);
//a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn);
@@ -83,6 +87,43 @@
}
}
+ public static List getAliasList() throws SQLException {
+
+
+ String sql = "SELECT postnr,vejnavn,aliasvejnavn " +
+ "FROM bogleveringer.vejtabelprod "
+ ;
+
+ try ( Connection conn = DBConnection.getConnection();
+ Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
+ ) {
+
+ stmt.setFetchSize(Integer.MIN_VALUE);
+
+ ResultSet res = stmt.executeQuery(sql);
+
+ DeduplicateHelper vejCache = new DeduplicateHelper();
+
+ List list = new ArrayList( 5000);
+ while (res.next()) {
+
+ AliasBean ab = new AliasBean();
+ ab.postnr = res.getInt(1);
+ ab.vejnavn = vejCache.getInstance( res.getString(2) );
+ ab.aliasVejnavn = vejCache.getInstance( res.getString(3) );
+
+ list.add(ab);
+ }
+
+ res.close();
+
+ System.out.println("Loaded " + list.size() + " aliase beans");
+
+ return list;
+ }
+
+ }
+
public static List getExtendedAdresslist() throws SQLException {
String debugFilter1 = DatabaseLayer.DEBUG ? " WHERE orgPostnr = 8700 " : "";
String debugFilter2 = DatabaseLayer.DEBUG ? " AND orgPostnr = 8700 " : "";
@@ -120,7 +161,7 @@
ExtendedBean eb = new ExtendedBean();
eb.orgId = res.getInt(1);
eb.targetId = res.getInt(2);
- eb.afstand = res.getDouble(3);
+ eb.afstand = (float) res.getDouble(3);
eb.transport = transportCache.getInstance(res.getString(4));
list.add(eb);
@@ -155,7 +196,7 @@
bean.distributor = distributorCache.getInstance(res.getString(2));
bean.rute = res.getString(3);
bean.koreliste = res.getString(4);
- bean.dbkBane = res.getInt(5);
+ bean.dbkBane = (short)res.getInt(5);
map.put(bean.postnr, bean);
}
@@ -238,17 +279,22 @@
return 20;
case ERROR_MISSING_HOUSENUMBER:
return 21;
+ case ERROR_POSTBOX:
+ return 22;
case ERROR_UNKNOWN_STREETNAME:
- return 22;
- case ERROR_UNKNOWN_ADDRESSPOINT:
return 23;
- case STATUS_NOT_COVERED:
+ case ERROR_UNKNOWN_ADDRESSPOINT:
return 24;
- case STATUS_OK:
+ case STATUS_NOT_COVERED:
return 25;
+ case ERROR_INTERNAL: //
+ return 26;
+
+ case STATUS_OK:
+ return 30;
default:
- return 29;
+ return 31;
}
}