--- dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/07/14 08:48:33 2607
+++ dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/07/15 06:52:05 2608
@@ -1,341 +1,30 @@
package dk.daoas.daoadresseservice.db;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
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.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 {
+public interface DatabaseLayer {
- static boolean DEBUG = false;
- 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,korelisteloerdag,ruteloerdag,korelistesoendag,rutesoendag,latitude,longitude "
- + "FROM fulddaekning.adressetabel "
- + "WHERE gadeid IS NOT NULL "
- + debugFilter
- ;
-
- 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();
+ public List getAllAdresses() throws SQLException ;
-
- 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.korelisteLordag = korelisteCache.getInstance( res.getString(13) );
- a.ruteLordag = ruteCache.getInstance( res.getString(14) );
- a.korelisteSondag = korelisteCache.getInstance( res.getString(15) );
- a.ruteSondag = ruteCache.getInstance( res.getString(16) );
-
- a.latitude = (float) res.getDouble(17);
- a.longitude = (float) res.getDouble(18);
-
- //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 List getAliasList() throws SQLException;
- 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 " : "";
-
-
- 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 " +
- debugFilter1 +
-
- "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 " +
- debugFilter2
- ;
-
- 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 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();
-
- System.out.println("Loaded " + list.size() + " extendedbeans");
-
- return list;
- }
- }
+ public List getExtendedAdresslist() throws SQLException;
- public static Map get100PctList() throws SQLException {
- String sql = "SELECT postnr,UPPER(distributor) as distributor,rute,koreliste,dbkbane " +
- "FROM bogleveringer.adresser_udenfor_daekning";
-
- 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);
+ public Map get100PctList() throws SQLException;
- 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);
- }
-
- res.close();
-
- System.out.println("Loaded " + map.size() + " 100pct beans");
-
- return map;
- }
-
- }
-
- 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();
- 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);
- 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
- */
- @Deprecated
- 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;
- }
- }
+ public void saveRequestLog(String brugerid, String postnr, String adresse, SearchResult result) throws SQLException;
- 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;
- }
-
}