--- dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2014/09/11 18:04:29 2210 +++ dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2015/02/09 14:39:37 2261 @@ -7,6 +7,7 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; +import java.util.HashMap; import java.util.List; import java.util.Properties; import java.util.Queue; @@ -14,12 +15,16 @@ import java.util.logging.Logger; + public class Database { Logger logger = Logger.getLogger(Database.class.getName()); + int batchCount = 0; Connection conn; PreparedStatement saveStmt; + + private HashMap bbCache = new HashMap(); public Database(SafeProperties conf) throws SQLException,IOException { this.conn = getConnection( conf ); @@ -42,23 +47,43 @@ } 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"; + String sql = "DROP TABLE IF EXISTS fulddaekning.afstand_anden_rute_old" + ext; conn.createStatement().executeUpdate(sql); logger.info("Rename tables"); - sql = "RENAME TABLE fulddaekning.afstand_anden_rute TO fulddaekning.afstand_anden_rute_old, fulddaekning.afstand_anden_rute_ny TO fulddaekning.afstand_anden_rute"; + sql = "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: " + sql); conn.createStatement().executeUpdate(sql); - } + } + + public BoundingBox getBoundingbox(String postnr) throws SQLException { + BoundingBox bb = bbCache.get(postnr); + if ( bb == null ) { + bb = getBoundingboxFromDb(postnr); + bbCache.put(postnr, bb); + } else { + logger.info("Serving BB from cache"); + } + + return bb.clone();//never return the original / cached object + } - public BoundingBox getBoundingbox(int postnr) throws SQLException { + private BoundingBox getBoundingboxFromDb(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;"; + "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 @@ -74,44 +99,69 @@ return bbox; } + + - public Queue hentIkkedaekkedeAdresser(int postnr) throws SQLException { + public Queue hentIkkedaekkedeAdresser(String postnr) throws SQLException { + + String minPostnr = postnr.replace('x', '0'); + String maxPostnr = postnr.replace('x', '9'); + ConcurrentLinkedQueue queue = new ConcurrentLinkedQueue(); - String sql = "SELECT id,postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute " + - "FROM fulddaekning.adressetabel " + + 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 postnr=? " + + "AND a.postnr BETWEEN ? AND ? " + "AND latitude IS NOT NULL " + "AND longitude IS NOT NULL " + - "AND gadeid 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, postnr); + stmt.setString(1, minPostnr); + stmt.setString(2, maxPostnr); queue.addAll( hentAdresseListe( stmt ) ); return queue; } - public List hentPostnumre() throws SQLException { - ArrayList list = new ArrayList(); + public List hentPostnumre() throws SQLException { + ArrayList list = new ArrayList(); Constants consts = Constants.getInstance(); - + /* String sql = "SELECT postnr " + "FROM fulddaekning.adressetabel " + - //"WHERE distributor = ? and rute is not null " + "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 "; + + + PreparedStatement stmt = conn.prepareStatement(sql); //stmt.setString(1, Lookup.distributor ); - stmt.setInt(1, consts.getMinPostnr()); - stmt.setInt(2, consts.getMaxPostnr()); + + stmt.setInt(1, consts.getPostnrGroup() ); + + stmt.setInt(2, consts.getMinPostnr()); + stmt.setInt(3, consts.getMaxPostnr()); ResultSet res = stmt.executeQuery(); while (res.next()) { - int postnr = res.getInt("postnr"); + String postnr = res.getString("postnr2"); list.add(postnr); } res.close(); @@ -123,12 +173,13 @@ } public Adresse[] hentDaekkedeAdresser( BoundingBox bbox) throws SQLException { - String sql = "SELECT id,postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute " + - "FROM fulddaekning.adressetabel " + + 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 = ? "; + "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 @@ -140,13 +191,35 @@ stmt.setDouble(2, bbox.latitudeMax); stmt.setDouble(3, bbox.longitudeMin); stmt.setDouble(4, bbox.longitudeMax); - stmt.setString(5, Lookup.distributor); + stmt.setString(5, LookupMain.distributor); List list = hentAdresseListe( stmt ); return list.toArray( new Adresse[ list.size() ] ); - } + + + public Adresse[] hentAlleDaekkedeAdresser() throws SQLException { + 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); + 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 ); + 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`) "+ @@ -177,12 +250,21 @@ saveStmt.setDouble(19, bedsteAfstand); - saveStmt.executeUpdate(); - saveStmt.clearParameters(); - + saveStmt.addBatch(); + batchCount++; + if (batchCount >= 100) { + saveStmt.executeBatch(); + batchCount = 0; + } + //saveStmt.executeUpdate(); + //saveStmt.clearParameters(); //saveStmt.close(); - + } + + public synchronized void saveBatch() throws SQLException{ + saveStmt.executeBatch(); + batchCount = 0; } @@ -196,6 +278,8 @@ while (res.next()) { Adresse adr = new Adresse(); + + /* adr.id = res.getInt("id"); adr.postnr = res.getInt("postnr"); adr.adresse = res.getString("adresse"); @@ -205,6 +289,19 @@ adr.latitude = res.getDouble("latitude"); adr.longitude = res.getDouble("longitude"); adr.rute = res.getString("rute"); + adr.ho = res.getInt("ho"); + */ + + adr.id = res.getInt(1); + adr.postnr = res.getInt(2); + adr.adresse = res.getString(3); + adr.gadeid = res.getInt(4); + adr.husnr = res.getInt(5); + adr.husnrbogstav = res.getString(6); + adr.latitude = res.getDouble(7); + adr.longitude = res.getDouble(8); + adr.rute = res.getString(9); + adr.ho = res.getInt(10); list.add(adr); @@ -231,10 +328,11 @@ connectionProps.put("user", db_user); connectionProps.put("password", db_pass); + //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;