adressevedligehold/db/DatabaseCoverageUpdate.java

package dk.daoas.adressevedligehold.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import dk.daoas.adressevedligehold.beans.Address;
import dk.daoas.adressevedligehold.beans.Address.AddressState;
import dk.daoas.adressevedligehold.tasks.TaskLogger;
import dk.daoas.adressevedligehold.util.DeduplicateHelper;
import dk.daoas.adressevedligehold.util.TimingHelper;

/* 
 *  
 */

public class DatabaseCoverageUpdate  {
        
        //static boolean DEBUG = false;
        
        private TaskLogger logger = TaskLogger.getInstance();
        
        
        public List<Address> getAllAdresses() throws SQLException {
                TimingHelper timing = new TimingHelper();
                
                //String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr >= 6000 " : "";
                
                String sql = 
                                "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,latitude,longitude, "
                                + "rutema,korelistema,ruteti,korelisteti,ruteon,korelisteon,ruteto,korelisteto,rutefr,korelistefr,rutelo,korelistelo,ruteso,korelisteso "
                                + "FROM fulddaekning.adressetabel "
                                //+ debugFilter
                                ;
                
                try ( Connection conn = DBConnection.getConnection();           
                                Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                                ) {
                        stmt.setFetchSize(Integer.MIN_VALUE);
                        ResultSet res = stmt.executeQuery(sql);
                        
                        List<Address> list = new ArrayList<Address>(2600000);//initial capacity 2.6 mio
                        
                        DeduplicateHelper<String> vejnavnCache = new DeduplicateHelper<String>(81920);
                        DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>();
                        DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
                        DeduplicateHelper<String> dirigeringsCache = new DeduplicateHelper<String>(16*1024);                    
        
                        
                        while (res.next()) {
                                
                                Address a = new Address();
                                a.id = res.getInt(1);
                                a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
                                a.husnr = (short) res.getInt(3);
                                a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
                                a.kommunekode = (short) res.getInt(5);
                                a.vejkode = (short)res.getInt(6);
                                a.postnr = (short)res.getInt(7);
                                a.gadeid = res.getInt(8);
                                a.distributor = distributorCache.getInstance(res.getString(9));
                                a.dbkBane = (short) res.getInt(10);
                                a.latitude = (float) res.getDouble(11);
                                a.longitude = (float) res.getDouble(12);
                                
                                a.ruteMandag = dirigeringsCache.getInstance( res.getString(13) );
                                a.korelisteMandag = dirigeringsCache.getInstance( res.getString(14) );
                                
                                a.ruteTirsdag = dirigeringsCache.getInstance( res.getString(15) );
                                a.korelisteTirsdag = dirigeringsCache.getInstance( res.getString(16) );                         

                                a.ruteOnsdag = dirigeringsCache.getInstance( res.getString(17) );
                                a.korelisteOnsdag = dirigeringsCache.getInstance( res.getString(18) );
                                
                                a.ruteTorsdag = dirigeringsCache.getInstance( res.getString(19) );
                                a.korelisteTorsdag = dirigeringsCache.getInstance( res.getString(20) );
                                
                                a.ruteFredag = dirigeringsCache.getInstance( res.getString(21) );
                                a.korelisteFredag = dirigeringsCache.getInstance( res.getString(22) );
                                
                                a.ruteLordag = dirigeringsCache.getInstance( res.getString(23) );
                                a.korelisteLordag = dirigeringsCache.getInstance( res.getString(24) );
                                
                                a.ruteSondag = dirigeringsCache.getInstance( res.getString(25) );
                                a.korelisteSondag = dirigeringsCache.getInstance( res.getString(26) );


                                list.add(a);
                        }
                        res.close();
                        
                        
                        logger.info("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms");
                        
                        return list;
                }
        }
        
        public void saveNewAddresses(List<Address> addresses) {
                
                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, " +
                                         "?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
                                         "?,?,?) ";
                
                
                try (Connection con = DBConnection.getConnection()) {

                        
                        try (PreparedStatement stmt = con.prepareStatement(sql)) {
                                for (Address addr : addresses) {
                                        
                                        String david = String.format("%04d%03d%04d", addr.postnr, addr.kommunekode, addr.vejkode);
                                        
                                        stmt.setString(1, addr.vejnavn);
                                        stmt.setShort(2, addr.husnr);
                                        stmt.setString(3, addr.husnrbogstav);
                                        stmt.setShort(4, addr.kommunekode);
                                        stmt.setShort(5, addr.vejkode);
                                        stmt.setShort(6, addr.postnr);
                                        stmt.setInt(7, addr.gadeid);
                                        stmt.setShort(8, addr.dbkBane);
                                        stmt.setString(9, david);
                                        
                                        stmt.setString(10, addr.ruteMandag);
                                        stmt.setString(11, addr.korelisteMandag);
                                        stmt.setString(12, addr.ruteTirsdag);
                                        stmt.setString(13, addr.korelisteTirsdag);
                                        stmt.setString(14, addr.ruteOnsdag);
                                        stmt.setString(15, addr.korelisteOnsdag);
                                        stmt.setString(16, addr.ruteTorsdag);
                                        stmt.setString(17, addr.korelisteTorsdag);
                                        stmt.setString(18, addr.ruteFredag);
                                        stmt.setString(19, addr.korelisteFredag);
                                        stmt.setString(20, addr.ruteLordag);
                                        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();
                                        if (stmt.getUpdateCount() != 1) {
                                                logger.info("Error executing insert: " + addr);
                                                addr.state = AddressState.CREATE_FAIL;
                                        }
                                        
                                }
                        }
                        
                        //con.commit();
                } catch (SQLException e) {
                        logger.warning("Error inserting addresses", e  );
                }
        }
        
        public void updateAddresses(List<Address> addresses) {
                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)  ";
                                        
                                        
                try (Connection con = DBConnection.getConnection()) {
                        //con.setAutoCommit(false);
                        
                        try (PreparedStatement stmt = con.prepareStatement(sql)) {
                                int count = 0;
                                
                                for (Address addr : addresses) {
                                        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.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 );
                }
        }
        
        /*
        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;
        }

        */
}
1	torben	2838	package dk.daoas.adressevedligehold.db;
2
3			import java.sql.Connection;
4			import java.sql.PreparedStatement;
5			import java.sql.ResultSet;
6			import java.sql.SQLException;
7			import java.sql.Statement;
8			import java.util.ArrayList;
9			import java.util.List;
10
11	torben	2876
12	torben	2903
13	torben	2838	import dk.daoas.adressevedligehold.beans.Address;
14	torben	2876	import dk.daoas.adressevedligehold.beans.Address.AddressState;
15	torben	2903	import dk.daoas.adressevedligehold.tasks.TaskLogger;
16	torben	2838	import dk.daoas.adressevedligehold.util.DeduplicateHelper;
17			import dk.daoas.adressevedligehold.util.TimingHelper;
18
19	torben	2950	/*
20	torben	2840	*
21			*/
22	torben	2838
23	torben	2946	public class DatabaseCoverageUpdate {
24	torben	2838
25	torben	2847	//static boolean DEBUG = false;
26	torben	2838
27	torben	2903	private TaskLogger logger = TaskLogger.getInstance();
28	torben	2838
29	torben	2903
30	torben	2838	public List<Address> getAllAdresses() throws SQLException {
31			TimingHelper timing = new TimingHelper();
32
33	torben	2847	//String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr >= 6000 " : "";
34	torben	2838
35			String sql =
36			"SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,latitude,longitude, "
37			+ "rutema,korelistema,ruteti,korelisteti,ruteon,korelisteon,ruteto,korelisteto,rutefr,korelistefr,rutelo,korelistelo,ruteso,korelisteso "
38			+ "FROM fulddaekning.adressetabel "
39	torben	2847	//+ debugFilter
40	torben	2838	;
41
42			try ( Connection conn = DBConnection.getConnection();
43	torben	2937	Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
44	torben	2838	) {
45			stmt.setFetchSize(Integer.MIN_VALUE);
46			ResultSet res = stmt.executeQuery(sql);
47
48			List<Address> list = new ArrayList<Address>(2600000);//initial capacity 2.6 mio
49
50			DeduplicateHelper<String> vejnavnCache = new DeduplicateHelper<String>(81920);
51			DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>();
52			DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
53			DeduplicateHelper<String> dirigeringsCache = new DeduplicateHelper<String>(16*1024);
54
55
56			while (res.next()) {
57
58			Address a = new Address();
59			a.id = res.getInt(1);
60			a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
61			a.husnr = (short) res.getInt(3);
62			a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
63			a.kommunekode = (short) res.getInt(5);
64			a.vejkode = (short)res.getInt(6);
65			a.postnr = (short)res.getInt(7);
66			a.gadeid = res.getInt(8);
67			a.distributor = distributorCache.getInstance(res.getString(9));
68			a.dbkBane = (short) res.getInt(10);
69			a.latitude = (float) res.getDouble(11);
70			a.longitude = (float) res.getDouble(12);
71
72			a.ruteMandag = dirigeringsCache.getInstance( res.getString(13) );
73			a.korelisteMandag = dirigeringsCache.getInstance( res.getString(14) );
74
75			a.ruteTirsdag = dirigeringsCache.getInstance( res.getString(15) );
76			a.korelisteTirsdag = dirigeringsCache.getInstance( res.getString(16) );
77
78			a.ruteOnsdag = dirigeringsCache.getInstance( res.getString(17) );
79			a.korelisteOnsdag = dirigeringsCache.getInstance( res.getString(18) );
80
81			a.ruteTorsdag = dirigeringsCache.getInstance( res.getString(19) );
82			a.korelisteTorsdag = dirigeringsCache.getInstance( res.getString(20) );
83
84			a.ruteFredag = dirigeringsCache.getInstance( res.getString(21) );
85			a.korelisteFredag = dirigeringsCache.getInstance( res.getString(22) );
86
87			a.ruteLordag = dirigeringsCache.getInstance( res.getString(23) );
88			a.korelisteLordag = dirigeringsCache.getInstance( res.getString(24) );
89
90			a.ruteSondag = dirigeringsCache.getInstance( res.getString(25) );
91			a.korelisteSondag = dirigeringsCache.getInstance( res.getString(26) );
92
93
94			list.add(a);
95			}
96			res.close();
97
98
99	torben	2903	logger.info("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms");
100	torben	2838
101			return list;
102			}
103			}
104
105	torben	2876	public void saveNewAddresses(List<Address> addresses) {
106	torben	3023
107	torben	2903	logger.info("DB::saveNewAddresses size:" + addresses.size() );
108	torben	2840
109	torben	2876	String sql = "INSERT IGNORE INTO fulddaekning.adressetabel " +
110			"(vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,dbkbane,indlast,aendret,david,googlechecked," +
111	torben	3003	"RuteMa,KorelisteMa,RuteTi,KorelisteTi,RuteOn,KorelisteOn,RuteTo,KorelisteTo,RuteFr,KorelisteFr,RuteLo,KorelisteLo,RuteSo,KorelisteSo, " +
112			"kommentar,latitude,longitude) " +
113	torben	2876	"VALUES " +
114			"(?,?,?,?,?,?,?,?, now(), now(), ?, 0, " +
115	torben	3003	"?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
116			"?,?,?) ";
117	torben	2876
118
119			try (Connection con = DBConnection.getConnection()) {
120	torben	2950
121	torben	2876
122			try (PreparedStatement stmt = con.prepareStatement(sql)) {
123			for (Address addr : addresses) {
124
125			String david = String.format("%04d%03d%04d", addr.postnr, addr.kommunekode, addr.vejkode);
126
127			stmt.setString(1, addr.vejnavn);
128			stmt.setShort(2, addr.husnr);
129			stmt.setString(3, addr.husnrbogstav);
130			stmt.setShort(4, addr.kommunekode);
131			stmt.setShort(5, addr.vejkode);
132			stmt.setShort(6, addr.postnr);
133			stmt.setInt(7, addr.gadeid);
134			stmt.setShort(8, addr.dbkBane);
135			stmt.setString(9, david);
136
137			stmt.setString(10, addr.ruteMandag);
138			stmt.setString(11, addr.korelisteMandag);
139			stmt.setString(12, addr.ruteTirsdag);
140			stmt.setString(13, addr.korelisteTirsdag);
141			stmt.setString(14, addr.ruteOnsdag);
142			stmt.setString(15, addr.korelisteOnsdag);
143			stmt.setString(16, addr.ruteTorsdag);
144			stmt.setString(17, addr.korelisteTorsdag);
145			stmt.setString(18, addr.ruteFredag);
146			stmt.setString(19, addr.korelisteFredag);
147			stmt.setString(20, addr.ruteLordag);
148			stmt.setString(21, addr.korelisteLordag);
149			stmt.setString(22, addr.ruteSondag);
150			stmt.setString(23, addr.korelisteSondag);
151	torben	3003
152			stmt.setString(24, addr.kommentar);
153
154			if (addr.latitude != 0.0) {
155			stmt.setDouble(25, addr.latitude);
156			} else {
157			stmt.setNull(25, java.sql.Types.DOUBLE);
158			}
159			if (addr.longitude != 0.0) {
160			stmt.setDouble(26, addr.longitude);
161			} else {
162			stmt.setNull(26, java.sql.Types.DOUBLE);
163			}
164	torben	2876
165
166			stmt.execute();
167			if (stmt.getUpdateCount() != 1) {
168	torben	2903	logger.info("Error executing insert: " + addr);
169	torben	2876	addr.state = AddressState.CREATE_FAIL;
170			}
171
172			}
173			}
174
175			//con.commit();
176			} catch (SQLException e) {
177	torben	2903	logger.warning("Error inserting addresses", e );
178	torben	2876	}
179	torben	2840	}
180
181	torben	2838	public void updateAddresses(List<Address> addresses) {
182	torben	2903	logger.info("DB::UpdateAddresses size:" + addresses.size() );
183	torben	2838
184	torben	3023	/*String sql = "UPDATE fulddaekning.adressetabel " +
185	torben	2838	"SET " +
186			"RuteMa=?, KorelisteMa=?, " +
187			"RuteTi=?, KorelisteTi=?, " +
188			"RuteOn=?, KorelisteOn=?, " +
189			"RuteTo=?, KorelisteTo=?, " +
190			"RuteFr=?, KorelisteFr=?, " +
191			"RuteLo=?, KorelisteLo=?, " +
192	torben	2845	"RuteSo=?, KorelisteSo=?, " +
193	torben	2847	"Distributor=?, " +
194	torben	2853	"DBKBane=?, " +
195			"Aendret=now() " +
196	torben	3023	"WHERE id=? ";*/
197			String sql = "INSERT INTO fulddaekning.adressetabel " +
198			"(id, " +
199			"RuteMa, KorelisteMa, RuteTi, KorelisteTi, RuteOn, KorelisteOn, " +
200			"RuteTo, KorelisteTo, RuteFr, KorelisteFr, RuteLo, KorelisteLo, " +
201			"RuteSo, KorelisteSo, " +
202			"Distributor, DBKBane, Aendret, " +
203			"vejnavn,husnr,husnrbogstav,kommunekode,vejkode,gadeid,postnr) \n" + //Disse har ingen default value
204			"VALUES " +
205			"(?," +
206			"?,?,?,?,?,?," +
207			"?,?,?,?,?,?," +
208			"?,?," +
209			"?,?, now(), " +
210			"'',0,'',0,0,0,0 ) \n" +
211	torben	2838
212	torben	3023	"ON DUPLICATE KEY UPDATE " +
213			"RuteMa=VALUES(RuteMa), KorelisteMa=VALUES(KorelisteMa), " +
214			"RuteTi=VALUES(RuteTi), KorelisteTi=VALUES(KorelisteTi), " +
215			"RuteOn=VALUES(RuteOn), KorelisteOn=VALUES(KorelisteOn), " +
216			"RuteTo=VALUES(RuteTo), KorelisteTo=VALUES(KorelisteTo), " +
217			"RuteFr=VALUES(RuteFr), KorelisteFr=VALUES(KorelisteFr), " +
218			"RuteLo=VALUES(RuteLo), KorelisteLo=VALUES(KorelisteLo), " +
219			"RuteSo=VALUES(RuteSo), KorelisteSo=VALUES(KorelisteSo), " +
220			"Distributor=VALUES(Distributor), " +
221			"DBKBane=VALUES(DBKBane) ";
222	torben	2838
223	torben	3023
224
225	torben	2838
226			try (Connection con = DBConnection.getConnection()) {
227	torben	2845	//con.setAutoCommit(false);
228	torben	2838
229			try (PreparedStatement stmt = con.prepareStatement(sql)) {
230	torben	3018	int count = 0;
231
232	torben	2838	for (Address addr : addresses) {
233	torben	3023	stmt.setInt(1, addr.id);
234			stmt.setString(2, addr.ruteMandag);
235			stmt.setString(3, addr.korelisteMandag);
236			stmt.setString(4, addr.ruteTirsdag);
237			stmt.setString(5, addr.korelisteTirsdag);
238			stmt.setString(6, addr.ruteOnsdag);
239			stmt.setString(7, addr.korelisteOnsdag);
240			stmt.setString(8, addr.ruteTorsdag);
241			stmt.setString(9, addr.korelisteTorsdag);
242			stmt.setString(10, addr.ruteFredag);
243			stmt.setString(11, addr.korelisteFredag);
244			stmt.setString(12, addr.ruteLordag);
245			stmt.setString(13, addr.korelisteLordag);
246			stmt.setString(14, addr.ruteSondag);
247			stmt.setString(15, addr.korelisteSondag);
248	torben	2838
249	torben	3023	stmt.setString(16, addr.distributor);
250			stmt.setInt(17, addr.dbkBane);
251	torben	2838
252
253	torben	3023
254	torben	3018	stmt.addBatch();
255			count++;
256	torben	3023	if ( (count%200) == 0) {
257	torben	3018	stmt.executeBatch();
258	torben	3023
259			if ( (count%50000) == 0 ) {
260			logger.info("Update count " + count);
261			}
262	torben	3018	}
263	torben	2838	}
264	torben	3018
265			stmt.executeBatch(); //update remainding queries
266
267	torben	2838	}
268
269	torben	2845	//con.commit();
270	torben	2838	} catch (SQLException e) {
271	torben	2903	logger.warning("Error updating addresses", e );
272	torben	2838	}
273			}
274
275	torben	2898	/*
276	torben	2838	private static String nullify(String str) {
277			if (str == null)
278			return null;
279
280			if (str.equals("")) {
281			return null;
282			} else {
283			return str;
284			}
285			}
286
287
288			/*
289			private static int safeInt(String str) {
290			try {
291			return Integer.parseInt( str );
292			} catch (NumberFormatException e) {
293			return 0;
294			}
295			}
296
297
298
299			private static String coalesce(String s1, String s2) {
300			if (s1 != null)
301			return s1;
302
303			return s2;
304			}
305
306			*/
307			}