--- dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2014/04/30 12:53:01 2147 +++ dao/FuldDaekningWorker/src/dk/daoas/fulddaekning/Database.java 2014/09/12 15:23:06 2218 @@ -16,83 +16,70 @@ public class Database { Logger logger = Logger.getLogger(Database.class.getName()); - - + + Connection conn; - Connection writeConn; - PreparedStatement saveStmt; - public Database(Properties conf) throws SQLException,IOException { - conn = getConnection(conf); - writeConn = 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`) "+ - "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, now() )"; - - saveStmt = writeConn.prepareStatement(sql); - - } - - public void resetResultTable() throws SQLException { - logger.info("Truncating result table"); - String sql = "TRUNCATE TABLE fulddaekning.afstand_anden_rute_thn"; - writeConn.createStatement().executeUpdate(sql); - - } - - public Connection getConnection(Properties 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"); + 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() )"; - Connection conn = null; - Properties connectionProps = new Properties(); - connectionProps.put("user", db_user); - connectionProps.put("password", db_pass); + saveStmt = conn.prepareStatement(sql); - conn = DriverManager.getConnection( - "jdbc:mysql://" + - db_host + - ":3306/", - connectionProps); - logger.info("Connected to database"); - return conn; } - + + 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 { + 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); - + 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 " + + + 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 " + @@ -100,60 +87,73 @@ "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"; + Constants consts = Constants.getInstance(); + + + 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()) { 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 " + + + 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 //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, Lookup.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`) "+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, now() )"; - + PreparedStatement saveStmt = conn.prepareStatement(sql);*/ - + saveStmt.setInt(1, orgAdresse.id); saveStmt.setInt(2, orgAdresse.postnr); saveStmt.setString(3, orgAdresse.adresse); @@ -163,8 +163,8 @@ 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); @@ -174,26 +174,26 @@ 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(); - + } - - - + + + 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(); //logger.info("Starting exec query done"); - + while (res.next()) { Adresse adr = new Adresse(); adr.id = res.getInt("id"); @@ -205,18 +205,39 @@ adr.latitude = res.getDouble("latitude"); adr.longitude = res.getDouble("longitude"); adr.rute = res.getString("rute"); - + 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.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); + + conn = DriverManager.getConnection( + "jdbc:mysql://" + + db_host + + ":3306/?rewriteBatchedStatements=true", + connectionProps); + logger.info("Connected to database"); + return conn; + } }