--- dao/DaoAdresseVedligehold/src/main/java/dk/daoas/adressevedligehold/db/DatabaseLayerImplementation.java	2016/02/03 19:02:38	2905
+++ dao/DaoAdresseVedligehold/src/main/java/dk/daoas/adressevedligehold/db/DatabaseCoverageUpdate.java	2016/05/02 17:50:14	3035
@@ -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;
@@ -16,18 +16,33 @@
 import dk.daoas.adressevedligehold.util.DeduplicateHelper;
 import dk.daoas.adressevedligehold.util.TimingHelper;
 
-/*
- * TODO: Batched skrivning af updates
- * TODO: Skrivning af nye adresser 
+/* 
  *  
  */
 
-public class DatabaseLayerImplementation  {
+public class DatabaseCoverageUpdate  {
 	
 	//static boolean DEBUG = false;
 	
 	private TaskLogger logger = TaskLogger.getInstance();
 	
+	Properties queries; 
+	
+	public DatabaseCoverageUpdate() throws SQLException {
+
+		//On some platforms it may be necessary to load as "META-INF/queries.properties"
+		ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
+		InputStream is = classLoader.getResourceAsStream("sql.xml");
+			
+		queries = new Properties();
+		try {
+			queries.loadFromXML(is);
+		} catch (Exception e) {
+			throw new SQLException("Error loading queries", e);
+		}
+			
+	}
+	
 	
 	public List<Address> getAllAdresses() throws SQLException {
 		TimingHelper timing = new TimingHelper();
@@ -42,7 +57,7 @@
 				;
 		
 		try ( Connection conn = DBConnection.getConnection();		
-				Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
+				Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
 				) {
 			stmt.setFetchSize(Integer.MIN_VALUE);
 			ResultSet res = stmt.executeQuery(sql);
@@ -104,21 +119,19 @@
 		}
 	}
 	
-	public void saveNewAddresses(List<Address> addresses) {
+	public void saveNewAddresses(List<Address> addresses) throws Exception {
+		
 		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) " +
-					 "VALUES " +
-					 "(?,?,?,?,?,?,?,?, now(), now(), ?, 0, " +
-					 "?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+		final String SQL_INSERT_NEW_ADDRESS = queries.getProperty("SQL_INSERT_NEW_ADDRESS");
+		
+		
 		
 		
 		try (Connection con = DBConnection.getConnection()) {
-			//con.setAutoCommit(false);
+
 			
-			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);
@@ -147,6 +160,19 @@
 					stmt.setString(21, addr.korelisteLordag);
 					stmt.setString(22, addr.ruteSondag);
 					stmt.setString(23, addr.korelisteSondag);
+					
+					stmt.setString(24, addr.kommentar);
+					
+					if (addr.latitude != 0.0) {
+						stmt.setDouble(25, addr.latitude);
+					} else {
+						stmt.setNull(25, java.sql.Types.DOUBLE);
+					}					
+					if (addr.longitude != 0.0) {
+						stmt.setDouble(26, addr.longitude);
+					} else {
+						stmt.setNull(26, java.sql.Types.DOUBLE);
+					}
 
 					
 					stmt.execute();
@@ -161,97 +187,67 @@
 			//con.commit();
 		} catch (SQLException e) {
 			logger.warning("Error inserting addresses", e  );
+			throw e;
 		}
 	}
 	
-	public void updateAddresses(List<Address> addresses) {
+	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=? ";
+
+		final String SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS = queries.getProperty("SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS");
+					
 					
 					
 		
 		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) {
-					stmt.setString(1, addr.ruteMandag);
-					stmt.setString(2, addr.korelisteMandag);
-					stmt.setString(3, addr.ruteTirsdag);
-					stmt.setString(4, addr.korelisteTirsdag);
-					stmt.setString(5, addr.ruteOnsdag);
-					stmt.setString(6, addr.korelisteOnsdag);
-					stmt.setString(7, addr.ruteTorsdag);
-					stmt.setString(8, addr.korelisteTorsdag);
-					stmt.setString(9, addr.ruteFredag);
-					stmt.setString(10, addr.korelisteFredag);
-					stmt.setString(11, addr.ruteLordag);
-					stmt.setString(12, addr.korelisteLordag);
-					stmt.setString(13, addr.ruteSondag);
-					stmt.setString(14, addr.korelisteSondag);
-
-					stmt.setString(15, addr.distributor);
-					stmt.setInt(16, addr.dbkBane);
-					stmt.setInt(17, addr.id);
-
+					stmt.setInt(1, addr.id);
+					stmt.setString(2, addr.ruteMandag);
+					stmt.setString(3, addr.korelisteMandag);
+					stmt.setString(4, addr.ruteTirsdag);
+					stmt.setString(5, addr.korelisteTirsdag);
+					stmt.setString(6, addr.ruteOnsdag);
+					stmt.setString(7, addr.korelisteOnsdag);
+					stmt.setString(8, addr.ruteTorsdag);
+					stmt.setString(9, addr.korelisteTorsdag);
+					stmt.setString(10, addr.ruteFredag);
+					stmt.setString(11, addr.korelisteFredag);
+					stmt.setString(12, addr.ruteLordag);
+					stmt.setString(13, addr.korelisteLordag);
+					stmt.setString(14, addr.ruteSondag);
+					stmt.setString(15, addr.korelisteSondag);
+
+					stmt.setString(16, addr.distributor);
+					stmt.setInt(17, addr.dbkBane);
+					
 					
-					stmt.execute();
-					if (stmt.getUpdateCount() != 1) {
-						logger.warning("Error executing update: " + addr);
-					}
 					
+					stmt.addBatch();
+					count++;
+					if ( (count%200) == 0) {
+						stmt.executeBatch();
+						
+						if ( (count%50000) == 0 ) {
+							logger.info("Update count " + count);
+						}
+					}					
 				}
+				
+				stmt.executeBatch(); //update remainding queries
+				
 			}
 			
 			//con.commit();
 		} catch (SQLException e) {
 			logger.warning("Error updating addresses", e );
+			throw e;
 		}
 	}
 	
-	/*
-	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;
-	}
-
-	*/
 }