8 |
import java.util.ArrayList; |
import java.util.ArrayList; |
9 |
import java.util.List; |
import java.util.List; |
10 |
|
|
11 |
|
|
12 |
import dk.daoas.adressevedligehold.beans.Address; |
import dk.daoas.adressevedligehold.beans.Address; |
13 |
|
import dk.daoas.adressevedligehold.beans.Address.AddressState; |
14 |
import dk.daoas.adressevedligehold.util.DeduplicateHelper; |
import dk.daoas.adressevedligehold.util.DeduplicateHelper; |
15 |
import dk.daoas.adressevedligehold.util.TimingHelper; |
import dk.daoas.adressevedligehold.util.TimingHelper; |
16 |
|
|
22 |
|
|
23 |
public class DatabaseLayerImplementation { |
public class DatabaseLayerImplementation { |
24 |
|
|
25 |
static boolean DEBUG = false; |
//static boolean DEBUG = false; |
26 |
|
|
27 |
|
|
28 |
public List<Address> getAllAdresses() throws SQLException { |
public List<Address> getAllAdresses() throws SQLException { |
29 |
TimingHelper timing = new TimingHelper(); |
TimingHelper timing = new TimingHelper(); |
30 |
|
|
31 |
String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr = 8700 " : ""; |
//String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr >= 6000 " : ""; |
32 |
|
|
33 |
String sql = |
String sql = |
34 |
"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, " |
35 |
+ "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 " |
36 |
+ "FROM fulddaekning.adressetabel " |
+ "FROM fulddaekning.adressetabel " |
37 |
+ debugFilter |
//+ debugFilter |
38 |
; |
; |
39 |
|
|
40 |
try ( Connection conn = DBConnection.getConnection(); |
try ( Connection conn = DBConnection.getConnection(); |
100 |
} |
} |
101 |
} |
} |
102 |
|
|
103 |
private void updateAddressesBatch(List<Address> addresses) throws SQLException { |
public void saveNewAddresses(List<Address> addresses) { |
104 |
|
System.out.println("DB::saveNewAddresses size:" + addresses.size() ); |
105 |
|
|
106 |
|
String sql = "INSERT IGNORE INTO fulddaekning.adressetabel " + |
107 |
|
"(vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,dbkbane,indlast,aendret,david,googlechecked," + |
108 |
|
"RuteMa,KorelisteMa,RuteTi,KorelisteTi,RuteOn,KorelisteOn,RuteTo,KorelisteTo,RuteFr,KorelisteFr,RuteLo,KorelisteLo,RuteSo,KorelisteSo) " + |
109 |
|
"VALUES " + |
110 |
|
"(?,?,?,?,?,?,?,?, now(), now(), ?, 0, " + |
111 |
|
"?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; |
112 |
|
|
113 |
|
|
114 |
|
try (Connection con = DBConnection.getConnection()) { |
115 |
|
//con.setAutoCommit(false); |
116 |
|
|
117 |
|
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
118 |
|
for (Address addr : addresses) { |
119 |
|
|
120 |
|
String david = String.format("%04d%03d%04d", addr.postnr, addr.kommunekode, addr.vejkode); |
121 |
|
|
122 |
|
stmt.setString(1, addr.vejnavn); |
123 |
|
stmt.setShort(2, addr.husnr); |
124 |
|
stmt.setString(3, addr.husnrbogstav); |
125 |
|
stmt.setShort(4, addr.kommunekode); |
126 |
|
stmt.setShort(5, addr.vejkode); |
127 |
|
stmt.setShort(6, addr.postnr); |
128 |
|
stmt.setInt(7, addr.gadeid); |
129 |
|
stmt.setShort(8, addr.dbkBane); |
130 |
|
stmt.setString(9, david); |
131 |
|
|
132 |
|
stmt.setString(10, addr.ruteMandag); |
133 |
|
stmt.setString(11, addr.korelisteMandag); |
134 |
|
stmt.setString(12, addr.ruteTirsdag); |
135 |
|
stmt.setString(13, addr.korelisteTirsdag); |
136 |
|
stmt.setString(14, addr.ruteOnsdag); |
137 |
|
stmt.setString(15, addr.korelisteOnsdag); |
138 |
|
stmt.setString(16, addr.ruteTorsdag); |
139 |
|
stmt.setString(17, addr.korelisteTorsdag); |
140 |
|
stmt.setString(18, addr.ruteFredag); |
141 |
|
stmt.setString(19, addr.korelisteFredag); |
142 |
|
stmt.setString(20, addr.ruteLordag); |
143 |
|
stmt.setString(21, addr.korelisteLordag); |
144 |
|
stmt.setString(22, addr.ruteSondag); |
145 |
|
stmt.setString(23, addr.korelisteSondag); |
146 |
|
|
147 |
|
|
148 |
|
stmt.execute(); |
149 |
|
if (stmt.getUpdateCount() != 1) { |
150 |
|
System.out.println("Error executing insert: " + addr); |
151 |
|
addr.state = AddressState.CREATE_FAIL; |
152 |
|
} |
153 |
|
|
154 |
|
} |
155 |
|
} |
156 |
|
|
157 |
|
//con.commit(); |
158 |
|
} catch (SQLException e) { |
159 |
|
System.out.println("Error inserting addresses" + e.getMessage() ); |
160 |
|
} |
161 |
} |
} |
162 |
|
|
163 |
public void updateAddresses(List<Address> addresses) { |
public void updateAddresses(List<Address> addresses) { |
164 |
|
System.out.println("DB::UpdateAddresses size:" + addresses.size() ); |
165 |
|
|
166 |
String sql = "UPDATE fulddaekning.adressetabel " + |
String sql = "UPDATE fulddaekning.adressetabel " + |
167 |
"SET " + |
"SET " + |
171 |
"RuteTo=?, KorelisteTo=?, " + |
"RuteTo=?, KorelisteTo=?, " + |
172 |
"RuteFr=?, KorelisteFr=?, " + |
"RuteFr=?, KorelisteFr=?, " + |
173 |
"RuteLo=?, KorelisteLo=?, " + |
"RuteLo=?, KorelisteLo=?, " + |
174 |
"RuteSo=?, KorelisteSo=? " + |
"RuteSo=?, KorelisteSo=?, " + |
175 |
"WHERE id=?"; |
"Distributor=?, " + |
176 |
|
"DBKBane=?, " + |
177 |
|
"Aendret=now() " + |
178 |
|
"WHERE id=? "; |
179 |
|
|
180 |
|
|
181 |
|
|
182 |
try (Connection con = DBConnection.getConnection()) { |
try (Connection con = DBConnection.getConnection()) { |
183 |
con.setAutoCommit(false); |
//con.setAutoCommit(false); |
184 |
|
|
185 |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
186 |
for (Address addr : addresses) { |
for (Address addr : addresses) { |
199 |
stmt.setString(13, addr.ruteSondag); |
stmt.setString(13, addr.ruteSondag); |
200 |
stmt.setString(14, addr.korelisteSondag); |
stmt.setString(14, addr.korelisteSondag); |
201 |
|
|
202 |
stmt.setInt(15, addr.id); |
stmt.setString(15, addr.distributor); |
203 |
|
stmt.setInt(16, addr.dbkBane); |
204 |
|
stmt.setInt(17, addr.id); |
205 |
|
|
206 |
|
|
207 |
stmt.execute(); |
stmt.execute(); |
212 |
} |
} |
213 |
} |
} |
214 |
|
|
215 |
con.commit(); |
//con.commit(); |
216 |
} catch (SQLException e) { |
} catch (SQLException e) { |
217 |
System.out.println("Error updating addresses" + e.getMessage() ); |
System.out.println("Error updating addresses" + e.getMessage() ); |
218 |
} |
} |
219 |
} |
} |
220 |
|
|
221 |
|
/* |
222 |
private static String nullify(String str) { |
private static String nullify(String str) { |
223 |
if (str == null) |
if (str == null) |
224 |
return null; |
return null; |