--- dao/DaoAdresseService/src/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/02/23 12:30:41 2337
+++ dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/05/11 08:05:01 2539
@@ -2,6 +2,7 @@
import java.sql.Connection;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
@@ -12,8 +13,11 @@
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;
+import dk.daoas.daoadresseservice.beans.SearchResult;
import dk.daoas.daoadresseservice.util.DeduplicateHelper;
public class DatabaseLayer {
@@ -23,59 +27,101 @@
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
;
- 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);
-
- List list = new ArrayList(2600000);//initial capacity 2.6 mio
-
- DeduplicateHelper vejnavnCache = new DeduplicateHelper();
- DeduplicateHelper husnrbogstavCache = new DeduplicateHelper();
- DeduplicateHelper distributorCache = new DeduplicateHelper();
- DeduplicateHelper korelisteCache = new DeduplicateHelper();
- DeduplicateHelper ruteCache = new DeduplicateHelper();
-
-
- while (res.next()) {
-
- Address a = new Address();
- a.id = res.getInt(1);
- a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
- a.husnr = 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.distributor = distributorCache.getInstance(res.getString(9));
- a.dbkBane = res.getInt(10);
- a.koreliste = korelisteCache.getInstance( res.getString(11) );
- a.rute = ruteCache.getInstance( res.getString(12) );
-
- //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);
- }
- res.close();
- stmt.close();
- conn.close();
+ 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);
+
+ List list = new ArrayList(2600000);//initial capacity 2.6 mio
+
+ DeduplicateHelper vejnavnCache = new DeduplicateHelper();
+ DeduplicateHelper husnrbogstavCache = new DeduplicateHelper();
+ DeduplicateHelper distributorCache = new DeduplicateHelper();
+ DeduplicateHelper korelisteCache = new DeduplicateHelper();
+ DeduplicateHelper ruteCache = new DeduplicateHelper();
+
+
+ while (res.next()) {
+
+ Address a = new Address();
+ a.id = res.getInt(1);
+ a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
+ a.husnr = (short) res.getInt(3);
+ a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
+ 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 = (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);
+
+ if (a.rute != null && a.rute.length()> 0) {
+ a.daekningsType = DaekningsType.DAEKNING_DIREKTE;
+ } else {
+ a.daekningsType = DaekningsType.DAEKNING_IKKEDAEKKET;
+ }
+
+ list.add(a);
+ }
+ res.close();
+ stmt.close();
+ conn.close();
+
+ System.out.println("Loaded " + list.size() + " adresses");
+
+ return list;
+ }
+ }
+
+ public static List getAliasList() throws SQLException {
- System.out.println("Loaded " + list.size() + " adresses");
+
+ String sql = "SELECT postnr,vejnavn,aliasvejnavn " +
+ "FROM bogleveringer.vejtabelprod "
+ ;
- return list;
+ 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 {
@@ -98,68 +144,185 @@
debugFilter2
;
- 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 transportCache = new DeduplicateHelper();
+ try ( Connection conn = DBConnection.getConnection();
+ Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
+ ) {
+
- List list = new ArrayList( 350000); //Initial capacity 350K
- while (res.next()) {
+ stmt.setFetchSize(Integer.MIN_VALUE);
+
+ ResultSet res = stmt.executeQuery(sql);
+
+ DeduplicateHelper transportCache = new DeduplicateHelper();
+
+ 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 = (float) res.getDouble(3);
+ eb.transport = transportCache.getInstance(res.getString(4));
+
+ list.add(eb);
+ }
+
+ res.close();
- ExtendedBean eb = new ExtendedBean();
- eb.orgId = res.getInt(1);
- eb.targetId = res.getInt(2);
- eb.afstand = res.getDouble(3);
- eb.transport = transportCache.getInstance(res.getString(4));
-
- list.add(eb);
+ System.out.println("Loaded " + list.size() + " extendedbeans");
+
+ return list;
}
-
- res.close();
- stmt.close();
- conn.close();
-
- System.out.println("Loaded " + list.size() + " extendedbeans");
-
- return list;
}
- 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";
- 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();
-
- DeduplicateHelper distributorCache = new DeduplicateHelper();
-
- while (res.next()) {
+ try ( 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();
+
+ DeduplicateHelper distributorCache = new DeduplicateHelper();
+ while (res.next()) {
+
+
+ HundredePctBean bean = new HundredePctBean();
+ 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 = (short)res.getInt(5);
+
+ map.put(bean.postnr, bean);
+ }
- HundredePctBean bean = new HundredePctBean();
- bean.postnr = 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);
-
- map.put(bean.postnr, bean);
+ res.close();
+
+ System.out.println("Loaded " + map.size() + " 100pct beans");
+
+ return map;
}
- res.close();
- stmt.close();
- conn.close();
+ }
+
+ public static void saveRequestLog(String brugerid, String postnr, String adresse, SearchResult result) throws SQLException {
+ String sql = "INSERT INTO logs.hentruteinformation (postnr,adresse,vejnavn,googlevejnavn,husnr,husnr_bogstav,etage,lejlighed,rest,brugerid,status, indlast) " +
+ "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW() )";
- System.out.println("Loaded " + map.size() + " 100pct beans");
+
- return map;
+ try ( Connection conn = DBConnection.getConnection();
+ PreparedStatement stmt = conn.prepareStatement(sql);
+ ) {
+
+ stmt.setInt( 1, safeInt(postnr) );
+ stmt.setString( 2, adresse);
+ stmt.setString( 3, result.splitResult.vej);
+ stmt.setString( 4, coalesce(result.googleVej,result.osmVej) );
+ stmt.setString( 5, nullify(result.splitResult.husnr) );
+ stmt.setString( 6, result.splitResult.litra);
+ stmt.setString( 7, result.splitResult.etage);
+ stmt.setString( 8, result.splitResult.lejlighed);
+ stmt.setString( 9, result.splitResult.resten);
+ stmt.setString(10, brugerid);
+ stmt.setInt(11, getStatusInt(result.status) );
+
+ stmt.executeUpdate();
+
+ }
+ }
+
+ /*
+ * Bruges til at sammenligne gammel og ny adresse service - kan fjernes engang efter at vi er skiftet til ny service
+ */
+ public static List getLoggedAdresses(int antaldage) throws SQLException {
+ String sql = "select postnr,adresse,status from logs.hentruteinformation where indlast>=date_sub(curdate(), interval " + antaldage + " day) " +
+ "and status IN (10,11,12) " +
+ "group by postnr,adresse "
+ ;
+
+ 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);
+
+ List result = new ArrayList();
+
+ while (res.next()) {
+ LoggedAddress a = new LoggedAddress();
+ a.postnr = res.getInt(1);
+ a.adresse = res.getString(2);
+ a.status = res.getInt(3);
+
+ result.add(a);
+ }
+
+ res.close();
+
+ return result;
+ }
+ }
+
+ private static int getStatusInt(SearchResult.Status status) {
+
+ switch (status) {
+ case ERROR_UNKNOWN_POSTAL:
+ return 20;
+ case ERROR_MISSING_HOUSENUMBER:
+ return 21;
+ case ERROR_POSTBOX:
+ return 22;
+ case ERROR_UNKNOWN_STREETNAME:
+ return 23;
+ case ERROR_UNKNOWN_ADDRESSPOINT:
+ return 24;
+ case STATUS_NOT_COVERED:
+ return 25;
+ case ERROR_INTERNAL: //
+ return 26;
+
+ case STATUS_OK:
+ return 30;
+
+ default:
+ return 31;
+ }
+ }
+
+ private static int safeInt(String str) {
+ try {
+ return Integer.parseInt( str );
+ } catch (NumberFormatException e) {
+ return 0;
+ }
+ }
+
+ private static String nullify(String str) {
+ if (str == null)
+ return null;
+
+ if (str.equals("")) {
+ return null;
+ } else {
+ return str;
+ }
+ }
+
+ private static String coalesce(String s1, String s2) {
+ if (s1 != null)
+ return s1;
+ return s2;
}
+
}