adressevedligehold/db/DatabaseLayerImplementation.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.util.DeduplicateHelper;
import dk.daoas.adressevedligehold.util.TimingHelper;

/*
 * TODO: Batched skrivning af updates
 * TODO: Skrivning af nye adresser 
 *  
 */

public class DatabaseLayerImplementation  {
        
        //static boolean DEBUG = false;
        
        
        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(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.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();
                        
                        
                        System.out.println("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms");
                        
                        return list;
                }
        }
        
        public void saveNewAddresses(List<Address> addresses) {
                System.out.println("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, " +
                                         "?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                
                
                try (Connection con = DBConnection.getConnection()) {
                        //con.setAutoCommit(false);
                        
                        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.execute();
                                        if (stmt.getUpdateCount() != 1) {
                                                System.out.println("Error executing insert: " + addr);
                                                addr.state = AddressState.CREATE_FAIL;
                                        }
                                        
                                }
                        }
                        
                        //con.commit();
                } catch (SQLException e) {
                        System.out.println("Error inserting addresses" + e.getMessage() );
                }
        }
        
        public void updateAddresses(List<Address> addresses) {
                System.out.println("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=? ";
                                        
                                        
                try (Connection con = DBConnection.getConnection()) {
                        //con.setAutoCommit(false);
                        
                        try (PreparedStatement stmt = con.prepareStatement(sql)) {
                                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.execute();
                                        if (stmt.getUpdateCount() != 1) {
                                                System.out.println("Error executing update");
                                        }
                                        
                                }
                        }
                        
                        //con.commit();
                } catch (SQLException e) {
                        System.out.println("Error updating addresses" + e.getMessage() );
                }
        }
        
        /*
        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	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
12	import dk.daoas.adressevedligehold.beans.Address;
13	import dk.daoas.adressevedligehold.beans.Address.AddressState;
14	import dk.daoas.adressevedligehold.util.DeduplicateHelper;
15	import dk.daoas.adressevedligehold.util.TimingHelper;
16
17	/*
18	* TODO: Batched skrivning af updates
19	* TODO: Skrivning af nye adresser
20	*
21	*/
22
23	public class DatabaseLayerImplementation {
24
25	//static boolean DEBUG = false;
26
27
28	public List<Address> getAllAdresses() throws SQLException {
29	TimingHelper timing = new TimingHelper();
30
31	//String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr >= 6000 " : "";
32
33	String sql =
34	"SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,latitude,longitude, "
35	+ "rutema,korelistema,ruteti,korelisteti,ruteon,korelisteon,ruteto,korelisteto,rutefr,korelistefr,rutelo,korelistelo,ruteso,korelisteso "
36	+ "FROM fulddaekning.adressetabel "
37	//+ debugFilter
38	;
39
40	try ( Connection conn = DBConnection.getConnection();
41	Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
42	) {
43	stmt.setFetchSize(Integer.MIN_VALUE);
44	ResultSet res = stmt.executeQuery(sql);
45
46	List<Address> list = new ArrayList<Address>(2600000);//initial capacity 2.6 mio
47
48	DeduplicateHelper<String> vejnavnCache = new DeduplicateHelper<String>(81920);
49	DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>();
50	DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
51	DeduplicateHelper<String> dirigeringsCache = new DeduplicateHelper<String>(16*1024);
52
53
54	while (res.next()) {
55
56	Address a = new Address();
57	a.id = res.getInt(1);
58	a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
59	a.husnr = (short) res.getInt(3);
60	a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
61	a.kommunekode = (short) res.getInt(5);
62	a.vejkode = (short)res.getInt(6);
63	a.postnr = (short)res.getInt(7);
64	a.gadeid = res.getInt(8);
65	a.distributor = distributorCache.getInstance(res.getString(9));
66	a.dbkBane = (short) res.getInt(10);
67	a.latitude = (float) res.getDouble(11);
68	a.longitude = (float) res.getDouble(12);
69
70	a.ruteMandag = dirigeringsCache.getInstance( res.getString(13) );
71	a.korelisteMandag = dirigeringsCache.getInstance( res.getString(14) );
72
73	a.ruteTirsdag = dirigeringsCache.getInstance( res.getString(15) );
74	a.korelisteTirsdag = dirigeringsCache.getInstance( res.getString(16) );
75
76	a.ruteOnsdag = dirigeringsCache.getInstance( res.getString(17) );
77	a.korelisteOnsdag = dirigeringsCache.getInstance( res.getString(18) );
78
79	a.ruteTorsdag = dirigeringsCache.getInstance( res.getString(19) );
80	a.korelisteTorsdag = dirigeringsCache.getInstance( res.getString(20) );
81
82	a.ruteFredag = dirigeringsCache.getInstance( res.getString(21) );
83	a.korelisteFredag = dirigeringsCache.getInstance( res.getString(22) );
84
85	a.ruteLordag = dirigeringsCache.getInstance( res.getString(23) );
86	a.korelisteLordag = dirigeringsCache.getInstance( res.getString(24) );
87
88	a.ruteSondag = dirigeringsCache.getInstance( res.getString(25) );
89	a.korelisteSondag = dirigeringsCache.getInstance( res.getString(26) );
90
91
92	list.add(a);
93	}
94	res.close();
95
96
97	System.out.println("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms");
98
99	return list;
100	}
101	}
102
103	public void saveNewAddresses(List<Address> addresses) {
104	System.out.println("DB::saveNewAddresses size:" + addresses.size() );
105
106	String sql = "INSERT IGNORE INTO fulddaekning.adressetabel " +
107	"(vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,dbkbane,indlast,aendret,david,googlechecked," +
108	"RuteMa,KorelisteMa,RuteTi,KorelisteTi,RuteOn,KorelisteOn,RuteTo,KorelisteTo,RuteFr,KorelisteFr,RuteLo,KorelisteLo,RuteSo,KorelisteSo) " +
109	"VALUES " +
110	"(?,?,?,?,?,?,?,?, now(), now(), ?, 0, " +
111	"?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
112
113
114	try (Connection con = DBConnection.getConnection()) {
115	//con.setAutoCommit(false);
116
117	try (PreparedStatement stmt = con.prepareStatement(sql)) {
118	for (Address addr : addresses) {
119
120	String david = String.format("%04d%03d%04d", addr.postnr, addr.kommunekode, addr.vejkode);
121
122	stmt.setString(1, addr.vejnavn);
123	stmt.setShort(2, addr.husnr);
124	stmt.setString(3, addr.husnrbogstav);
125	stmt.setShort(4, addr.kommunekode);
126	stmt.setShort(5, addr.vejkode);
127	stmt.setShort(6, addr.postnr);
128	stmt.setInt(7, addr.gadeid);
129	stmt.setShort(8, addr.dbkBane);
130	stmt.setString(9, david);
131
132	stmt.setString(10, addr.ruteMandag);
133	stmt.setString(11, addr.korelisteMandag);
134	stmt.setString(12, addr.ruteTirsdag);
135	stmt.setString(13, addr.korelisteTirsdag);
136	stmt.setString(14, addr.ruteOnsdag);
137	stmt.setString(15, addr.korelisteOnsdag);
138	stmt.setString(16, addr.ruteTorsdag);
139	stmt.setString(17, addr.korelisteTorsdag);
140	stmt.setString(18, addr.ruteFredag);
141	stmt.setString(19, addr.korelisteFredag);
142	stmt.setString(20, addr.ruteLordag);
143	stmt.setString(21, addr.korelisteLordag);
144	stmt.setString(22, addr.ruteSondag);
145	stmt.setString(23, addr.korelisteSondag);
146
147
148	stmt.execute();
149	if (stmt.getUpdateCount() != 1) {
150	System.out.println("Error executing insert: " + addr);
151	addr.state = AddressState.CREATE_FAIL;
152	}
153
154	}
155	}
156
157	//con.commit();
158	} catch (SQLException e) {
159	System.out.println("Error inserting addresses" + e.getMessage() );
160	}
161	}
162
163	public void updateAddresses(List<Address> addresses) {
164	System.out.println("DB::UpdateAddresses size:" + addresses.size() );
165
166	String sql = "UPDATE fulddaekning.adressetabel " +
167	"SET " +
168	"RuteMa=?, KorelisteMa=?, " +
169	"RuteTi=?, KorelisteTi=?, " +
170	"RuteOn=?, KorelisteOn=?, " +
171	"RuteTo=?, KorelisteTo=?, " +
172	"RuteFr=?, KorelisteFr=?, " +
173	"RuteLo=?, KorelisteLo=?, " +
174	"RuteSo=?, KorelisteSo=?, " +
175	"Distributor=?, " +
176	"DBKBane=?, " +
177	"Aendret=now() " +
178	"WHERE id=? ";
179
180
181
182	try (Connection con = DBConnection.getConnection()) {
183	//con.setAutoCommit(false);
184
185	try (PreparedStatement stmt = con.prepareStatement(sql)) {
186	for (Address addr : addresses) {
187	stmt.setString(1, addr.ruteMandag);
188	stmt.setString(2, addr.korelisteMandag);
189	stmt.setString(3, addr.ruteTirsdag);
190	stmt.setString(4, addr.korelisteTirsdag);
191	stmt.setString(5, addr.ruteOnsdag);
192	stmt.setString(6, addr.korelisteOnsdag);
193	stmt.setString(7, addr.ruteTorsdag);
194	stmt.setString(8, addr.korelisteTorsdag);
195	stmt.setString(9, addr.ruteFredag);
196	stmt.setString(10, addr.korelisteFredag);
197	stmt.setString(11, addr.ruteLordag);
198	stmt.setString(12, addr.korelisteLordag);
199	stmt.setString(13, addr.ruteSondag);
200	stmt.setString(14, addr.korelisteSondag);
201
202	stmt.setString(15, addr.distributor);
203	stmt.setInt(16, addr.dbkBane);
204	stmt.setInt(17, addr.id);
205
206
207	stmt.execute();
208	if (stmt.getUpdateCount() != 1) {
209	System.out.println("Error executing update");
210	}
211
212	}
213	}
214
215	//con.commit();
216	} catch (SQLException e) {
217	System.out.println("Error updating addresses" + e.getMessage() );
218	}
219	}
220
221	/*
222	private static String nullify(String str) {
223	if (str == null)
224	return null;
225
226	if (str.equals("")) {
227	return null;
228	} else {
229	return str;
230	}
231	}
232
233
234	/*
235	private static int safeInt(String str) {
236	try {
237	return Integer.parseInt( str );
238	} catch (NumberFormatException e) {
239	return 0;
240	}
241	}
242
243
244
245	private static String coalesce(String s1, String s2) {
246	if (s1 != null)
247	return s1;
248
249	return s2;
250	}
251
252	*/
253	}