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; |
24 |
|
|
25 |
//static boolean DEBUG = false; |
//static boolean DEBUG = false; |
26 |
|
|
27 |
private TaskLogger logger = TaskLogger.getInstance(); |
private static TaskLogger logger = TaskLogger.getInstance(); |
28 |
|
|
29 |
|
static Properties queries; |
30 |
|
|
31 |
|
static { |
32 |
|
try { |
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.properties"); |
36 |
|
|
37 |
|
queries = new Properties(); |
38 |
|
queries.loadFromXML(is); |
39 |
|
|
40 |
|
|
41 |
|
} catch (Exception e) { |
42 |
|
logger.warning("error", e); |
43 |
|
throw new RuntimeException(e); //Escalate it to a runtime exception |
44 |
|
} |
45 |
|
|
46 |
|
} |
47 |
|
|
48 |
|
|
49 |
public List<Address> getAllAdresses() throws SQLException { |
public List<Address> getAllAdresses() throws SQLException { |
125 |
|
|
126 |
logger.info("DB::saveNewAddresses size:" + addresses.size() ); |
logger.info("DB::saveNewAddresses size:" + addresses.size() ); |
127 |
|
|
128 |
String sql = "INSERT IGNORE INTO fulddaekning.adressetabel " + |
final String SQL_INSERT_NEW_ADDRESS = queries.getProperty("SQL_INSERT_NEW_ADDRESS"); |
129 |
"(vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,dbkbane,indlast,aendret,david,googlechecked," + |
|
130 |
"RuteMa,KorelisteMa,RuteTi,KorelisteTi,RuteOn,KorelisteOn,RuteTo,KorelisteTo,RuteFr,KorelisteFr,RuteLo,KorelisteLo,RuteSo,KorelisteSo, " + |
|
|
"kommentar,latitude,longitude) " + |
|
|
"VALUES " + |
|
|
"(?,?,?,?,?,?,?,?, now(), now(), ?, 0, " + |
|
|
"?,?,?,?,?,?,?,?,?,?,?,?,?,?," + |
|
|
"?,?,?) "; |
|
131 |
|
|
132 |
|
|
133 |
try (Connection con = DBConnection.getConnection()) { |
try (Connection con = DBConnection.getConnection()) { |
134 |
|
|
135 |
|
|
136 |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
try (PreparedStatement stmt = con.prepareStatement(SQL_INSERT_NEW_ADDRESS)) { |
137 |
for (Address addr : addresses) { |
for (Address addr : addresses) { |
138 |
|
|
139 |
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); |
196 |
public void updateAddresses(List<Address> addresses) throws Exception{ |
public void updateAddresses(List<Address> addresses) throws Exception{ |
197 |
logger.info("DB::UpdateAddresses size:" + addresses.size() ); |
logger.info("DB::UpdateAddresses size:" + addresses.size() ); |
198 |
|
|
199 |
/*String sql = "UPDATE fulddaekning.adressetabel " + |
|
200 |
"SET " + |
final String SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS = queries.getProperty("SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS"); |
|
"RuteMa=?, KorelisteMa=?, " + |
|
|
"RuteTi=?, KorelisteTi=?, " + |
|
|
"RuteOn=?, KorelisteOn=?, " + |
|
|
"RuteTo=?, KorelisteTo=?, " + |
|
|
"RuteFr=?, KorelisteFr=?, " + |
|
|
"RuteLo=?, KorelisteLo=?, " + |
|
|
"RuteSo=?, KorelisteSo=?, " + |
|
|
"Distributor=?, " + |
|
|
"DBKBane=?, " + |
|
|
"Aendret=now() " + |
|
|
"WHERE id=? ";*/ |
|
|
String sql = "INSERT INTO fulddaekning.adressetabel " + |
|
|
"(id, " + |
|
|
"RuteMa, KorelisteMa, RuteTi, KorelisteTi, RuteOn, KorelisteOn, " + |
|
|
"RuteTo, KorelisteTo, RuteFr, KorelisteFr, RuteLo, KorelisteLo, " + |
|
|
"RuteSo, KorelisteSo, " + |
|
|
"Distributor, DBKBane, Aendret, " + |
|
|
"vejnavn,husnr,husnrbogstav,kommunekode,vejkode,gadeid,postnr) \n" + //Disse har ingen default value |
|
|
"VALUES " + |
|
|
"(?," + |
|
|
"?,?,?,?,?,?," + |
|
|
"?,?,?,?,?,?," + |
|
|
"?,?," + |
|
|
"?,?, now(), " + |
|
|
"'',0,'',0,0,0,0 ) \n" + |
|
|
|
|
|
"ON DUPLICATE KEY UPDATE " + |
|
|
"RuteMa=VALUES(RuteMa), KorelisteMa=VALUES(KorelisteMa), " + |
|
|
"RuteTi=VALUES(RuteTi), KorelisteTi=VALUES(KorelisteTi), " + |
|
|
"RuteOn=VALUES(RuteOn), KorelisteOn=VALUES(KorelisteOn), " + |
|
|
"RuteTo=VALUES(RuteTo), KorelisteTo=VALUES(KorelisteTo), " + |
|
|
"RuteFr=VALUES(RuteFr), KorelisteFr=VALUES(KorelisteFr), " + |
|
|
"RuteLo=VALUES(RuteLo), KorelisteLo=VALUES(KorelisteLo), " + |
|
|
"RuteSo=VALUES(RuteSo), KorelisteSo=VALUES(KorelisteSo), " + |
|
|
"Distributor=VALUES(Distributor), " + |
|
|
"DBKBane=VALUES(DBKBane) "; |
|
201 |
|
|
202 |
|
|
203 |
|
|
205 |
try (Connection con = DBConnection.getConnection()) { |
try (Connection con = DBConnection.getConnection()) { |
206 |
//con.setAutoCommit(false); |
//con.setAutoCommit(false); |
207 |
|
|
208 |
try (PreparedStatement stmt = con.prepareStatement(sql)) { |
try (PreparedStatement stmt = con.prepareStatement(SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS)) { |
209 |
int count = 0; |
int count = 0; |
210 |
|
|
211 |
for (Address addr : addresses) { |
for (Address addr : addresses) { |
252 |
} |
} |
253 |
} |
} |
254 |
|
|
|
/* |
|
|
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; |
|
|
} |
|
|
|
|
|
*/ |
|
255 |
} |
} |