8 |
import java.util.ArrayList; |
import java.util.ArrayList; |
9 |
import java.util.List; |
import java.util.List; |
10 |
|
|
11 |
|
|
12 |
|
|
13 |
import dk.daoas.adressevedligehold.beans.Address; |
import dk.daoas.adressevedligehold.beans.Address; |
14 |
|
import dk.daoas.adressevedligehold.beans.Address.AddressState; |
15 |
|
import dk.daoas.adressevedligehold.tasks.TaskLogger; |
16 |
import dk.daoas.adressevedligehold.util.DeduplicateHelper; |
import dk.daoas.adressevedligehold.util.DeduplicateHelper; |
17 |
import dk.daoas.adressevedligehold.util.TimingHelper; |
import dk.daoas.adressevedligehold.util.TimingHelper; |
18 |
|
|
19 |
/* |
/* |
|
* TODO: Batched skrivning af updates |
|
|
* TODO: Skrivning af nye adresser |
|
20 |
* |
* |
21 |
*/ |
*/ |
22 |
|
|
23 |
public class DatabaseLayerImplementation { |
public class DatabaseCoverageUpdate { |
24 |
|
|
25 |
|
//static boolean DEBUG = false; |
26 |
|
|
27 |
static boolean DEBUG = true; |
private TaskLogger logger = TaskLogger.getInstance(); |
28 |
|
|
29 |
|
|
30 |
public List<Address> getAllAdresses() throws SQLException { |
public List<Address> getAllAdresses() throws SQLException { |
31 |
TimingHelper timing = new TimingHelper(); |
TimingHelper timing = new TimingHelper(); |
32 |
|
|
33 |
String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr >= 6000 " : ""; |
//String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr >= 6000 " : ""; |
34 |
|
|
35 |
String sql = |
String sql = |
36 |
"SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,latitude,longitude, " |
"SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,latitude,longitude, " |
37 |
+ "rutema,korelistema,ruteti,korelisteti,ruteon,korelisteon,ruteto,korelisteto,rutefr,korelistefr,rutelo,korelistelo,ruteso,korelisteso " |
+ "rutema,korelistema,ruteti,korelisteti,ruteon,korelisteon,ruteto,korelisteto,rutefr,korelistefr,rutelo,korelistelo,ruteso,korelisteso " |
38 |
+ "FROM fulddaekning.adressetabel " |
+ "FROM fulddaekning.adressetabel " |
39 |
+ debugFilter |
//+ debugFilter |
40 |
; |
; |
41 |
|
|
42 |
try ( Connection conn = DBConnection.getConnection(); |
try ( Connection conn = DBConnection.getConnection(); |
43 |
Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); |
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); |
44 |
) { |
) { |
45 |
stmt.setFetchSize(Integer.MIN_VALUE); |
stmt.setFetchSize(Integer.MIN_VALUE); |
46 |
ResultSet res = stmt.executeQuery(sql); |
ResultSet res = stmt.executeQuery(sql); |
96 |
res.close(); |
res.close(); |
97 |
|
|
98 |
|
|
99 |
System.out.println("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms"); |
logger.info("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms"); |
100 |
|
|
101 |
return list; |
return list; |
102 |
} |
} |
103 |
} |
} |
104 |
|
|
105 |
private void updateAddressesBatch(List<Address> addresses) throws SQLException { |
public void saveNewAddresses(List<Address> addresses) throws Exception { |
106 |
|
|
107 |
|
logger.info("DB::saveNewAddresses size:" + addresses.size() ); |
108 |
|
|
109 |
|
String sql = "INSERT IGNORE INTO fulddaekning.adressetabel " + |
110 |
|
"(vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,dbkbane,indlast,aendret,david,googlechecked," + |
111 |
|
"RuteMa,KorelisteMa,RuteTi,KorelisteTi,RuteOn,KorelisteOn,RuteTo,KorelisteTo,RuteFr,KorelisteFr,RuteLo,KorelisteLo,RuteSo,KorelisteSo, " + |
112 |
|
"kommentar,latitude,longitude) " + |
113 |
|
"VALUES " + |
114 |
|
"(?,?,?,?,?,?,?,?, now(), now(), ?, 0, " + |
115 |
|
"?,?,?,?,?,?,?,?,?,?,?,?,?,?," + |
116 |
|
"?,?,?) "; |
117 |
|
|
118 |
|
|
119 |
|
try (Connection con = DBConnection.getConnection()) { |
120 |
|
|
121 |
|
|
122 |
|
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
123 |
|
for (Address addr : addresses) { |
124 |
|
|
125 |
|
String david = String.format("%04d%03d%04d", addr.postnr, addr.kommunekode, addr.vejkode); |
126 |
|
|
127 |
|
stmt.setString(1, addr.vejnavn); |
128 |
|
stmt.setShort(2, addr.husnr); |
129 |
|
stmt.setString(3, addr.husnrbogstav); |
130 |
|
stmt.setShort(4, addr.kommunekode); |
131 |
|
stmt.setShort(5, addr.vejkode); |
132 |
|
stmt.setShort(6, addr.postnr); |
133 |
|
stmt.setInt(7, addr.gadeid); |
134 |
|
stmt.setShort(8, addr.dbkBane); |
135 |
|
stmt.setString(9, david); |
136 |
|
|
137 |
|
stmt.setString(10, addr.ruteMandag); |
138 |
|
stmt.setString(11, addr.korelisteMandag); |
139 |
|
stmt.setString(12, addr.ruteTirsdag); |
140 |
|
stmt.setString(13, addr.korelisteTirsdag); |
141 |
|
stmt.setString(14, addr.ruteOnsdag); |
142 |
|
stmt.setString(15, addr.korelisteOnsdag); |
143 |
|
stmt.setString(16, addr.ruteTorsdag); |
144 |
|
stmt.setString(17, addr.korelisteTorsdag); |
145 |
|
stmt.setString(18, addr.ruteFredag); |
146 |
|
stmt.setString(19, addr.korelisteFredag); |
147 |
|
stmt.setString(20, addr.ruteLordag); |
148 |
|
stmt.setString(21, addr.korelisteLordag); |
149 |
|
stmt.setString(22, addr.ruteSondag); |
150 |
|
stmt.setString(23, addr.korelisteSondag); |
151 |
|
|
152 |
|
stmt.setString(24, addr.kommentar); |
153 |
|
|
154 |
|
if (addr.latitude != 0.0) { |
155 |
|
stmt.setDouble(25, addr.latitude); |
156 |
|
} else { |
157 |
|
stmt.setNull(25, java.sql.Types.DOUBLE); |
158 |
|
} |
159 |
|
if (addr.longitude != 0.0) { |
160 |
|
stmt.setDouble(26, addr.longitude); |
161 |
|
} else { |
162 |
|
stmt.setNull(26, java.sql.Types.DOUBLE); |
163 |
|
} |
164 |
|
|
165 |
|
|
166 |
|
stmt.execute(); |
167 |
|
if (stmt.getUpdateCount() != 1) { |
168 |
|
logger.info("Error executing insert: " + addr); |
169 |
|
addr.state = AddressState.CREATE_FAIL; |
170 |
|
} |
171 |
|
|
172 |
|
} |
173 |
|
} |
174 |
|
|
175 |
|
//con.commit(); |
176 |
|
} catch (SQLException e) { |
177 |
|
logger.warning("Error inserting addresses", e ); |
178 |
|
throw e; |
179 |
|
} |
180 |
} |
} |
181 |
|
|
182 |
public void updateAddresses(List<Address> addresses) { |
public void updateAddresses(List<Address> addresses) throws Exception{ |
183 |
|
logger.info("DB::UpdateAddresses size:" + addresses.size() ); |
184 |
|
|
185 |
String sql = "UPDATE fulddaekning.adressetabel " + |
/*String sql = "UPDATE fulddaekning.adressetabel " + |
186 |
"SET " + |
"SET " + |
187 |
"RuteMa=?, KorelisteMa=?, " + |
"RuteMa=?, KorelisteMa=?, " + |
188 |
"RuteTi=?, KorelisteTi=?, " + |
"RuteTi=?, KorelisteTi=?, " + |
190 |
"RuteTo=?, KorelisteTo=?, " + |
"RuteTo=?, KorelisteTo=?, " + |
191 |
"RuteFr=?, KorelisteFr=?, " + |
"RuteFr=?, KorelisteFr=?, " + |
192 |
"RuteLo=?, KorelisteLo=?, " + |
"RuteLo=?, KorelisteLo=?, " + |
193 |
"RuteSo=?, KorelisteSo=? " + |
"RuteSo=?, KorelisteSo=?, " + |
194 |
"WHERE id=?"; |
"Distributor=?, " + |
195 |
|
"DBKBane=?, " + |
196 |
|
"Aendret=now() " + |
197 |
|
"WHERE id=? ";*/ |
198 |
|
String sql = "INSERT INTO fulddaekning.adressetabel " + |
199 |
|
"(id, " + |
200 |
|
"RuteMa, KorelisteMa, RuteTi, KorelisteTi, RuteOn, KorelisteOn, " + |
201 |
|
"RuteTo, KorelisteTo, RuteFr, KorelisteFr, RuteLo, KorelisteLo, " + |
202 |
|
"RuteSo, KorelisteSo, " + |
203 |
|
"Distributor, DBKBane, Aendret, " + |
204 |
|
"vejnavn,husnr,husnrbogstav,kommunekode,vejkode,gadeid,postnr) \n" + //Disse har ingen default value |
205 |
|
"VALUES " + |
206 |
|
"(?," + |
207 |
|
"?,?,?,?,?,?," + |
208 |
|
"?,?,?,?,?,?," + |
209 |
|
"?,?," + |
210 |
|
"?,?, now(), " + |
211 |
|
"'',0,'',0,0,0,0 ) \n" + |
212 |
|
|
213 |
|
"ON DUPLICATE KEY UPDATE " + |
214 |
|
"RuteMa=VALUES(RuteMa), KorelisteMa=VALUES(KorelisteMa), " + |
215 |
|
"RuteTi=VALUES(RuteTi), KorelisteTi=VALUES(KorelisteTi), " + |
216 |
|
"RuteOn=VALUES(RuteOn), KorelisteOn=VALUES(KorelisteOn), " + |
217 |
|
"RuteTo=VALUES(RuteTo), KorelisteTo=VALUES(KorelisteTo), " + |
218 |
|
"RuteFr=VALUES(RuteFr), KorelisteFr=VALUES(KorelisteFr), " + |
219 |
|
"RuteLo=VALUES(RuteLo), KorelisteLo=VALUES(KorelisteLo), " + |
220 |
|
"RuteSo=VALUES(RuteSo), KorelisteSo=VALUES(KorelisteSo), " + |
221 |
|
"Distributor=VALUES(Distributor), " + |
222 |
|
"DBKBane=VALUES(DBKBane) "; |
223 |
|
|
224 |
|
|
225 |
|
|
226 |
|
|
227 |
try (Connection con = DBConnection.getConnection()) { |
try (Connection con = DBConnection.getConnection()) { |
228 |
con.setAutoCommit(false); |
//con.setAutoCommit(false); |
229 |
|
|
230 |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
231 |
|
int count = 0; |
232 |
|
|
233 |
for (Address addr : addresses) { |
for (Address addr : addresses) { |
234 |
stmt.setString(1, addr.ruteMandag); |
stmt.setInt(1, addr.id); |
235 |
stmt.setString(2, addr.korelisteMandag); |
stmt.setString(2, addr.ruteMandag); |
236 |
stmt.setString(3, addr.ruteTirsdag); |
stmt.setString(3, addr.korelisteMandag); |
237 |
stmt.setString(4, addr.korelisteTirsdag); |
stmt.setString(4, addr.ruteTirsdag); |
238 |
stmt.setString(5, addr.ruteOnsdag); |
stmt.setString(5, addr.korelisteTirsdag); |
239 |
stmt.setString(6, addr.korelisteOnsdag); |
stmt.setString(6, addr.ruteOnsdag); |
240 |
stmt.setString(7, addr.ruteTorsdag); |
stmt.setString(7, addr.korelisteOnsdag); |
241 |
stmt.setString(8, addr.korelisteTorsdag); |
stmt.setString(8, addr.ruteTorsdag); |
242 |
stmt.setString(9, addr.ruteFredag); |
stmt.setString(9, addr.korelisteTorsdag); |
243 |
stmt.setString(10, addr.korelisteFredag); |
stmt.setString(10, addr.ruteFredag); |
244 |
stmt.setString(11, addr.ruteLordag); |
stmt.setString(11, addr.korelisteFredag); |
245 |
stmt.setString(12, addr.korelisteLordag); |
stmt.setString(12, addr.ruteLordag); |
246 |
stmt.setString(13, addr.ruteSondag); |
stmt.setString(13, addr.korelisteLordag); |
247 |
stmt.setString(14, addr.korelisteSondag); |
stmt.setString(14, addr.ruteSondag); |
248 |
|
stmt.setString(15, addr.korelisteSondag); |
|
stmt.setInt(15, addr.id); |
|
249 |
|
|
250 |
|
stmt.setString(16, addr.distributor); |
251 |
|
stmt.setInt(17, addr.dbkBane); |
252 |
|
|
253 |
|
|
|
stmt.execute(); |
|
|
if (stmt.getUpdateCount() != 1) { |
|
|
System.out.println("Error executing update"); |
|
|
} |
|
254 |
|
|
255 |
|
stmt.addBatch(); |
256 |
|
count++; |
257 |
|
if ( (count%200) == 0) { |
258 |
|
stmt.executeBatch(); |
259 |
|
|
260 |
|
if ( (count%50000) == 0 ) { |
261 |
|
logger.info("Update count " + count); |
262 |
|
} |
263 |
|
} |
264 |
} |
} |
265 |
|
|
266 |
|
stmt.executeBatch(); //update remainding queries |
267 |
|
|
268 |
} |
} |
269 |
|
|
270 |
con.commit(); |
//con.commit(); |
271 |
} catch (SQLException e) { |
} catch (SQLException e) { |
272 |
System.out.println("Error updating addresses" + e.getMessage() ); |
logger.warning("Error updating addresses", e ); |
273 |
|
throw e; |
274 |
} |
} |
275 |
} |
} |
276 |
|
|
277 |
|
/* |
278 |
private static String nullify(String str) { |
private static String nullify(String str) { |
279 |
if (str == null) |
if (str == null) |
280 |
return null; |
return null; |