--- dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2014/04/30 12:53:01 2147 +++ dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2015/07/28 08:08:22 2640 @@ -1,5 +1,7 @@ package dk.daoas.fulddaekning; +import geocode.GeoPoint; + import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; @@ -7,46 +9,277 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; +import java.util.HashMap; import java.util.List; +import java.util.Map; import java.util.Properties; -import java.util.Queue; -import java.util.concurrent.ConcurrentLinkedQueue; +import java.util.Set; +import java.util.TreeSet; import java.util.logging.Logger; + public class Database { Logger logger = Logger.getLogger(Database.class.getName()); + + int batchCount = 0; + + Connection conn; + PreparedStatement saveStmt; + List alleAdresser; + Adresse alleIkkeDaekkede[]; - Connection conn; - Connection writeConn; + Map> daekkedeAdresserHO = new HashMap>(); - PreparedStatement saveStmt; + + + + + + DeduplicateHelper husnrbogstavCache = new DeduplicateHelper(); + DeduplicateHelper ruteCache = new DeduplicateHelper(); + + Set postnumre = new TreeSet(); + + //Map> ikkeDaekkedePrPost = new HashMap>(); + + + private HashMap bbCache = new HashMap(); + + public Database(SafeProperties conf) throws SQLException,IOException { + this.conn = getConnection( conf ); + + String sql = "INSERT INTO fulddaekning.afstand_anden_rute_ny (orgId,orgPostnr, orgAdresse,orgGadeid,orgHusnr,orgHusnrBogstav,orgLatitude,orgLongitude,orgRute,id,postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,afstand,`timestamp`) "+ + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, now() )"; + + saveStmt = conn.prepareStatement(sql); + + } - public Database(Properties conf) throws SQLException,IOException { - conn = getConnection(conf); - writeConn = getConnection(conf); + public void resetResultTable() throws SQLException { + logger.info("Dropping old result table (if exists)"); + String sql = "DROP TABLE IF EXISTS fulddaekning.afstand_anden_rute_ny"; + conn.createStatement().executeUpdate(sql); + + logger.info("Create new result table"); + sql = "CREATE TABLE fulddaekning.afstand_anden_rute_ny LIKE fulddaekning.afstand_anden_rute"; + conn.createStatement().executeUpdate(sql); + } + + public void renameResultTables() throws SQLException { + Constants consts = Constants.getInstance(); + String ext = consts.getTableExtension(); + + logger.info("Dropping old backup table (if exists)"); + String sql = "DROP TABLE IF EXISTS fulddaekning.afstand_anden_rute_old" + ext; + conn.createStatement().executeUpdate(sql); + logger.info("Rename tables"); + String sql2 = "RENAME TABLE fulddaekning.afstand_anden_rute" + ext + " TO fulddaekning.afstand_anden_rute_old" + ext + ", fulddaekning.afstand_anden_rute_ny TO fulddaekning.afstand_anden_rute" + ext; + + logger.info("Executing: " + sql2); + conn.createStatement().executeUpdate(sql2); + } + + public BoundingBox getBoundingbox(short postnr) { + BoundingBox bb = bbCache.get(postnr); + return bb.clone();//never return the original / cached object + } + + + public Set hentPostnumreCache() { + return postnumre; + } + + + public Adresse[] hentAlleIkkedaekkedeAdresser(int minPostnr, int maxPostnr) throws SQLException { - String sql = "INSERT INTO fulddaekning.afstand_anden_rute_thn (orgId,orgPostnr, orgAdresse,orgGadeid,orgHusnr,orgHusnrBogstav,orgLatitude,orgLongitude,orgRute,id,postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,afstand,`timestamp`) "+ - "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, now() )"; + logger.info("Henter alle IKKE-daekkede adresser"); + + String sql = "SELECT id,a.postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,p.distributor as ho " + + "FROM fulddaekning.adressetabel a " + + "LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " + + "WHERE rute IS NULL " + //Ingen dækning + "AND a.postnr BETWEEN ? AND ? " + + "AND a.postnr NOT BETWEEN 3900 and 3999 " + //Skip greenland + "AND a.postnr NOT BETWEEN 9000 AND 9499 " + // Skip nordjylland (DAO) + "AND latitude IS NOT NULL " + + "AND longitude IS NOT NULL " + + "AND gadeid IS NOT NULL " + + "AND (a.distributor IS NULL OR a.distributor<>'LUKKET') "; + PreparedStatement stmt = conn.prepareStatement(sql); + stmt.setInt(1, minPostnr); + stmt.setInt(2, maxPostnr); - saveStmt = writeConn.prepareStatement(sql); + List list = hentAdresseListe( stmt ); + alleIkkeDaekkede = list.toArray( new Adresse[ list.size() ] ); + logger.info("Analyserer ikke-daekkede adresser"); + + for (Adresse a : alleIkkeDaekkede) { + + + BoundingBox bbox; + + if (! postnumre.contains(a.postnr )) { + postnumre.add( a.postnr ); + + bbox = new BoundingBox(); + + bbCache.put( a.postnr, bbox); + + } else { + bbox = bbCache.get( a.postnr); + } + + bbox.latitudeMax = Math.max(bbox.latitudeMax, a.latitude); + bbox.latitudeMin = Math.min(bbox.latitudeMin, a.latitude); + bbox.longitudeMax = Math.max(bbox.longitudeMax, a.longitude); + bbox.longitudeMin = Math.min(bbox.longitudeMin, a.longitude); + + + } + return alleIkkeDaekkede; } - public void resetResultTable() throws SQLException { - logger.info("Truncating result table"); - String sql = "TRUNCATE TABLE fulddaekning.afstand_anden_rute_thn"; - writeConn.createStatement().executeUpdate(sql); + + + + public Map> getDaekkedeAdresserHO() { + return daekkedeAdresserHO; + } + + public int hentAlleDaekkedeAdresser() throws SQLException { + if ( alleAdresser == null ) { + String sql = "SELECT id,a.postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,p.distributor as ho " + + "FROM fulddaekning.adressetabel a " + + "LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " + + "WHERE rute IS NOT NULL " + + "AND latitude IS NOT NULL " + + "AND longitude IS NOT NULL " + + "AND a.distributor = ? "; + + // Forward only + concur_read_only + fetchsize tvinger driver til at hente en række af gangen (bedre performance ved store result sets) + // Se http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html + PreparedStatement stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); + stmt.setFetchSize(Integer.MIN_VALUE); + + stmt.setString(1, LookupMain.distributor); + + List list = hentAdresseListe( stmt ); + + alleAdresser = new ArrayList(); + alleAdresser.addAll(list); + + for(Adresse addr : list) { + short ho = addr.ho; + List hoListe = daekkedeAdresserHO.get(ho); + if ( hoListe == null) { + hoListe = new ArrayList(); + daekkedeAdresserHO.put(ho, hoListe); + } + + hoListe.add(addr); + + } + } + return alleAdresser.size(); } - public Connection getConnection(Properties conf) throws SQLException, IOException { + + + public synchronized void gemResultat(Adresse orgAdresse, Adresse bedsteAdresse, double bedsteAfstand) throws SQLException { + /*String sql = "INSERT INTO fulddaekning.afstand_anden_rute_thn (orgId,orgPostnr, orgAdresse,orgGadeid,orgHusnr,orgHusnrBogstav,orgLatitude,orgLongitude,orgRute,id,postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,afstand,`timestamp`) "+ + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, now() )"; + + PreparedStatement saveStmt = conn.prepareStatement(sql);*/ + + saveStmt.setInt(1, orgAdresse.id); + saveStmt.setShort(2, orgAdresse.postnr); + saveStmt.setString(3, orgAdresse.adresse); + saveStmt.setInt(4, orgAdresse.gadeid); + saveStmt.setShort(5, orgAdresse.husnr); + saveStmt.setString(6, orgAdresse.husnrbogstav); + saveStmt.setDouble(7, orgAdresse.latitude); + saveStmt.setDouble(8, orgAdresse.longitude); + saveStmt.setString(9, orgAdresse.rute); + + + saveStmt.setInt(10, bedsteAdresse.id); + saveStmt.setShort(11, bedsteAdresse.postnr); + saveStmt.setString(12, bedsteAdresse.adresse); + saveStmt.setInt(13, bedsteAdresse.gadeid); + saveStmt.setShort(14, bedsteAdresse.husnr); + saveStmt.setString(15, bedsteAdresse.husnrbogstav); + saveStmt.setDouble(16, bedsteAdresse.latitude); + saveStmt.setDouble(17, bedsteAdresse.longitude); + saveStmt.setString(18, bedsteAdresse.rute); + + saveStmt.setDouble(19, bedsteAfstand); + + saveStmt.addBatch(); + batchCount++; + if (batchCount >= 1000) { + saveStmt.executeBatch(); + batchCount = 0; + } + //saveStmt.executeUpdate(); + //saveStmt.clearParameters(); + + //saveStmt.close(); + } + + public synchronized void saveBatch() throws SQLException{ + saveStmt.executeBatch(); + batchCount = 0; + } + + + + protected ArrayList hentAdresseListe(PreparedStatement stmt) throws SQLException{ + ArrayList list = new ArrayList( 1000000 ); + + //logger.info("Starting query"); + ResultSet res = stmt.executeQuery(); + //logger.info("Starting exec query done"); + + while (res.next()) { + + double latitude = res.getDouble(7); + double longitude = res.getDouble(8); + + + Adresse adr = new Adresse(latitude,longitude); + + adr.id = res.getInt(1); + adr.postnr = res.getShort(2); + adr.adresse = res.getString(3); + adr.gadeid = res.getInt(4); + adr.husnr = res.getShort(5); + adr.husnrbogstav = husnrbogstavCache.getInstance( res.getString(6) ); + //adr.latitude = res.getDouble(7); + //adr.longitude = res.getDouble(8); + adr.rute = ruteCache.getInstance( res.getString(9) ); + adr.ho = res.getShort(10); + + list.add(adr); + + //logger.info( "Adress:" + adr); + } + + res.close(); + stmt.close(); + + return list; + } + + public Connection getConnection(SafeProperties conf) throws SQLException, IOException { - String db_host = conf.getProperty("DB_HOST"); - String db_user = conf.getProperty("DB_USER"); - String db_pass = conf.getProperty("DB_PASS"); + String db_host = conf.getSafeProperty("DB_HOST"); + String db_user = conf.getSafeProperty("DB_USER"); + String db_pass = conf.getSafeProperty("DB_PASS"); @@ -56,167 +289,173 @@ connectionProps.put("user", db_user); connectionProps.put("password", db_pass); - conn = DriverManager.getConnection( + //For debug output, tilføj denne til JDBC url'en: &profileSQL=true + conn = DriverManager.getConnection( "jdbc:mysql://" + db_host + - ":3306/", + ":3306/?rewriteBatchedStatements=true", connectionProps); logger.info("Connected to database"); return conn; } + - public BoundingBox getBoundingbox(int postnr) throws SQLException { + // ////////////////////////////////////////////////////////////////// + + /* + public Queue hentIkkedaekkedeAdresserCache(short postnr) { + List postListe = ikkeDaekkedePrPost.get(postnr); + return new ConcurrentLinkedQueue(postListe); + } + + public Adresse[] hentDaekkedeAdresserCache( BoundingBox bbox, Adresse adresseListe[] ) { + long start = System.currentTimeMillis(); + ArrayList list = new ArrayList(); + for (Adresse a : adresseListe) { + if ( a.latitude > bbox.latitudeMin && a.latitude< bbox.latitudeMax && a.longitude> bbox.longitudeMin && a.longitude < bbox.longitudeMax) { + list.add(a); + } + } + long stop = System.currentTimeMillis(); + logger.info("Elapsed cache: " + (stop - start)); + return list.toArray( new Adresse[ list.size() ] ); + } + + + /*public Adresse[] hentDaekkedeAdresserCache( BoundingBox bbox) { + return hentDaekkedeAdresserCache(bbox, alleAdresser); + }*/ + + /* + @Deprecated + private BoundingBox getBoundingboxFromDb_old(String postnr) throws SQLException { + String minPostnr = postnr.replace('x', '0'); + String maxPostnr = postnr.replace('x', '9'); + String sql = - "select max(latitude) latmax, min(latitude) latmin, max(longitude) lngmax,min(longitude) lngmin " + - "from fulddaekning.adressetabel WHERE postnr=? and rute is null;"; - + "SELECT max(latitude) latmax, min(latitude) latmin, max(longitude) lngmax,min(longitude) lngmin " + + "FROM fulddaekning.adressetabel WHERE postnr BETWEEN ? and ? and rute is null;"; + PreparedStatement stmt = conn.prepareStatement(sql); - stmt.setInt(1, postnr); - + stmt.setString(1, minPostnr); + stmt.setString(2, maxPostnr); + ResultSet res = stmt.executeQuery(); res.next(); //query returnerer altid 1 række - + BoundingBox bbox = new BoundingBox(); bbox.latitudeMax = res.getDouble("latmax"); bbox.latitudeMin = res.getDouble("latmin"); bbox.longitudeMax = res.getDouble("lngmax"); bbox.longitudeMin = res.getDouble("lngmin"); - + res.close(); stmt.close(); - + return bbox; } - - public Queue hentIkkedaekkedeAdresser(int postnr) throws SQLException { - ConcurrentLinkedQueue queue = new ConcurrentLinkedQueue(); - - String sql = "SELECT id,postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute FROM fulddaekning.adressetabel " + - "WHERE rute IS NULL " + //Ingen dækning - "AND postnr=? " + - "AND latitude IS NOT NULL " + - "AND longitude IS NOT NULL " + - "AND gadeid IS NOT NULL "; - PreparedStatement stmt = conn.prepareStatement(sql); - stmt.setInt(1, postnr); - - queue.addAll( hentAdresseListe( stmt ) ); - return queue; - } - - public List hentPostnumre() throws SQLException { - ArrayList list = new ArrayList(); - - - String sql = "SELECT postnr FROM fulddaekning.adressetabel WHERE distributor = 'DAO' and rute is not null GROUP BY postnr ORDER by postnr"; - PreparedStatement stmt = conn.prepareStatement(sql); - ResultSet res = stmt.executeQuery(); - - while (res.next()) { - int postnr = res.getInt("postnr"); - list.add(postnr); - } - res.close(); - stmt.close(); - - //list.add(8700); - - return list; - } - - public ArrayList hentDaekkedeAdresser( BoundingBox bbox) throws SQLException { - String sql = "SELECT id,postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute FROM fulddaekning.adressetabel " + + + @Deprecated + public Adresse[] hentDaekkedeAdresser_old( BoundingBox bbox) throws SQLException { + long start = System.currentTimeMillis(); + String sql = "SELECT id,a.postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,p.distributor as ho " + + "FROM fulddaekning.adressetabel a " + + "LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " + "WHERE rute IS NOT NULL " + "AND latitude BETWEEN ? AND ? " + "AND longitude BETWEEN ? AND ? " + - "AND distributor = 'DAO' "; - + "AND a.distributor = ? "; + // Forward only + concur_read_only + fetchsize tvinger driver til at hente en række af gangen (bedre performance ved store result sets) // Se http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html //PreparedStatement stmt = conn.prepareStatement(sql); PreparedStatement stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); - + stmt.setDouble(1, bbox.latitudeMin); stmt.setDouble(2, bbox.latitudeMax); stmt.setDouble(3, bbox.longitudeMin); stmt.setDouble(4, bbox.longitudeMax); - - return hentAdresseListe( stmt ); - - } + stmt.setString(5, LookupMain.distributor); + List list = hentAdresseListe( stmt ); + long stop = System.currentTimeMillis(); + logger.info("Elapsed DB: " + (stop - start)); + return list.toArray( new Adresse[ list.size() ] ); + } - public synchronized void gemResultat(Adresse orgAdresse, Adresse bedsteAdresse, double bedsteAfstand) throws SQLException { - /*String sql = "INSERT INTO fulddaekning.afstand_anden_rute_thn (orgId,orgPostnr, orgAdresse,orgGadeid,orgHusnr,orgHusnrBogstav,orgLatitude,orgLongitude,orgRute,id,postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,afstand,`timestamp`) "+ - "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, now() )"; + @Deprecated + public Queue hentIkkedaekkedeAdresser_old(String postnr) throws SQLException { - PreparedStatement saveStmt = conn.prepareStatement(sql);*/ - - saveStmt.setInt(1, orgAdresse.id); - saveStmt.setInt(2, orgAdresse.postnr); - saveStmt.setString(3, orgAdresse.adresse); - saveStmt.setInt(4, orgAdresse.gadeid); - saveStmt.setInt(5, orgAdresse.husnr); - saveStmt.setString(6, orgAdresse.husnrbogstav); - saveStmt.setDouble(7, orgAdresse.latitude); - saveStmt.setDouble(8, orgAdresse.longitude); - saveStmt.setString(9, orgAdresse.rute); - - - saveStmt.setInt(10, bedsteAdresse.id); - saveStmt.setInt(11, bedsteAdresse.postnr); - saveStmt.setString(12, bedsteAdresse.adresse); - saveStmt.setInt(13, bedsteAdresse.gadeid); - saveStmt.setInt(14, bedsteAdresse.husnr); - saveStmt.setString(15, bedsteAdresse.husnrbogstav); - saveStmt.setDouble(16, bedsteAdresse.latitude); - saveStmt.setDouble(17, bedsteAdresse.longitude); - saveStmt.setString(18, bedsteAdresse.rute); - - saveStmt.setDouble(19, bedsteAfstand); - - saveStmt.executeUpdate(); - saveStmt.clearParameters(); - - - //saveStmt.close(); + String minPostnr = postnr.replace('x', '0'); + String maxPostnr = postnr.replace('x', '9'); + ConcurrentLinkedQueue queue = new ConcurrentLinkedQueue(); + + String sql = "SELECT id,a.postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute,p.distributor as ho " + + "FROM fulddaekning.adressetabel a " + + "LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " + + "WHERE rute IS NULL " + //Ingen dækning + "AND a.postnr BETWEEN ? AND ? " + + "AND latitude IS NOT NULL " + + "AND longitude IS NOT NULL " + + "AND gadeid IS NOT NULL " + + "AND (a.distributor IS NULL OR a.distributor<>'LUKKET') "; + PreparedStatement stmt = conn.prepareStatement(sql); + stmt.setString(1, minPostnr); + stmt.setString(2, maxPostnr); + + queue.addAll( hentAdresseListe( stmt ) ); + return queue; } + @Deprecated + public List hentPostnumre_old() throws SQLException { + ArrayList list = new ArrayList(); + + Constants consts = Constants.getInstance(); + + /* + String sql = "SELECT postnr " + + "FROM fulddaekning.adressetabel " + + "WHERE postnr BETWEEN ? AND ? " + + "AND rute is null " + // Træk kun liste på postnumre hvor der er ikke-dækkede adresser + "GROUP BY postnr " + + "ORDER by postnr"; + * / + + + String sql = "SELECT rpad(left(postnr,?),'4', 'x') as postnr2 " + + "FROM fulddaekning.adressetabel " + + "WHERE postnr BETWEEN ? AND ? " + + "AND rute is null " + // Trae kun liste paa postnumre hvor der er ikke-daekede adresser + "AND (postnr NOT BETWEEN 3900 and 3999) " + //Skip alle groenlandske postnumre + "GROUP BY postnr2 " + + "ORDER by postnr2 "; - - protected ArrayList hentAdresseListe(PreparedStatement stmt) throws SQLException{ - ArrayList list = new ArrayList( 30000 ); - - //logger.info("Starting query"); + + + PreparedStatement stmt = conn.prepareStatement(sql); + //stmt.setString(1, Lookup.distributor ); + + stmt.setInt(1, consts.getPostnrGroup() ); + + stmt.setInt(2, consts.getMinPostnr()); + stmt.setInt(3, consts.getMaxPostnr()); ResultSet res = stmt.executeQuery(); - //logger.info("Starting exec query done"); - + while (res.next()) { - Adresse adr = new Adresse(); - adr.id = res.getInt("id"); - adr.postnr = res.getInt("postnr"); - adr.adresse = res.getString("adresse"); - adr.gadeid = res.getInt("gadeid"); - adr.husnr = res.getInt("husnr"); - adr.husnrbogstav = res.getString("husnrbogstav"); - adr.latitude = res.getDouble("latitude"); - adr.longitude = res.getDouble("longitude"); - adr.rute = res.getString("rute"); - - list.add(adr); - - //logger.info( "Adress:" + adr); + String postnr = res.getString("postnr2"); + list.add(postnr); } - res.close(); stmt.close(); - + + //list.add(8700); + return list; - } + }*/ - }