--- 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; - } - }