--- 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/05/12 13:30:55 2544
@@ -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.gadeid = res.getLong(8);
+ a.kommunekode = (short) res.getInt(5);
+ a.vejkode = (short)res.getInt(6);
+ a.postnr = (short)res.getInt(7);
+ a.gadeid = res.getInt(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.getShort(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);
@@ -134,7 +175,7 @@
}
}
- public static Map get100PctList() throws SQLException {
+ public static Map get100PctList() throws SQLException {
String sql = "SELECT postnr,UPPER(distributor) as distributor,rute,koreliste,dbkbane " +
"FROM bogleveringer.adresser_udenfor_daekning";
@@ -143,7 +184,7 @@
) {
ResultSet res = stmt.executeQuery(sql);
- Map map = new HashMap();
+ Map map = new HashMap();
DeduplicateHelper distributorCache = new DeduplicateHelper();
@@ -151,11 +192,11 @@
HundredePctBean bean = new HundredePctBean();
- bean.postnr = res.getInt(1);
+ bean.postnr = (short) res.getInt(1);
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);
}
@@ -170,15 +211,21 @@
}
public static void saveRequestLog(String brugerid, String postnr, String adresse, SearchResult result) throws SQLException {
+ String setVar = "set sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ";
+
String sql = "INSERT INTO logs.hentruteinformation (postnr,adresse,vejnavn,googlevejnavn,husnr,husnr_bogstav,etage,lejlighed,rest,brugerid,status, indlast) " +
"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW() )";
- try ( Connection conn = DBConnection.getConnection();
+ try ( Connection conn = DBConnection.getConnection();
+ Statement setStmt = conn.createStatement();
PreparedStatement stmt = conn.prepareStatement(sql);
) {
+ setStmt.execute(setVar);
+
+
stmt.setInt( 1, safeInt(postnr) );
stmt.setString( 2, adresse);
stmt.setString( 3, result.splitResult.vej);
@@ -238,17 +285,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;
}
}