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 |
|
|
23 |
static boolean DEBUG = false; |
//static boolean DEBUG = false; |
24 |
|
|
25 |
|
|
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, " |
33 |
+ "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 " |
34 |
+ "FROM fulddaekning.adressetabel " |
+ "FROM fulddaekning.adressetabel " |
35 |
+ debugFilter |
//+ debugFilter |
36 |
; |
; |
37 |
|
|
38 |
try ( Connection conn = DBConnection.getConnection(); |
try ( Connection conn = DBConnection.getConnection(); |
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 |
"WHERE id=?"; |
"Distributor=?, " + |
118 |
|
"DBKBane=?, " + |
119 |
|
"Aendret=now() " + |
120 |
|
"WHERE id=? "; |
121 |
|
|
122 |
|
|
123 |
|
|
124 |
try (Connection con = DBConnection.getConnection()) { |
try (Connection con = DBConnection.getConnection()) { |
125 |
con.setAutoCommit(false); |
//con.setAutoCommit(false); |
126 |
|
|
127 |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
128 |
for (Address addr : addresses) { |
for (Address addr : addresses) { |
141 |
stmt.setString(13, addr.ruteSondag); |
stmt.setString(13, addr.ruteSondag); |
142 |
stmt.setString(14, addr.korelisteSondag); |
stmt.setString(14, addr.korelisteSondag); |
143 |
|
|
144 |
stmt.setInt(15, addr.id); |
stmt.setString(15, addr.distributor); |
145 |
|
stmt.setInt(16, addr.dbkBane); |
146 |
|
stmt.setInt(17, addr.id); |
147 |
|
|
148 |
|
|
149 |
stmt.execute(); |
stmt.execute(); |
154 |
} |
} |
155 |
} |
} |
156 |
|
|
157 |
con.commit(); |
//con.commit(); |
158 |
} catch (SQLException e) { |
} catch (SQLException e) { |
159 |
System.out.println("Error updating addresses" + e.getMessage() ); |
System.out.println("Error updating addresses" + e.getMessage() ); |
160 |
} |
} |