--- dao/DaoAdresseVedligehold/src/main/java/dk/daoas/adressevedligehold/db/DatabaseCoverageUpdate.java	2016/04/26 19:34:14	3024
+++ dao/DaoAdresseVedligehold/src/main/java/dk/daoas/adressevedligehold/db/DatabaseCoverageUpdate.java	2016/04/27 18:19:53	3025
@@ -1,5 +1,6 @@
 package dk.daoas.adressevedligehold.db;
 
+import java.io.InputStream;
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
@@ -7,8 +8,7 @@
 import java.sql.Statement;
 import java.util.ArrayList;
 import java.util.List;
-
-
+import java.util.Properties;
 
 import dk.daoas.adressevedligehold.beans.Address;
 import dk.daoas.adressevedligehold.beans.Address.AddressState;
@@ -24,7 +24,26 @@
 	
 	//static boolean DEBUG = false;
 	
-	private TaskLogger logger = TaskLogger.getInstance();
+	private static TaskLogger logger = TaskLogger.getInstance();
+	
+	static Properties queries; 
+	
+	static {
+		try {
+			//On some platforms it may be necessary to load as "META-INF/queries.properties"
+			ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
+			InputStream is = classLoader.getResourceAsStream("sql.properties");
+			
+			queries = new Properties();
+			queries.loadFromXML(is);
+			
+			
+		} catch (Exception e) {
+			logger.warning("error", e);
+			throw new RuntimeException(e); //Escalate it to a runtime exception
+		}
+
+	}
 	
 	
 	public List<Address> getAllAdresses() throws SQLException {
@@ -106,20 +125,15 @@
 		
 		logger.info("DB::saveNewAddresses size:" + addresses.size() );
 		
-		String sql = "INSERT IGNORE INTO fulddaekning.adressetabel " +
-					 "(vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,dbkbane,indlast,aendret,david,googlechecked," +
-					 "RuteMa,KorelisteMa,RuteTi,KorelisteTi,RuteOn,KorelisteOn,RuteTo,KorelisteTo,RuteFr,KorelisteFr,RuteLo,KorelisteLo,RuteSo,KorelisteSo, " +
-					 "kommentar,latitude,longitude) " +
-					 "VALUES " +
-					 "(?,?,?,?,?,?,?,?, now(), now(), ?, 0, " +
-					 "?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
-					 "?,?,?) ";
+		final String SQL_INSERT_NEW_ADDRESS = queries.getProperty("SQL_INSERT_NEW_ADDRESS");
+		
+		
 		
 		
 		try (Connection con = DBConnection.getConnection()) {
 
 			
-			try (PreparedStatement stmt = con.prepareStatement(sql)) {
+			try (PreparedStatement stmt = con.prepareStatement(SQL_INSERT_NEW_ADDRESS)) {
 				for (Address addr : addresses) {
 					
 					String david = String.format("%04d%03d%04d", addr.postnr, addr.kommunekode, addr.vejkode);
@@ -182,44 +196,8 @@
 	public void updateAddresses(List<Address> addresses) throws Exception{
 		logger.info("DB::UpdateAddresses size:" + addresses.size() );
 		
-		/*String sql = "UPDATE fulddaekning.adressetabel " +
-					"SET " +
-					"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)  ";
+
+		final String SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS = queries.getProperty("SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS");
 					
 					
 					
@@ -227,7 +205,7 @@
 		try (Connection con = DBConnection.getConnection()) {
 			//con.setAutoCommit(false);
 			
-			try (PreparedStatement stmt = con.prepareStatement(sql)) {
+			try (PreparedStatement stmt = con.prepareStatement(SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS)) {
 				int count = 0;
 				
 				for (Address addr : addresses) {
@@ -274,36 +252,4 @@
 		}
 	}
 	
-	/*
-	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;
-	}
-
-	*/
 }