12 |
import dk.daoas.adressevedligehold.util.DeduplicateHelper; |
import dk.daoas.adressevedligehold.util.DeduplicateHelper; |
13 |
import dk.daoas.adressevedligehold.util.TimingHelper; |
import dk.daoas.adressevedligehold.util.TimingHelper; |
14 |
|
|
15 |
|
/* |
16 |
|
* TODO: Batched skrivning af updates |
17 |
|
* TODO: Skrivning af nye adresser |
18 |
|
* |
19 |
|
*/ |
20 |
|
|
21 |
public class DatabaseLayerImplementation { |
public class DatabaseLayerImplementation { |
22 |
|
|
26 |
public List<Address> getAllAdresses() throws SQLException { |
public List<Address> getAllAdresses() throws SQLException { |
27 |
TimingHelper timing = new TimingHelper(); |
TimingHelper timing = new TimingHelper(); |
28 |
|
|
29 |
String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr = 8700 " : ""; |
String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr >= 6000 " : ""; |
30 |
|
|
31 |
String sql = |
String sql = |
32 |
"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, " |
98 |
} |
} |
99 |
} |
} |
100 |
|
|
101 |
|
private void updateAddressesBatch(List<Address> addresses) throws SQLException { |
102 |
|
|
103 |
|
} |
104 |
|
|
105 |
public void updateAddresses(List<Address> addresses) { |
public void updateAddresses(List<Address> addresses) { |
106 |
|
System.out.println("DB::UpdateAddresses size:" + addresses.size() ); |
107 |
|
|
108 |
String sql = "UPDATE fulddaekning.adressetabel " + |
String sql = "UPDATE fulddaekning.adressetabel " + |
109 |
"SET " + |
"SET " + |
113 |
"RuteTo=?, KorelisteTo=?, " + |
"RuteTo=?, KorelisteTo=?, " + |
114 |
"RuteFr=?, KorelisteFr=?, " + |
"RuteFr=?, KorelisteFr=?, " + |
115 |
"RuteLo=?, KorelisteLo=?, " + |
"RuteLo=?, KorelisteLo=?, " + |
116 |
"RuteSo=?, KorelisteSo=? " + |
"RuteSo=?, KorelisteSo=?, " + |
117 |
|
"Distributor=? " + |
118 |
"WHERE id=?"; |
"WHERE id=?"; |
119 |
|
|
120 |
|
|
121 |
|
|
122 |
try (Connection con = DBConnection.getConnection()) { |
try (Connection con = DBConnection.getConnection()) { |
123 |
con.setAutoCommit(false); |
//con.setAutoCommit(false); |
124 |
|
|
125 |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
126 |
for (Address addr : addresses) { |
for (Address addr : addresses) { |
139 |
stmt.setString(13, addr.ruteSondag); |
stmt.setString(13, addr.ruteSondag); |
140 |
stmt.setString(14, addr.korelisteSondag); |
stmt.setString(14, addr.korelisteSondag); |
141 |
|
|
142 |
stmt.setInt(15, addr.id); |
stmt.setString(15, addr.distributor); |
143 |
|
stmt.setInt(16, addr.id); |
144 |
|
|
145 |
|
|
146 |
stmt.execute(); |
stmt.execute(); |
151 |
} |
} |
152 |
} |
} |
153 |
|
|
154 |
con.commit(); |
//con.commit(); |
155 |
} catch (SQLException e) { |
} catch (SQLException e) { |
156 |
System.out.println("Error updating addresses" + e.getMessage() ); |
System.out.println("Error updating addresses" + e.getMessage() ); |
157 |
} |
} |