--- dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayerImplementation.java 2015/07/15 13:42:30 2616 +++ dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayerImplementation.java 2016/02/13 07:24:34 2943 @@ -17,35 +17,39 @@ 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); + Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 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 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.ruteTorsdag = dirigeringsCache.getInstance( res.getString(19) ); + a.korelisteTorsdag = dirigeringsCache.getInstance( res.getString(20) ); - 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.ruteFredag = dirigeringsCache.getInstance( res.getString(21) ); + a.korelisteFredag = dirigeringsCache.getInstance( res.getString(22) ); - a.latitude = (float) res.getDouble(17); - a.longitude = (float) res.getDouble(18); + a.ruteLordag = dirigeringsCache.getInstance( res.getString(23) ); + a.korelisteLordag = dirigeringsCache.getInstance( res.getString(24) ); - //a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn); + a.ruteSondag = dirigeringsCache.getInstance( res.getString(25) ); + a.korelisteSondag = dirigeringsCache.getInstance( res.getString(16) ); //Ajourfør adresse objectets dækningstype @@ -81,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; } @@ -92,6 +106,7 @@ @Override public List getAliasList() throws SQLException { + TimingHelper timing = new TimingHelper(); String sql = "SELECT postnr,vejnavn,aliasvejnavn " + @@ -99,7 +114,7 @@ ; try ( Connection conn = DBConnection.getConnection(); - Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); + Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ) { stmt.setFetchSize(Integer.MIN_VALUE); @@ -121,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; } @@ -130,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 " + @@ -150,7 +166,7 @@ ; try ( Connection conn = DBConnection.getConnection(); - Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); + Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ) { @@ -174,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; } @@ -182,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"; @@ -193,6 +211,8 @@ Map map = new HashMap(); DeduplicateHelper distributorCache = new DeduplicateHelper(); + DeduplicateHelper ruteCache = new DeduplicateHelper(); + DeduplicateHelper korelisteCache = new DeduplicateHelper(); while (res.next()) { @@ -200,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); @@ -209,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; } @@ -217,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() )"; @@ -242,8 +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(10, brugerid); stmt.setInt(11, getStatusInt(result.status) ); + stmt.setString(12, omdelingsdag); stmt.executeUpdate();