22 |
|
|
23 |
|
|
24 |
|
|
25 |
public class Database { |
public class DatabaseRouteDistance { |
26 |
private TaskLogger logger = TaskLogger.getInstance(); |
private TaskLogger logger = TaskLogger.getInstance(); |
27 |
|
|
28 |
int batchCount = 0; |
int batchCount = 0; |
35 |
|
|
36 |
Map<Short,List<Address>> daekkedeAddressrHO = new HashMap<Short,List<Address>>(); |
Map<Short,List<Address>> daekkedeAddressrHO = new HashMap<Short,List<Address>>(); |
37 |
|
|
38 |
|
String weekdayField; |
39 |
|
|
40 |
|
|
41 |
|
|
42 |
|
|
53 |
|
|
54 |
private HashMap<Short,BoundingBox> bbCache = new HashMap<Short,BoundingBox>(); |
private HashMap<Short,BoundingBox> bbCache = new HashMap<Short,BoundingBox>(); |
55 |
|
|
56 |
public Database(Connection conn, boolean isIncremental) throws SQLException,IOException { |
public DatabaseRouteDistance(Connection conn, boolean isIncremental, String weekdayField) throws SQLException,IOException { |
57 |
this.conn = conn; |
this.conn = conn; |
58 |
|
|
59 |
String newExt = ""; |
String newExt = ""; |
60 |
if (isIncremental == false) { |
if (isIncremental == false) { |
61 |
newExt = "_ny"; |
newExt = "_ny"; |
62 |
} |
} |
63 |
|
|
64 |
|
this.weekdayField = weekdayField; |
65 |
|
|
66 |
String sql = "INSERT INTO fulddaekning.afstand_anden_rute" + newExt + " (orgId,orgPostnr, orgAdresse,orgGadeid,orgHusnr,orgHusnrBogstav,orgRute,id,postnr,adresse,gadeid,husnr,husnrbogstav,rute,afstand,`timestamp`) "+ |
String sql = "INSERT IGNORE INTO fulddaekning.afstand_anden_rute" + newExt + " (orgId,orgPostnr, orgAdresse,orgGadeid,orgHusnr,orgHusnrBogstav,orgRute,id,postnr,adresse,gadeid,husnr,husnrbogstav,rute,afstand,`timestamp`) "+ |
67 |
"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, now() )"; |
"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, now() )"; |
68 |
|
|
69 |
saveStmt = conn.prepareStatement(sql); |
saveStmt = conn.prepareStatement(sql); |
141 |
"FROM fulddaekning.adressetabel a " + |
"FROM fulddaekning.adressetabel a " + |
142 |
"LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " + |
"LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " + |
143 |
placeHolder1 + |
placeHolder1 + |
144 |
"WHERE ruteMa IS NULL " + //Ingen dækning |
"WHERE " + weekdayField + " IS NULL " + //Ingen dækning |
145 |
"AND a.postnr BETWEEN ? AND ? " + |
"AND a.postnr BETWEEN ? AND ? " + |
146 |
"AND latitude IS NOT NULL " + |
"AND latitude IS NOT NULL " + |
147 |
"AND longitude IS NOT NULL " + |
"AND longitude IS NOT NULL " + |
215 |
String sql = "SELECT id,a.postnr,vejnavn,gadeid,husnr,husnrbogstav,latitude,longitude,ruteMa,p.distributor as ho " + |
String sql = "SELECT id,a.postnr,vejnavn,gadeid,husnr,husnrbogstav,latitude,longitude,ruteMa,p.distributor as ho " + |
216 |
"FROM fulddaekning.adressetabel a " + |
"FROM fulddaekning.adressetabel a " + |
217 |
"LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " + |
"LEFT JOIN bogleveringer.postnummerdistributor p on (a.postnr=p.postnr) " + |
218 |
"WHERE ruteMa IS NOT NULL " + |
"WHERE " + weekdayField + " IS NOT NULL " + |
219 |
"AND latitude IS NOT NULL " + |
"AND latitude IS NOT NULL " + |
220 |
"AND longitude IS NOT NULL " + |
"AND longitude IS NOT NULL " + |
221 |
"AND a.distributor = ? "; |
"AND a.distributor = ? "; |
227 |
// Forward only + concur_read_only + fetchsize tvinger driver til at hente en række af gangen (bedre performance ved store result sets) |
// Forward only + concur_read_only + fetchsize tvinger driver til at hente en række af gangen (bedre performance ved store result sets) |
228 |
// Se http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html |
// Se http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html |
229 |
|
|
230 |
try (PreparedStatement stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY)) { |
try (PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) { |
231 |
stmt.setFetchSize(Integer.MIN_VALUE); |
stmt.setFetchSize(Integer.MIN_VALUE); |
232 |
|
|
233 |
stmt.setString(1, distributor); |
stmt.setString(1, distributor); |
298 |
|
|
299 |
@SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE") |
@SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE") |
300 |
public void prepareIncrementalSearch() throws SQLException { |
public void prepareIncrementalSearch() throws SQLException { |
301 |
|
if (isIncremental == false) { |
302 |
|
return; |
303 |
|
} |
304 |
|
|
305 |
logger.info("prepareIncrementalSearch() "); |
logger.info("prepareIncrementalSearch() "); |
306 |
|
|
309 |
String sql1 = "UPDATE fulddaekning.afstand_anden_rute" + consts.getTableExtension() + " afstand " + |
String sql1 = "UPDATE fulddaekning.afstand_anden_rute" + consts.getTableExtension() + " afstand " + |
310 |
"JOIN fulddaekning.adressetabel addr ON (afstand.id = addr.id) " + |
"JOIN fulddaekning.adressetabel addr ON (afstand.id = addr.id) " + |
311 |
"SET afstand.id = 0 " + |
"SET afstand.id = 0 " + |
312 |
"WHERE addr.rutema is null " |
"WHERE ruteMa is null " |
313 |
; |
; |
314 |
|
|
315 |
String sql2 = "DELETE FROM fulddaekning.afstand_anden_rute" + consts.getTableExtension() + " " + |
String sql2 = "DELETE FROM fulddaekning.afstand_anden_rute" + consts.getTableExtension() + " " + |