--- dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2014/05/07 08:10:45 2152 +++ dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2014/09/15 10:37:25 2220 @@ -14,17 +14,19 @@ import java.util.logging.Logger; + public class Database { Logger logger = Logger.getLogger(Database.class.getName()); + int batchCount = 0; Connection conn; PreparedStatement saveStmt; - public Database(Properties conf) throws SQLException,IOException { + public Database(SafeProperties conf) throws SQLException,IOException { this.conn = getConnection( conf ); - 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`) "+ + 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); @@ -32,17 +34,30 @@ } public void resetResultTable() throws SQLException { - logger.info("Truncating result table"); - String sql = "TRUNCATE TABLE fulddaekning.afstand_anden_rute_thn"; + 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 { + logger.info("Dropping old backup table (if exists)"); + String sql = "DROP TABLE IF EXISTS fulddaekning.afstand_anden_rute_old"; + 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"; + conn.createStatement().executeUpdate(sql); } public BoundingBox getBoundingbox(int postnr) throws SQLException { 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=? and rute is null;"; PreparedStatement stmt = conn.prepareStatement(sql); stmt.setInt(1, postnr); @@ -65,7 +80,8 @@ 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 " + + 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 " + @@ -80,10 +96,20 @@ public List hentPostnumre() throws SQLException { ArrayList list = new ArrayList(); + + Constants consts = Constants.getInstance(); - String sql = "SELECT postnr FROM fulddaekning.adressetabel WHERE distributor = 'DAO' and rute is not null GROUP BY postnr ORDER by postnr"; + String sql = "SELECT postnr " + + "FROM fulddaekning.adressetabel " + + //"WHERE distributor = ? and rute is not null " + + "WHERE postnr BETWEEN ? AND ? " + + "GROUP BY postnr " + + "ORDER by postnr"; PreparedStatement stmt = conn.prepareStatement(sql); + //stmt.setString(1, Lookup.distributor ); + stmt.setInt(1, consts.getMinPostnr()); + stmt.setInt(2, consts.getMaxPostnr()); ResultSet res = stmt.executeQuery(); while (res.next()) { @@ -98,12 +124,13 @@ return list; } - public ArrayList hentDaekkedeAdresser( BoundingBox bbox) throws SQLException { - String sql = "SELECT id,postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute FROM fulddaekning.adressetabel " + + public Adresse[] hentDaekkedeAdresser( BoundingBox bbox) throws SQLException { + String sql = "SELECT id,postnr,adresse,gadeid,husnr,husnrbogstav,latitude,longitude,rute " + + "FROM fulddaekning.adressetabel " + "WHERE rute IS NOT NULL " + "AND latitude BETWEEN ? AND ? " + "AND longitude BETWEEN ? AND ? " + - "AND distributor = 'DAO' "; + "AND 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 @@ -115,8 +142,10 @@ stmt.setDouble(2, bbox.latitudeMax); stmt.setDouble(3, bbox.longitudeMin); stmt.setDouble(4, bbox.longitudeMax); + stmt.setString(5, Lookup.distributor); - return hentAdresseListe( stmt ); + List list = hentAdresseListe( stmt ); + return list.toArray( new Adresse[ list.size() ] ); } @@ -150,18 +179,27 @@ 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; } protected ArrayList hentAdresseListe(PreparedStatement stmt) throws SQLException{ - ArrayList list = new ArrayList( 30000 ); + ArrayList list = new ArrayList( 1000000 ); //logger.info("Starting query"); ResultSet res = stmt.executeQuery(); @@ -190,11 +228,11 @@ return list; } - public Connection getConnection(Properties conf) throws SQLException, IOException { + 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"); @@ -204,10 +242,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;