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; |
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; |
//static boolean DEBUG = false; |
26 |
|
|
27 |
|
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(); |
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 |
public void saveNewAddresses(List<Address> addresses) { |
public void saveNewAddresses(List<Address> addresses) { |
106 |
System.out.println("DB::saveNewAddresses size:" + addresses.size() ); |
logger.info("DB::saveNewAddresses size:" + addresses.size() ); |
107 |
|
|
108 |
String sql = "INSERT IGNORE INTO fulddaekning.adressetabel " + |
String sql = "INSERT IGNORE INTO fulddaekning.adressetabel " + |
109 |
"(vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,dbkbane,indlast,aendret,david,googlechecked," + |
"(vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,dbkbane,indlast,aendret,david,googlechecked," + |
110 |
"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, " + |
111 |
|
"kommentar,latitude,longitude) " + |
112 |
"VALUES " + |
"VALUES " + |
113 |
"(?,?,?,?,?,?,?,?, now(), now(), ?, 0, " + |
"(?,?,?,?,?,?,?,?, now(), now(), ?, 0, " + |
114 |
"?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; |
"?,?,?,?,?,?,?,?,?,?,?,?,?,?," + |
115 |
|
"?,?,?) "; |
116 |
|
|
117 |
|
|
118 |
try (Connection con = DBConnection.getConnection()) { |
try (Connection con = DBConnection.getConnection()) { |
119 |
//con.setAutoCommit(false); |
|
120 |
|
|
121 |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
122 |
for (Address addr : addresses) { |
for (Address addr : addresses) { |
147 |
stmt.setString(21, addr.korelisteLordag); |
stmt.setString(21, addr.korelisteLordag); |
148 |
stmt.setString(22, addr.ruteSondag); |
stmt.setString(22, addr.ruteSondag); |
149 |
stmt.setString(23, addr.korelisteSondag); |
stmt.setString(23, addr.korelisteSondag); |
150 |
|
|
151 |
|
stmt.setString(24, addr.kommentar); |
152 |
|
|
153 |
|
if (addr.latitude != 0.0) { |
154 |
|
stmt.setDouble(25, addr.latitude); |
155 |
|
} else { |
156 |
|
stmt.setNull(25, java.sql.Types.DOUBLE); |
157 |
|
} |
158 |
|
if (addr.longitude != 0.0) { |
159 |
|
stmt.setDouble(26, addr.longitude); |
160 |
|
} else { |
161 |
|
stmt.setNull(26, java.sql.Types.DOUBLE); |
162 |
|
} |
163 |
|
|
164 |
|
|
165 |
stmt.execute(); |
stmt.execute(); |
166 |
if (stmt.getUpdateCount() != 1) { |
if (stmt.getUpdateCount() != 1) { |
167 |
System.out.println("Error executing insert: " + addr); |
logger.info("Error executing insert: " + addr); |
168 |
addr.state = AddressState.CREATE_FAIL; |
addr.state = AddressState.CREATE_FAIL; |
169 |
} |
} |
170 |
|
|
173 |
|
|
174 |
//con.commit(); |
//con.commit(); |
175 |
} catch (SQLException e) { |
} catch (SQLException e) { |
176 |
System.out.println("Error inserting addresses" + e.getMessage() ); |
logger.warning("Error inserting addresses", e ); |
177 |
} |
} |
178 |
} |
} |
179 |
|
|
180 |
public void updateAddresses(List<Address> addresses) { |
public void updateAddresses(List<Address> addresses) { |
181 |
System.out.println("DB::UpdateAddresses size:" + addresses.size() ); |
logger.info("DB::UpdateAddresses size:" + addresses.size() ); |
182 |
|
|
183 |
String sql = "UPDATE fulddaekning.adressetabel " + |
String sql = "UPDATE fulddaekning.adressetabel " + |
184 |
"SET " + |
"SET " + |
200 |
//con.setAutoCommit(false); |
//con.setAutoCommit(false); |
201 |
|
|
202 |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
203 |
|
int count = 0; |
204 |
|
|
205 |
for (Address addr : addresses) { |
for (Address addr : addresses) { |
206 |
stmt.setString(1, addr.ruteMandag); |
stmt.setString(1, addr.ruteMandag); |
207 |
stmt.setString(2, addr.korelisteMandag); |
stmt.setString(2, addr.korelisteMandag); |
221 |
stmt.setString(15, addr.distributor); |
stmt.setString(15, addr.distributor); |
222 |
stmt.setInt(16, addr.dbkBane); |
stmt.setInt(16, addr.dbkBane); |
223 |
stmt.setInt(17, addr.id); |
stmt.setInt(17, addr.id); |
|
|
|
224 |
|
|
|
stmt.execute(); |
|
|
if (stmt.getUpdateCount() != 1) { |
|
|
System.out.println("Error executing update"); |
|
|
} |
|
225 |
|
|
226 |
|
stmt.addBatch(); |
227 |
|
count++; |
228 |
|
if (count >= 100) { |
229 |
|
stmt.executeBatch(); |
230 |
|
count = 0; |
231 |
|
} |
232 |
} |
} |
233 |
|
|
234 |
|
stmt.executeBatch(); //update remainding queries |
235 |
|
|
236 |
} |
} |
237 |
|
|
238 |
//con.commit(); |
//con.commit(); |
239 |
} catch (SQLException e) { |
} catch (SQLException e) { |
240 |
System.out.println("Error updating addresses" + e.getMessage() ); |
logger.warning("Error updating addresses", e ); |
241 |
} |
} |
242 |
} |
} |
243 |
|
|