--- dao/DaoAdresseService/src/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/02/24 14:48:30 2371
+++ dao/DaoAdresseService/src/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/02/24 20:38:56 2372
@@ -2,6 +2,7 @@
import java.sql.Connection;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
@@ -14,6 +15,7 @@
import dk.daoas.daoadresseservice.beans.Address;
import dk.daoas.daoadresseservice.beans.ExtendedBean;
import dk.daoas.daoadresseservice.beans.HundredePctBean;
+import dk.daoas.daoadresseservice.beans.SearchResult;
import dk.daoas.daoadresseservice.util.DeduplicateHelper;
public class DatabaseLayer {
@@ -29,53 +31,55 @@
+ 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();
-
- System.out.println("Loaded " + list.size() + " adresses");
-
- 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);
+
+ 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();
+
+ System.out.println("Loaded " + list.size() + " adresses");
+
+ return list;
+ }
}
public static List getExtendedAdresslist() throws SQLException {
@@ -98,68 +102,145 @@
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);
- 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);
+ 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 = res.getDouble(3);
+ eb.transport = transportCache.getInstance(res.getString(4));
+
+ list.add(eb);
+ }
+
+ res.close();
+
+ 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 {
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();
- 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);
+ while (res.next()) {
+
+
+ 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();
+
+ }
+ }
+
+ private static int getStatusInt(SearchResult.Status status) {
+
+ switch (status) {
+ case ERROR_UNKNOWN_POSTAL:
+ return 20;
+ case ERROR_MISSING_HOUSENUMBER:
+ return 21;
+ case ERROR_UNKNOWN_STREETNAME:
+ return 22;
+ case ERROR_UNKNOWN_ADDRESSPOINT:
+ return 23;
+ case STATUS_NOT_COVERED:
+ return 24;
+ case STATUS_OK:
+ return 25;
+
+ default:
+ return 29;
+ }
+ }
+
+ 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;
}
+
}