1 |
package dk.daoas.adressevedligehold.db; |
package dk.daoas.adressevedligehold.db; |
2 |
|
|
3 |
|
import java.io.InputStream; |
4 |
import java.sql.Connection; |
import java.sql.Connection; |
5 |
import java.sql.PreparedStatement; |
import java.sql.PreparedStatement; |
6 |
import java.sql.ResultSet; |
import java.sql.ResultSet; |
8 |
import java.sql.Statement; |
import java.sql.Statement; |
9 |
import java.util.ArrayList; |
import java.util.ArrayList; |
10 |
import java.util.List; |
import java.util.List; |
11 |
|
import java.util.Properties; |
|
|
|
12 |
|
|
13 |
import dk.daoas.adressevedligehold.beans.Address; |
import dk.daoas.adressevedligehold.beans.Address; |
14 |
import dk.daoas.adressevedligehold.beans.Address.AddressState; |
import dk.daoas.adressevedligehold.beans.Address.AddressState; |
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; |
//static boolean DEBUG = false; |
26 |
|
|
27 |
private TaskLogger logger = TaskLogger.getInstance(); |
private TaskLogger logger = TaskLogger.getInstance(); |
28 |
|
|
29 |
|
Properties queries; |
30 |
|
|
31 |
|
public DatabaseCoverageUpdate() throws SQLException { |
32 |
|
|
33 |
|
//On some platforms it may be necessary to load as "META-INF/queries.properties" |
34 |
|
ClassLoader classLoader = Thread.currentThread().getContextClassLoader(); |
35 |
|
InputStream is = classLoader.getResourceAsStream("sql.xml"); |
36 |
|
|
37 |
|
queries = new Properties(); |
38 |
|
try { |
39 |
|
queries.loadFromXML(is); |
40 |
|
} catch (Exception e) { |
41 |
|
throw new SQLException("Error loading queries", e); |
42 |
|
} |
43 |
|
|
44 |
|
} |
45 |
|
|
46 |
|
|
47 |
public List<Address> getAllAdresses() throws SQLException { |
public List<Address> getAllAdresses() throws SQLException { |
48 |
TimingHelper timing = new TimingHelper(); |
TimingHelper timing = new TimingHelper(); |
57 |
; |
; |
58 |
|
|
59 |
try ( Connection conn = DBConnection.getConnection(); |
try ( Connection conn = DBConnection.getConnection(); |
60 |
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); |
61 |
) { |
) { |
62 |
stmt.setFetchSize(Integer.MIN_VALUE); |
stmt.setFetchSize(Integer.MIN_VALUE); |
63 |
ResultSet res = stmt.executeQuery(sql); |
ResultSet res = stmt.executeQuery(sql); |
119 |
} |
} |
120 |
} |
} |
121 |
|
|
122 |
public void saveNewAddresses(List<Address> addresses) { |
public void saveNewAddresses(List<Address> addresses) throws Exception { |
123 |
|
|
124 |
logger.info("DB::saveNewAddresses size:" + addresses.size() ); |
logger.info("DB::saveNewAddresses size:" + addresses.size() ); |
125 |
|
|
126 |
String sql = "INSERT IGNORE INTO fulddaekning.adressetabel " + |
final String SQL_INSERT_NEW_ADDRESS = queries.getProperty("SQL_INSERT_NEW_ADDRESS"); |
127 |
"(vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,dbkbane,indlast,aendret,david,googlechecked," + |
|
128 |
"RuteMa,KorelisteMa,RuteTi,KorelisteTi,RuteOn,KorelisteOn,RuteTo,KorelisteTo,RuteFr,KorelisteFr,RuteLo,KorelisteLo,RuteSo,KorelisteSo) " + |
|
|
"VALUES " + |
|
|
"(?,?,?,?,?,?,?,?, now(), now(), ?, 0, " + |
|
|
"?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; |
|
129 |
|
|
130 |
|
|
131 |
try (Connection con = DBConnection.getConnection()) { |
try (Connection con = DBConnection.getConnection()) { |
132 |
//con.setAutoCommit(false); |
|
133 |
|
|
134 |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
try (PreparedStatement stmt = con.prepareStatement(SQL_INSERT_NEW_ADDRESS)) { |
135 |
for (Address addr : addresses) { |
for (Address addr : addresses) { |
136 |
|
|
137 |
String david = String.format("%04d%03d%04d", addr.postnr, addr.kommunekode, addr.vejkode); |
String david = String.format("%04d%03d%04d", addr.postnr, addr.kommunekode, addr.vejkode); |
160 |
stmt.setString(21, addr.korelisteLordag); |
stmt.setString(21, addr.korelisteLordag); |
161 |
stmt.setString(22, addr.ruteSondag); |
stmt.setString(22, addr.ruteSondag); |
162 |
stmt.setString(23, addr.korelisteSondag); |
stmt.setString(23, addr.korelisteSondag); |
163 |
|
|
164 |
|
stmt.setString(24, addr.kommentar); |
165 |
|
|
166 |
|
if (addr.latitude != 0.0) { |
167 |
|
stmt.setDouble(25, addr.latitude); |
168 |
|
} else { |
169 |
|
stmt.setNull(25, java.sql.Types.DOUBLE); |
170 |
|
} |
171 |
|
if (addr.longitude != 0.0) { |
172 |
|
stmt.setDouble(26, addr.longitude); |
173 |
|
} else { |
174 |
|
stmt.setNull(26, java.sql.Types.DOUBLE); |
175 |
|
} |
176 |
|
|
177 |
|
|
178 |
stmt.execute(); |
stmt.execute(); |
187 |
//con.commit(); |
//con.commit(); |
188 |
} catch (SQLException e) { |
} catch (SQLException e) { |
189 |
logger.warning("Error inserting addresses", e ); |
logger.warning("Error inserting addresses", e ); |
190 |
|
throw e; |
191 |
} |
} |
192 |
} |
} |
193 |
|
|
194 |
public void updateAddresses(List<Address> addresses) { |
public void updateAddresses(List<Address> addresses) throws Exception{ |
195 |
logger.info("DB::UpdateAddresses size:" + addresses.size() ); |
logger.info("DB::UpdateAddresses size:" + addresses.size() ); |
196 |
|
|
197 |
String sql = "UPDATE fulddaekning.adressetabel " + |
|
198 |
"SET " + |
final String SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS = queries.getProperty("SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS"); |
199 |
"RuteMa=?, KorelisteMa=?, " + |
|
|
"RuteTi=?, KorelisteTi=?, " + |
|
|
"RuteOn=?, KorelisteOn=?, " + |
|
|
"RuteTo=?, KorelisteTo=?, " + |
|
|
"RuteFr=?, KorelisteFr=?, " + |
|
|
"RuteLo=?, KorelisteLo=?, " + |
|
|
"RuteSo=?, KorelisteSo=?, " + |
|
|
"Distributor=?, " + |
|
|
"DBKBane=?, " + |
|
|
"Aendret=now() " + |
|
|
"WHERE id=? "; |
|
200 |
|
|
201 |
|
|
202 |
|
|
203 |
try (Connection con = DBConnection.getConnection()) { |
try (Connection con = DBConnection.getConnection()) { |
204 |
//con.setAutoCommit(false); |
//con.setAutoCommit(false); |
205 |
|
|
206 |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
try (PreparedStatement stmt = con.prepareStatement(SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS)) { |
207 |
|
int count = 0; |
208 |
|
|
209 |
for (Address addr : addresses) { |
for (Address addr : addresses) { |
210 |
stmt.setString(1, addr.ruteMandag); |
stmt.setInt(1, addr.id); |
211 |
stmt.setString(2, addr.korelisteMandag); |
stmt.setString(2, addr.ruteMandag); |
212 |
stmt.setString(3, addr.ruteTirsdag); |
stmt.setString(3, addr.korelisteMandag); |
213 |
stmt.setString(4, addr.korelisteTirsdag); |
stmt.setString(4, addr.ruteTirsdag); |
214 |
stmt.setString(5, addr.ruteOnsdag); |
stmt.setString(5, addr.korelisteTirsdag); |
215 |
stmt.setString(6, addr.korelisteOnsdag); |
stmt.setString(6, addr.ruteOnsdag); |
216 |
stmt.setString(7, addr.ruteTorsdag); |
stmt.setString(7, addr.korelisteOnsdag); |
217 |
stmt.setString(8, addr.korelisteTorsdag); |
stmt.setString(8, addr.ruteTorsdag); |
218 |
stmt.setString(9, addr.ruteFredag); |
stmt.setString(9, addr.korelisteTorsdag); |
219 |
stmt.setString(10, addr.korelisteFredag); |
stmt.setString(10, addr.ruteFredag); |
220 |
stmt.setString(11, addr.ruteLordag); |
stmt.setString(11, addr.korelisteFredag); |
221 |
stmt.setString(12, addr.korelisteLordag); |
stmt.setString(12, addr.ruteLordag); |
222 |
stmt.setString(13, addr.ruteSondag); |
stmt.setString(13, addr.korelisteLordag); |
223 |
stmt.setString(14, addr.korelisteSondag); |
stmt.setString(14, addr.ruteSondag); |
224 |
|
stmt.setString(15, addr.korelisteSondag); |
225 |
stmt.setString(15, addr.distributor); |
|
226 |
stmt.setInt(16, addr.dbkBane); |
stmt.setString(16, addr.distributor); |
227 |
stmt.setInt(17, addr.id); |
stmt.setInt(17, addr.dbkBane); |
228 |
|
|
229 |
|
|
|
stmt.execute(); |
|
|
if (stmt.getUpdateCount() != 1) { |
|
|
logger.warning("Error executing update: " + addr); |
|
|
} |
|
230 |
|
|
231 |
|
stmt.addBatch(); |
232 |
|
count++; |
233 |
|
if ( (count%200) == 0) { |
234 |
|
stmt.executeBatch(); |
235 |
|
|
236 |
|
if ( (count%50000) == 0 ) { |
237 |
|
logger.info("Update count " + count); |
238 |
|
} |
239 |
|
} |
240 |
} |
} |
241 |
|
|
242 |
|
stmt.executeBatch(); //update remainding queries |
243 |
|
|
244 |
} |
} |
245 |
|
|
246 |
//con.commit(); |
//con.commit(); |
247 |
} catch (SQLException e) { |
} catch (SQLException e) { |
248 |
logger.warning("Error updating addresses", e ); |
logger.warning("Error updating addresses", e ); |
249 |
|
throw e; |
250 |
} |
} |
251 |
} |
} |
252 |
|
|
|
/* |
|
|
private static String nullify(String str) { |
|
|
if (str == null) |
|
|
return null; |
|
|
|
|
|
if (str.equals("")) { |
|
|
return null; |
|
|
} else { |
|
|
return str; |
|
|
} |
|
|
} |
|
|
|
|
|
|
|
|
/* |
|
|
private static int safeInt(String str) { |
|
|
try { |
|
|
return Integer.parseInt( str ); |
|
|
} catch (NumberFormatException e) { |
|
|
return 0; |
|
|
} |
|
|
} |
|
|
|
|
|
|
|
|
|
|
|
private static String coalesce(String s1, String s2) { |
|
|
if (s1 != null) |
|
|
return s1; |
|
|
|
|
|
return s2; |
|
|
} |
|
|
|
|
|
*/ |
|
253 |
} |
} |