34 |
DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>(); |
DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>(); |
35 |
DeduplicateHelper<String> ruteCache = new DeduplicateHelper<String>(); |
DeduplicateHelper<String> ruteCache = new DeduplicateHelper<String>(); |
36 |
|
|
37 |
Set<Integer> postnumre = new TreeSet<Integer>(); |
Set<Short> postnumre = new TreeSet<Short>(); |
38 |
|
|
39 |
Map<Integer, List<Adresse>> ikkeDaekkedePrPost = new HashMap<Integer, List<Adresse>>(); |
Map<Short, List<Adresse>> ikkeDaekkedePrPost = new HashMap<Short, List<Adresse>>(); |
40 |
|
|
41 |
|
|
42 |
private HashMap<Integer,BoundingBox> bbCache = new HashMap<Integer,BoundingBox>(); |
private HashMap<Short,BoundingBox> bbCache = new HashMap<Short,BoundingBox>(); |
43 |
|
|
44 |
public Database(SafeProperties conf) throws SQLException,IOException { |
public Database(SafeProperties conf) throws SQLException,IOException { |
45 |
this.conn = getConnection( conf ); |
this.conn = getConnection( conf ); |
76 |
conn.createStatement().executeUpdate(sql); |
conn.createStatement().executeUpdate(sql); |
77 |
} |
} |
78 |
|
|
79 |
public BoundingBox getBoundingbox(int postnr) { |
public BoundingBox getBoundingbox(short postnr) { |
80 |
BoundingBox bb = bbCache.get(postnr); |
BoundingBox bb = bbCache.get(postnr); |
81 |
return bb.clone();//never return the original / cached object |
return bb.clone();//never return the original / cached object |
82 |
} |
} |
83 |
|
|
84 |
|
|
85 |
public Set<Integer> hentPostnumreCache() { |
public Set<Short> hentPostnumreCache() { |
86 |
return postnumre; |
return postnumre; |
87 |
} |
} |
88 |
|
|
139 |
} |
} |
140 |
} |
} |
141 |
|
|
142 |
public Queue<Adresse> hentIkkedaekkedeAdresserCache(int postnr) { |
public Queue<Adresse> hentIkkedaekkedeAdresserCache(short postnr) { |
143 |
List<Adresse> postListe = ikkeDaekkedePrPost.get(postnr); |
List<Adresse> postListe = ikkeDaekkedePrPost.get(postnr); |
144 |
|
|
145 |
return new ConcurrentLinkedQueue<Adresse>(postListe); |
return new ConcurrentLinkedQueue<Adresse>(postListe); |
146 |
} |
} |
|
|
|
147 |
|
|
148 |
public Adresse[] hentDaekkedeAdresserCache( BoundingBox bbox) { |
public Adresse[] hentDaekkedeAdresserCache( BoundingBox bbox, Adresse adresseListe[] ) { |
149 |
long start = System.currentTimeMillis(); |
long start = System.currentTimeMillis(); |
150 |
ArrayList<Adresse> list = new ArrayList<Adresse>(); |
ArrayList<Adresse> list = new ArrayList<Adresse>(); |
151 |
for (Adresse a : alleAdresser) { |
for (Adresse a : adresseListe) { |
152 |
if ( a.latitude > bbox.latitudeMin && a.latitude< bbox.latitudeMax && a.longitude> bbox.longitudeMin && a.longitude < bbox.longitudeMax) { |
if ( a.latitude > bbox.latitudeMin && a.latitude< bbox.latitudeMax && a.longitude> bbox.longitudeMin && a.longitude < bbox.longitudeMax) { |
153 |
list.add(a); |
list.add(a); |
154 |
} |
} |
157 |
logger.info("Elapsed cache: " + (stop - start)); |
logger.info("Elapsed cache: " + (stop - start)); |
158 |
return list.toArray( new Adresse[ list.size() ] ); |
return list.toArray( new Adresse[ list.size() ] ); |
159 |
} |
} |
160 |
|
|
161 |
|
|
162 |
|
public Adresse[] hentDaekkedeAdresserCache( BoundingBox bbox) { |
163 |
|
return hentDaekkedeAdresserCache(bbox, alleAdresser); |
164 |
|
} |
165 |
|
|
166 |
|
|
167 |
|
|
168 |
|
|
169 |
public Adresse[] hentAlleDaekkedeAdresser() throws SQLException { |
public Adresse[] hentAlleDaekkedeAdresser() throws SQLException { |
178 |
|
|
179 |
// 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) |
180 |
// 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 |
|
//PreparedStatement stmt = conn.prepareStatement(sql); |
|
181 |
PreparedStatement stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); |
PreparedStatement stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); |
182 |
stmt.setFetchSize(Integer.MIN_VALUE); |
stmt.setFetchSize(Integer.MIN_VALUE); |
183 |
|
|
198 |
PreparedStatement saveStmt = conn.prepareStatement(sql);*/ |
PreparedStatement saveStmt = conn.prepareStatement(sql);*/ |
199 |
|
|
200 |
saveStmt.setInt(1, orgAdresse.id); |
saveStmt.setInt(1, orgAdresse.id); |
201 |
saveStmt.setInt(2, orgAdresse.postnr); |
saveStmt.setShort(2, orgAdresse.postnr); |
202 |
saveStmt.setString(3, orgAdresse.adresse); |
saveStmt.setString(3, orgAdresse.adresse); |
203 |
saveStmt.setInt(4, orgAdresse.gadeid); |
saveStmt.setInt(4, orgAdresse.gadeid); |
204 |
saveStmt.setInt(5, orgAdresse.husnr); |
saveStmt.setShort(5, orgAdresse.husnr); |
205 |
saveStmt.setString(6, orgAdresse.husnrbogstav); |
saveStmt.setString(6, orgAdresse.husnrbogstav); |
206 |
saveStmt.setDouble(7, orgAdresse.latitude); |
saveStmt.setDouble(7, orgAdresse.latitude); |
207 |
saveStmt.setDouble(8, orgAdresse.longitude); |
saveStmt.setDouble(8, orgAdresse.longitude); |
209 |
|
|
210 |
|
|
211 |
saveStmt.setInt(10, bedsteAdresse.id); |
saveStmt.setInt(10, bedsteAdresse.id); |
212 |
saveStmt.setInt(11, bedsteAdresse.postnr); |
saveStmt.setShort(11, bedsteAdresse.postnr); |
213 |
saveStmt.setString(12, bedsteAdresse.adresse); |
saveStmt.setString(12, bedsteAdresse.adresse); |
214 |
saveStmt.setInt(13, bedsteAdresse.gadeid); |
saveStmt.setInt(13, bedsteAdresse.gadeid); |
215 |
saveStmt.setInt(14, bedsteAdresse.husnr); |
saveStmt.setShort(14, bedsteAdresse.husnr); |
216 |
saveStmt.setString(15, bedsteAdresse.husnrbogstav); |
saveStmt.setString(15, bedsteAdresse.husnrbogstav); |
217 |
saveStmt.setDouble(16, bedsteAdresse.latitude); |
saveStmt.setDouble(16, bedsteAdresse.latitude); |
218 |
saveStmt.setDouble(17, bedsteAdresse.longitude); |
saveStmt.setDouble(17, bedsteAdresse.longitude); |
222 |
|
|
223 |
saveStmt.addBatch(); |
saveStmt.addBatch(); |
224 |
batchCount++; |
batchCount++; |
225 |
if (batchCount >= 100) { |
if (batchCount >= 1000) { |
226 |
saveStmt.executeBatch(); |
saveStmt.executeBatch(); |
227 |
batchCount = 0; |
batchCount = 0; |
228 |
} |
} |
263 |
*/ |
*/ |
264 |
|
|
265 |
adr.id = res.getInt(1); |
adr.id = res.getInt(1); |
266 |
adr.postnr = res.getInt(2); |
adr.postnr = res.getShort(2); |
267 |
adr.adresse = res.getString(3); |
adr.adresse = res.getString(3); |
268 |
adr.gadeid = res.getInt(4); |
adr.gadeid = res.getInt(4); |
269 |
adr.husnr = res.getInt(5); |
adr.husnr = res.getShort(5); |
270 |
adr.husnrbogstav = husnrbogstavCache.getInstance( res.getString(6) ); |
adr.husnrbogstav = husnrbogstavCache.getInstance( res.getString(6) ); |
271 |
adr.latitude = res.getDouble(7); |
adr.latitude = res.getDouble(7); |
272 |
adr.longitude = res.getDouble(8); |
adr.longitude = res.getDouble(8); |
273 |
adr.rute = ruteCache.getInstance( res.getString(9) ); |
adr.rute = ruteCache.getInstance( res.getString(9) ); |
274 |
adr.ho = res.getInt(10); |
adr.ho = res.getShort(10); |
275 |
|
|
276 |
list.add(adr); |
list.add(adr); |
277 |
|
|
310 |
|
|
311 |
|
|
312 |
// ////////////////////////////////////////////////////////////////// |
// ////////////////////////////////////////////////////////////////// |
313 |
|
/* |
314 |
@Deprecated |
@Deprecated |
315 |
private BoundingBox getBoundingboxFromDb_old(String postnr) throws SQLException { |
private BoundingBox getBoundingboxFromDb_old(String postnr) throws SQLException { |
316 |
String minPostnr = postnr.replace('x', '0'); |
String minPostnr = postnr.replace('x', '0'); |
406 |
"AND rute is null " + // Træk kun liste på postnumre hvor der er ikke-dækkede adresser |
"AND rute is null " + // Træk kun liste på postnumre hvor der er ikke-dækkede adresser |
407 |
"GROUP BY postnr " + |
"GROUP BY postnr " + |
408 |
"ORDER by postnr"; |
"ORDER by postnr"; |
409 |
*/ |
* / |
410 |
|
|
411 |
|
|
412 |
String sql = "SELECT rpad(left(postnr,?),'4', 'x') as postnr2 " + |
String sql = "SELECT rpad(left(postnr,?),'4', 'x') as postnr2 " + |
438 |
//list.add(8700); |
//list.add(8700); |
439 |
|
|
440 |
return list; |
return list; |
441 |
} |
}*/ |
442 |
|
|
443 |
|
|
444 |
} |
} |