adressevedligehold/db/DatabaseCoverageUpdate.java

package dk.daoas.adressevedligehold.db;

import java.io.InputStream;
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 java.util.Properties;

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();
        
        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();
                
                //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);
                        
                        DeduplicateHelper<String> baneCache = new DeduplicateHelper<String>();
        
                        
                        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 = baneCache.getInstance(res.getString(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 int saveNewAddresses(List<Address> addresses) throws Exception {
                
                logger.info("DB::saveNewAddresses size:" + addresses.size() );
                
                final String SQL_INSERT_NEW_ADDRESS = queries.getProperty("SQL_INSERT_NEW_ADDRESS");
                
                
                try (Connection con = DBConnection.getConnection()) {

                        
                        try (PreparedStatement stmt = con.prepareStatement(SQL_INSERT_NEW_ADDRESS)) {
                                int insertCount = 0;
                                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.setString(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) {
                                                insertCount++;
                                        } else {
                                                logger.info("Error executing insert: " + addr);
                                                addr.state = AddressState.CREATE_FAIL;
                                        }
                                        
                                }
                                
                                return insertCount;
                        }
                        
                        //con.commit();
                } catch (SQLException e) {
                        logger.warning("Error inserting addresses", e  );
                        throw e;
                }
        }
        
        public int updateAddresses(List<Address> addresses) throws Exception{
                logger.info("DB::UpdateAddresses size:" + addresses.size() );
                

                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_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS)) {
                                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.setString(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
                                return count;
                        }
                        
                        //con.commit();
                } catch (SQLException e) {
                        logger.warning("Error updating addresses", e );
                        throw e;
                }
                                
        }
        
}
1	torben	2838	package dk.daoas.adressevedligehold.db;
2
3	torben	3025	import java.io.InputStream;
4	torben	2838	import java.sql.Connection;
5			import java.sql.PreparedStatement;
6			import java.sql.ResultSet;
7			import java.sql.SQLException;
8			import java.sql.Statement;
9			import java.util.ArrayList;
10			import java.util.List;
11	torben	3025	import java.util.Properties;
12	torben	2838
13			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	3035	private TaskLogger logger = TaskLogger.getInstance();
28	torben	2838
29	torben	3035	Properties queries;
30	torben	2903
31	torben	3035	public DatabaseCoverageUpdate() throws SQLException {
32
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.xml");
36
37			queries = new Properties();
38	torben	3025	try {
39			queries.loadFromXML(is);
40			} catch (Exception e) {
41	torben	3035	throw new SQLException("Error loading queries", e);
42	torben	3025	}
43	torben	3035
44	torben	3025	}
45
46
47	torben	2838	public List<Address> getAllAdresses() throws SQLException {
48			TimingHelper timing = new TimingHelper();
49
50	torben	2847	//String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr >= 6000 " : "";
51	torben	2838
52			String sql =
53			"SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,latitude,longitude, "
54			+ "rutema,korelistema,ruteti,korelisteti,ruteon,korelisteon,ruteto,korelisteto,rutefr,korelistefr,rutelo,korelistelo,ruteso,korelisteso "
55			+ "FROM fulddaekning.adressetabel "
56	torben	2847	//+ debugFilter
57	torben	2838	;
58
59			try ( Connection conn = DBConnection.getConnection();
60	torben	2937	Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
61	torben	2838	) {
62			stmt.setFetchSize(Integer.MIN_VALUE);
63			ResultSet res = stmt.executeQuery(sql);
64
65			List<Address> list = new ArrayList<Address>(2600000);//initial capacity 2.6 mio
66
67			DeduplicateHelper<String> vejnavnCache = new DeduplicateHelper<String>(81920);
68			DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>();
69			DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
70	torben	3111	DeduplicateHelper<String> dirigeringsCache = new DeduplicateHelper<String>(16*1024);
71
72			DeduplicateHelper<String> baneCache = new DeduplicateHelper<String>();
73	torben	2838
74
75			while (res.next()) {
76
77			Address a = new Address();
78			a.id = res.getInt(1);
79			a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
80			a.husnr = (short) res.getInt(3);
81			a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
82			a.kommunekode = (short) res.getInt(5);
83			a.vejkode = (short)res.getInt(6);
84			a.postnr = (short)res.getInt(7);
85			a.gadeid = res.getInt(8);
86			a.distributor = distributorCache.getInstance(res.getString(9));
87	torben	3111	a.dbkBane = baneCache.getInstance(res.getString(10));
88	torben	2838	a.latitude = (float) res.getDouble(11);
89			a.longitude = (float) res.getDouble(12);
90
91			a.ruteMandag = dirigeringsCache.getInstance( res.getString(13) );
92			a.korelisteMandag = dirigeringsCache.getInstance( res.getString(14) );
93
94			a.ruteTirsdag = dirigeringsCache.getInstance( res.getString(15) );
95			a.korelisteTirsdag = dirigeringsCache.getInstance( res.getString(16) );
96
97			a.ruteOnsdag = dirigeringsCache.getInstance( res.getString(17) );
98			a.korelisteOnsdag = dirigeringsCache.getInstance( res.getString(18) );
99
100			a.ruteTorsdag = dirigeringsCache.getInstance( res.getString(19) );
101			a.korelisteTorsdag = dirigeringsCache.getInstance( res.getString(20) );
102
103			a.ruteFredag = dirigeringsCache.getInstance( res.getString(21) );
104			a.korelisteFredag = dirigeringsCache.getInstance( res.getString(22) );
105
106			a.ruteLordag = dirigeringsCache.getInstance( res.getString(23) );
107			a.korelisteLordag = dirigeringsCache.getInstance( res.getString(24) );
108
109			a.ruteSondag = dirigeringsCache.getInstance( res.getString(25) );
110			a.korelisteSondag = dirigeringsCache.getInstance( res.getString(26) );
111
112
113			list.add(a);
114			}
115			res.close();
116
117
118	torben	2903	logger.info("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms");
119	torben	2838
120			return list;
121			}
122			}
123
124	torben	3115	public int saveNewAddresses(List<Address> addresses) throws Exception {
125	torben	3023
126	torben	2903	logger.info("DB::saveNewAddresses size:" + addresses.size() );
127	torben	2840
128	torben	3025	final String SQL_INSERT_NEW_ADDRESS = queries.getProperty("SQL_INSERT_NEW_ADDRESS");
129	torben	2876
130
131	torben	3025
132
133	torben	2876	try (Connection con = DBConnection.getConnection()) {
134	torben	2950
135	torben	2876
136	torben	3025	try (PreparedStatement stmt = con.prepareStatement(SQL_INSERT_NEW_ADDRESS)) {
137	torben	3115	int insertCount = 0;
138	torben	2876	for (Address addr : addresses) {
139
140			String david = String.format("%04d%03d%04d", addr.postnr, addr.kommunekode, addr.vejkode);
141
142			stmt.setString(1, addr.vejnavn);
143			stmt.setShort(2, addr.husnr);
144			stmt.setString(3, addr.husnrbogstav);
145			stmt.setShort(4, addr.kommunekode);
146			stmt.setShort(5, addr.vejkode);
147			stmt.setShort(6, addr.postnr);
148			stmt.setInt(7, addr.gadeid);
149	torben	3111	stmt.setString(8, addr.dbkBane);
150	torben	2876	stmt.setString(9, david);
151
152			stmt.setString(10, addr.ruteMandag);
153			stmt.setString(11, addr.korelisteMandag);
154			stmt.setString(12, addr.ruteTirsdag);
155			stmt.setString(13, addr.korelisteTirsdag);
156			stmt.setString(14, addr.ruteOnsdag);
157			stmt.setString(15, addr.korelisteOnsdag);
158			stmt.setString(16, addr.ruteTorsdag);
159			stmt.setString(17, addr.korelisteTorsdag);
160			stmt.setString(18, addr.ruteFredag);
161			stmt.setString(19, addr.korelisteFredag);
162			stmt.setString(20, addr.ruteLordag);
163			stmt.setString(21, addr.korelisteLordag);
164			stmt.setString(22, addr.ruteSondag);
165			stmt.setString(23, addr.korelisteSondag);
166	torben	3003
167			stmt.setString(24, addr.kommentar);
168
169			if (addr.latitude != 0.0) {
170			stmt.setDouble(25, addr.latitude);
171			} else {
172			stmt.setNull(25, java.sql.Types.DOUBLE);
173			}
174			if (addr.longitude != 0.0) {
175			stmt.setDouble(26, addr.longitude);
176			} else {
177			stmt.setNull(26, java.sql.Types.DOUBLE);
178			}
179	torben	2876
180
181			stmt.execute();
182	torben	3115	if (stmt.getUpdateCount() == 1) {
183			insertCount++;
184			} else {
185	torben	2903	logger.info("Error executing insert: " + addr);
186	torben	2876	addr.state = AddressState.CREATE_FAIL;
187			}
188
189			}
190	torben	3115
191			return insertCount;
192	torben	2876	}
193
194			//con.commit();
195			} catch (SQLException e) {
196	torben	2903	logger.warning("Error inserting addresses", e );
197	torben	3024	throw e;
198	torben	2876	}
199	torben	2840	}
200
201	torben	3115	public int updateAddresses(List<Address> addresses) throws Exception{
202	torben	2903	logger.info("DB::UpdateAddresses size:" + addresses.size() );
203	torben	2838
204	torben	3025
205			final String SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS = queries.getProperty("SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS");
206	torben	2838
207
208	torben	3023
209	torben	2838
210			try (Connection con = DBConnection.getConnection()) {
211	torben	2845	//con.setAutoCommit(false);
212	torben	2838
213	torben	3025	try (PreparedStatement stmt = con.prepareStatement(SQL_INSERT_ON_DUPLICATE_KEY_UPDATE_ADDRESS)) {
214	torben	3018	int count = 0;
215
216	torben	2838	for (Address addr : addresses) {
217	torben	3023	stmt.setInt(1, addr.id);
218			stmt.setString(2, addr.ruteMandag);
219			stmt.setString(3, addr.korelisteMandag);
220			stmt.setString(4, addr.ruteTirsdag);
221			stmt.setString(5, addr.korelisteTirsdag);
222			stmt.setString(6, addr.ruteOnsdag);
223			stmt.setString(7, addr.korelisteOnsdag);
224			stmt.setString(8, addr.ruteTorsdag);
225			stmt.setString(9, addr.korelisteTorsdag);
226			stmt.setString(10, addr.ruteFredag);
227			stmt.setString(11, addr.korelisteFredag);
228			stmt.setString(12, addr.ruteLordag);
229			stmt.setString(13, addr.korelisteLordag);
230			stmt.setString(14, addr.ruteSondag);
231			stmt.setString(15, addr.korelisteSondag);
232	torben	2838
233	torben	3023	stmt.setString(16, addr.distributor);
234	torben	3111	stmt.setString(17, addr.dbkBane);
235	torben	2838
236
237	torben	3023
238	torben	3018	stmt.addBatch();
239			count++;
240	torben	3023	if ( (count%200) == 0) {
241	torben	3018	stmt.executeBatch();
242	torben	3023
243			if ( (count%50000) == 0 ) {
244			logger.info("Update count " + count);
245			}
246	torben	3018	}
247	torben	2838	}
248	torben	3018
249			stmt.executeBatch(); //update remainding queries
250	torben	3115	return count;
251	torben	2838	}
252
253	torben	2845	//con.commit();
254	torben	2838	} catch (SQLException e) {
255	torben	2903	logger.warning("Error updating addresses", e );
256	torben	3024	throw e;
257	torben	2838	}
258	torben	3115
259	torben	2838	}
260
261			}