--- dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayerImplementation.java 2015/08/25 09:30:14 2666 +++ dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayerImplementation.java 2016/01/19 11:15:35 2827 @@ -17,21 +17,26 @@ import dk.daoas.daoadresseservice.beans.HundredePctBean; import dk.daoas.daoadresseservice.beans.SearchResult; import dk.daoas.daoadresseservice.util.DeduplicateHelper; +import dk.daoas.daoadresseservice.util.TimingHelper; public class DatabaseLayerImplementation implements DatabaseLayer { - static boolean DEBUG = false; + public static final boolean DEBUG = false; @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); @@ -41,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()) { @@ -61,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.ruteMandag = dirigeringsCache.getInstance( res.getString(13) ); + a.korelisteMandag = dirigeringsCache.getInstance( res.getString(14) ); + + 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.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.ruteTorsdag = dirigeringsCache.getInstance( res.getString(19) ); + a.korelisteTorsdag = dirigeringsCache.getInstance( res.getString(20) ); - a.latitude = (float) res.getDouble(17); - a.longitude = (float) res.getDouble(18); + a.ruteFredag = dirigeringsCache.getInstance( res.getString(21) ); + a.korelisteFredag = dirigeringsCache.getInstance( res.getString(22) ); - //a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn); + 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 @@ -82,7 +97,8 @@ } res.close(); - System.out.println("Loaded " + list.size() + " adresses"); + + System.out.println("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms"); return list; } @@ -90,6 +106,7 @@ @Override public List getAliasList() throws SQLException { + TimingHelper timing = new TimingHelper(); String sql = "SELECT postnr,vejnavn,aliasvejnavn " + @@ -119,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; } @@ -128,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 " + @@ -172,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; } @@ -180,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"; @@ -191,6 +211,8 @@ Map map = new HashMap(); DeduplicateHelper distributorCache = new DeduplicateHelper(); + DeduplicateHelper ruteCache = new DeduplicateHelper(); + DeduplicateHelper korelisteCache = new DeduplicateHelper(); while (res.next()) { @@ -198,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); @@ -207,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; } @@ -240,9 +262,9 @@ stmt.setString( 7, result.splitResult.etage); stmt.setString( 8, result.splitResult.lejlighed); stmt.setString( 9, result.splitResult.resten); - stmt.setString(10, brugerid); - stmt.setString(11, omdelingsdag); - stmt.setInt(12, getStatusInt(result.status) ); + stmt.setString(10, brugerid); + stmt.setInt(11, getStatusInt(result.status) ); + stmt.setString(12, omdelingsdag); stmt.executeUpdate();