--- dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2014/05/07 07:59:03 2151 +++ dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2014/09/20 11:16:56 2224 @@ -1,27 +1,32 @@ package dk.daoas.fulddaekning; +import java.io.IOException; import java.sql.Connection; +import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; +import java.util.Properties; import java.util.Queue; import java.util.concurrent.ConcurrentLinkedQueue; import java.util.logging.Logger; + public class Database { Logger logger = Logger.getLogger(Database.class.getName()); + int batchCount = 0; Connection conn; PreparedStatement saveStmt; - public Database(Connection conn) throws SQLException { - this.conn = conn; + 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); @@ -29,17 +34,33 @@ } 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 { + 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"); + 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; + 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); @@ -62,7 +83,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 " + @@ -77,10 +99,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()) { @@ -95,12 +127,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 @@ -112,8 +145,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() ] ); } @@ -147,18 +182,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(); @@ -187,6 +231,28 @@ return list; } - + public Connection getConnection(SafeProperties conf) throws SQLException, IOException { + + String db_host = conf.getSafeProperty("DB_HOST"); + String db_user = conf.getSafeProperty("DB_USER"); + String db_pass = conf.getSafeProperty("DB_PASS"); + + + + + Connection conn = null; + Properties connectionProps = new Properties(); + 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/?rewriteBatchedStatements=true", + connectionProps); + logger.info("Connected to database"); + return conn; + } }