--- dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayerImplementation.java 2015/07/15 08:37:40 2609 +++ dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayerImplementation.java 2016/01/19 10:23:47 2822 @@ -11,14 +11,13 @@ import java.util.Map; import dk.daoas.daoadresseservice.AddressUtils; -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; +import dk.daoas.daoadresseservice.util.TimingHelper; public class DatabaseLayerImplementation implements DatabaseLayer { @@ -26,14 +25,18 @@ @Override public List
getAllAdresses() throws SQLException { + TimingHelper timing = new TimingHelper(); + String debugFilter = DatabaseLayerImplementation.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 " + "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,latitude,longitude, " + + "rutema,korelistema,ruteti,korelisteti,ruteon,korelisteon,ruteto,korelisteto,rutefr,korelistefr,rutelo,korelistelo,ruteso,korelisteso " + "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); @@ -43,11 +46,10 @@ List
list = new ArrayList
(2600000);//initial capacity 2.6 mio - DeduplicateHelper vejnavnCache = new DeduplicateHelper(); + DeduplicateHelper vejnavnCache = new DeduplicateHelper(81920); DeduplicateHelper husnrbogstavCache = new DeduplicateHelper(); DeduplicateHelper distributorCache = new DeduplicateHelper(); - DeduplicateHelper korelisteCache = new DeduplicateHelper(); - DeduplicateHelper ruteCache = new DeduplicateHelper(); + DeduplicateHelper dirigeringsCache = new DeduplicateHelper(16*1024); while (res.next()) { @@ -63,18 +65,29 @@ a.gadeid = res.getInt(8); a.distributor = distributorCache.getInstance(res.getString(9)); a.dbkBane = (short) res.getInt(10); + a.latitude = (float) res.getDouble(11); + a.longitude = (float) res.getDouble(12); - 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.ruteMandag = dirigeringsCache.getInstance( res.getString(13) ); + a.korelisteMandag = dirigeringsCache.getInstance( res.getString(14) ); - a.latitude = (float) res.getDouble(17); - a.longitude = (float) res.getDouble(18); + a.ruteTirsdag = dirigeringsCache.getInstance( res.getString(15) ); + a.korelisteTirsdag = dirigeringsCache.getInstance( res.getString(16) ); + + a.ruteOnsdag = dirigeringsCache.getInstance( res.getString(17) ); + a.korelisteOnsdag = dirigeringsCache.getInstance( res.getString(18) ); - //a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn); + a.ruteTorsdag = dirigeringsCache.getInstance( res.getString(19) ); + a.korelisteTorsdag = dirigeringsCache.getInstance( res.getString(20) ); + + a.ruteFredag = dirigeringsCache.getInstance( res.getString(21) ); + a.korelisteFredag = dirigeringsCache.getInstance( res.getString(22) ); + + a.ruteLordag = dirigeringsCache.getInstance( res.getString(23) ); + a.korelisteLordag = dirigeringsCache.getInstance( res.getString(24) ); + + a.ruteSondag = dirigeringsCache.getInstance( res.getString(25) ); + a.korelisteSondag = dirigeringsCache.getInstance( res.getString(16) ); //Ajourfør adresse objectets dækningstype @@ -83,10 +96,9 @@ list.add(a); } res.close(); - stmt.close(); - conn.close(); - System.out.println("Loaded " + list.size() + " adresses"); + + System.out.println("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms"); return list; } @@ -94,6 +106,7 @@ @Override public List getAliasList() throws SQLException { + TimingHelper timing = new TimingHelper(); String sql = "SELECT postnr,vejnavn,aliasvejnavn " + @@ -123,7 +136,7 @@ res.close(); - System.out.println("Loaded " + list.size() + " aliase beans"); + System.out.println("DB Loaded " + list.size() + " aliase beans in " + timing.getElapsed() + "ms"); return list; } @@ -132,15 +145,16 @@ @Override public List getExtendedAdresslist() throws SQLException { + + TimingHelper timing = new TimingHelper(); + String debugFilter1 = DatabaseLayerImplementation.DEBUG ? " WHERE orgPostnr = 8700 " : ""; String debugFilter2 = DatabaseLayerImplementation.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 " + + "LEFT join odbc.transporttype t " + + "ON (t.Rute = a.Rute) " + debugFilter1 + "UNION ALL " + @@ -176,7 +190,7 @@ res.close(); - System.out.println("Loaded " + list.size() + " extendedbeans"); + System.out.println("DB Loaded " + list.size() + " extendedbeans in " + timing.getElapsed() + "ms"); return list; } @@ -184,6 +198,8 @@ @Override public Map get100PctList() throws SQLException { + TimingHelper timing = new TimingHelper(); + String sql = "SELECT postnr,UPPER(distributor) as distributor,rute,koreliste,dbkbane " + "FROM bogleveringer.adresser_udenfor_daekning"; @@ -195,6 +211,8 @@ Map map = new HashMap(); DeduplicateHelper distributorCache = new DeduplicateHelper(); + DeduplicateHelper ruteCache = new DeduplicateHelper(); + DeduplicateHelper korelisteCache = new DeduplicateHelper(); while (res.next()) { @@ -202,8 +220,8 @@ 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.rute = ruteCache.getInstance( res.getString(3) ); + bean.koreliste = korelisteCache.getInstance(res.getString(4) ); bean.dbkBane = (short)res.getInt(5); map.put(bean.postnr, bean); @@ -211,7 +229,7 @@ res.close(); - System.out.println("Loaded " + map.size() + " 100pct beans"); + System.out.println("DB Loaded " + map.size() + " 100pct beans in " + timing.getElapsed() + "ms"); return map; } @@ -219,11 +237,11 @@ } @Override - public void saveRequestLog(String brugerid, String postnr, String adresse, SearchResult result) throws SQLException { + public void saveRequestLog(String brugerid, String postnr, String adresse, String omdelingsdag, 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() )"; + String sql = "INSERT INTO logs.hentruteinformation (postnr,adresse,vejnavn,googlevejnavn,husnr,husnr_bogstav,etage,lejlighed,rest,brugerid,status, OmdelingDag, indlast) " + + "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW() )"; @@ -244,49 +262,16 @@ stmt.setString( 7, result.splitResult.etage); stmt.setString( 8, result.splitResult.lejlighed); stmt.setString( 9, result.splitResult.resten); - stmt.setString(10, brugerid); + stmt.setString(10, brugerid); stmt.setInt(11, getStatusInt(result.status) ); + stmt.setString(12, omdelingsdag); 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; - } - } private static int safeInt(String str) {