--- dao/DaoAdresseService/src/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/02/13 11:48:23 2274
+++ dao/DaoAdresseService/src/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/02/15 10:11:13 2296
@@ -6,26 +6,30 @@
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
+import java.util.HashMap;
import java.util.List;
+import java.util.Map;
-import dk.daoas.daoadresseservice.Address;
-import dk.daoas.daoadresseservice.AddressUtils;
import dk.daoas.daoadresseservice.DaekningsType;
+import dk.daoas.daoadresseservice.beans.Address;
+import dk.daoas.daoadresseservice.beans.ExtendedBean;
+import dk.daoas.daoadresseservice.beans.HundredePctBean;
public class DatabaseLayer {
public static List
getAllAdresses() throws SQLException {
- String sql = "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,distributor,dbkbane,koreliste,rute "
+ String sql = "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,koreliste,rute "
+ "FROM fulddaekning.adressetabel "
- + "WHERE postnr BETWEEN 8700 and 8899" //DEBUG only
+ + "WHERE gadeid IS NOT NULL "
+ //+ "AND postnr = 8700" //DEBUG only
;
Connection conn = DBConnection.getConnection();
Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
ResultSet res = stmt.executeQuery(sql);
- List list = new ArrayList();
+ List list = new ArrayList(2600000);//initial capacity 2.6 mio
while (res.next()) {
Address a = new Address();
@@ -42,13 +46,13 @@
a.koreliste = res.getString(11);
a.rute = res.getString(12);
- a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn);
+ //a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn);
if (a.rute != null && a.rute.length()> 0) {
a.daekningsType = DaekningsType.DAEKNING_DIREKTE;
} else {
a.daekningsType = DaekningsType.DAEKNING_IKKEDAEKKET;
- }
+ }
list.add(a);
}
@@ -60,4 +64,79 @@
return list;
}
+
+ public static List getExtendedAdresslist() throws SQLException {
+ //DEBUG
+ if (true)
+ return new ArrayList();
+
+ String sql = "select orgid, a.id as targetid, afstand, LOWER(type) as type from fulddaekning.afstand_anden_rute a " +
+ "join odbc.transporttype t " +
+ "on t.Art = 'Transpost' " +
+ "and ( (t.Type = 'Cykel' and a.Afstand < 1.001) or (t.Type = 'Scooter' and a.Afstand < 1.201) or (t.Type = 'Bil' and a.Afstand < 2.601) ) " +
+ "and t.Rute = a.Rute " +
+
+ "UNION ALL " +
+
+ "SELECT orgid, a.id as targetid, afstand,'' as type FROM fulddaekning.afstand_anden_rute_bk a " +
+ "left join bogleveringer.postnummerdistributor d on d.PostNr = a.orgPostnr " +
+ "WHERE d.Distributor <> 10057"
+ ;
+
+ Connection conn = DBConnection.getConnection();
+ Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
+ ResultSet res = stmt.executeQuery(sql);
+
+ List list = new ArrayList( 350000); //Initial capacity 350K
+ while (res.next()) {
+ ExtendedBean eb = new ExtendedBean();
+ eb.orgId = res.getInt(1);
+ eb.targetId = res.getInt(2);
+ eb.afstand = res.getDouble(3);
+ eb.transport = res.getString(4);
+
+ list.add(eb);
+ }
+
+ res.close();
+ stmt.close();
+ conn.close();
+
+ System.out.println("Loaded " + list.size() + " extendedbeans");
+
+ return list;
+ }
+
+ public static Map get100PctList() throws SQLException {
+ String sql = "SELECT postnr,UPPER(distributor) as distributor,rute,koreliste,dbkbane " +
+ "FROM bogleveringer.adresser_udenfor_daekning";
+
+ Connection conn = DBConnection.getConnection();
+ Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
+ ResultSet res = stmt.executeQuery(sql);
+
+ Map map = new HashMap();
+
+ while (res.next()) {
+
+ HundredePctBean bean = new HundredePctBean();
+ bean.postnr = res.getInt(1);
+ bean.distributor = res.getString(2);
+ bean.rute = res.getString(3);
+ bean.koreliste = res.getString(4);
+ bean.dbkBane = res.getInt(5);
+
+ map.put(bean.postnr, bean);
+ }
+
+ res.close();
+ stmt.close();
+ conn.close();
+
+ System.out.println("Loaded " + map.size() + " 100pct beans");
+
+ return map;
+
+ }
+
}