daoadresseservice/db/DatabaseLayer.java

package dk.daoas.daoadresseservice.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.HashMap;
import java.util.List;
import java.util.Map;

import dk.daoas.daoadresseservice.DaekningsType;
import dk.daoas.daoadresseservice.beans.Address;
import dk.daoas.daoadresseservice.beans.AliasBean;
import dk.daoas.daoadresseservice.beans.ExtendedBean;
import dk.daoas.daoadresseservice.beans.HundredePctBean;
import dk.daoas.daoadresseservice.beans.LoggedAddress;
import dk.daoas.daoadresseservice.beans.SearchResult;
import dk.daoas.daoadresseservice.util.DeduplicateHelper;

public class DatabaseLayer {
        
        static boolean DEBUG = false;
        
        public static List<Address> getAllAdresses() throws SQLException {
                String debugFilter = DatabaseLayer.DEBUG ? " AND postnr = 8700 " : "";
                
                String sql = "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,koreliste,rute "
                                + "FROM fulddaekning.adressetabel "
                                + "WHERE gadeid IS NOT NULL "
                                + 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>();
                        DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>();
                        DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
                        DeduplicateHelper<String> korelisteCache = new DeduplicateHelper<String>();
                        DeduplicateHelper<String> ruteCache = new DeduplicateHelper<String>();
        
                        
                        while (res.next()) {
                                
                                Address a = new Address();
                                a.id = res.getInt(1);
                                a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
                                a.husnr = res.getInt(3);
                                a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
                                a.kommunekode = res.getInt(5);
                                a.vejkode = res.getInt(6);
                                a.postnr = res.getInt(7);
                                a.gadeid = res.getLong(8);
                                a.distributor = distributorCache.getInstance(res.getString(9));
                                a.dbkBane = res.getInt(10);
                                a.koreliste = korelisteCache.getInstance( res.getString(11) );
                                a.rute = ruteCache.getInstance( res.getString(12) );
                                
                                //a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn);
                                
                                if (a.rute != null && a.rute.length()> 0) {
                                        a.daekningsType = DaekningsType.DAEKNING_DIREKTE;
                                } else {
                                        a.daekningsType = DaekningsType.DAEKNING_IKKEDAEKKET;
                                }                       
                                
                                list.add(a);
                        }
                        res.close();
                        stmt.close();
                        conn.close();
                        
                        System.out.println("Loaded " + list.size() + " adresses");
                        
                        return list;
                }
        }
        
        public static List<AliasBean> getAliasList() throws SQLException {
                

                String sql = "SELECT postnr,vejnavn,aliasvejnavn " +
                                        "FROM bogleveringer.vejtabelprod " 
                                        ;
                
                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);
                        
                        DeduplicateHelper<String> vejCache = new DeduplicateHelper<String>();
                        
                        List<AliasBean> list = new ArrayList<AliasBean>( 5000); 
                        while (res.next()) {            
                                
                                AliasBean ab = new AliasBean();
                                ab.postnr = res.getInt(1);
                                ab.vejnavn = vejCache.getInstance( res.getString(2) );
                                ab.aliasVejnavn = vejCache.getInstance( res.getString(3) );
                                                        
                                list.add(ab);
                        }
                
                        res.close();
                        
                        System.out.println("Loaded " + list.size() + " aliase beans");
                        
                        return list;
                }

        }
        
        public static List<ExtendedBean> getExtendedAdresslist() throws SQLException {
                String debugFilter1 = DatabaseLayer.DEBUG ? " WHERE orgPostnr = 8700 " : "";
                String debugFilter2 = DatabaseLayer.DEBUG ? " AND orgPostnr = 8700 " : "";
                
                
                String sql = "select orgid, a.id as targetid, afstand, LOWER(type) as type from fulddaekning.afstand_anden_rute a " +
                                        "join odbc.transporttype t " + 
                                        "on t.Art = 'Transpost' " +
                                        "and ( (t.Type = 'Cykel' and a.Afstand < 1.001) or (t.Type = 'Scooter' and a.Afstand < 1.201) or (t.Type = 'Bil' and a.Afstand < 2.601) ) " +
                                        "and t.Rute = a.Rute " + 
                                        debugFilter1 +
                                        
                                        "UNION ALL " +
                                        
                                        "SELECT orgid, a.id as targetid, afstand,'' as type FROM  fulddaekning.afstand_anden_rute_bk a " +
                                        "left join bogleveringer.postnummerdistributor d on d.PostNr = a.orgPostnr " + 
                                        "WHERE d.Distributor <> 10057 " +
                                        debugFilter2
                                        ;
                
                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);
                        
                        DeduplicateHelper<String> transportCache = new DeduplicateHelper<String>();
                        
                        List<ExtendedBean> list = new ArrayList<ExtendedBean>( 350000); //Initial capacity 350K
                        while (res.next()) {            
                                
                                ExtendedBean eb = new ExtendedBean();
                                eb.orgId = res.getInt(1);
                                eb.targetId = res.getInt(2);
                                eb.afstand = res.getDouble(3);
                                eb.transport = transportCache.getInstance(res.getString(4));
                                                        
                                list.add(eb);
                        }
                        
                        res.close();
                        
                        System.out.println("Loaded " + list.size() + " extendedbeans");
                        
                        return list;
                }
        }
        
        public static Map<Integer,HundredePctBean> get100PctList() throws SQLException {
                String sql = "SELECT postnr,UPPER(distributor) as distributor,rute,koreliste,dbkbane " + 
                                         "FROM bogleveringer.adresser_udenfor_daekning";
                
                try ( Connection conn = DBConnection.getConnection();           
                                Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
                                ) {
                        ResultSet res = stmt.executeQuery(sql);
        
                        Map<Integer,HundredePctBean> map = new HashMap<Integer,HundredePctBean>();
                        
                        DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
                        
                        while (res.next()) {
                                
                                
                                HundredePctBean bean = new HundredePctBean();
                                bean.postnr = res.getInt(1);
                                bean.distributor = distributorCache.getInstance(res.getString(2));
                                bean.rute = res.getString(3);
                                bean.koreliste = res.getString(4);
                                bean.dbkBane = res.getInt(5);                   
                                                        
                                map.put(bean.postnr, bean);
                        }
                        
                        res.close();
                        
                        System.out.println("Loaded " + map.size() + " 100pct beans");
                        
                        return map;
                }
                
        }
        
        public static void saveRequestLog(String brugerid, String postnr, String adresse, SearchResult result) throws SQLException {
                String sql = "INSERT INTO logs.hentruteinformation (postnr,adresse,vejnavn,googlevejnavn,husnr,husnr_bogstav,etage,lejlighed,rest,brugerid,status, indlast) " +
                                        "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,  NOW() )";
                

                try ( Connection conn = DBConnection.getConnection();           
                                PreparedStatement stmt = conn.prepareStatement(sql);
                        ) {
                        
                        stmt.setInt( 1, safeInt(postnr) );
                        stmt.setString( 2, adresse);
                        stmt.setString( 3, result.splitResult.vej);
                        stmt.setString( 4, coalesce(result.googleVej,result.osmVej) );
                        stmt.setString( 5, nullify(result.splitResult.husnr) );
                        stmt.setString( 6, result.splitResult.litra);
                        stmt.setString( 7, result.splitResult.etage);
                        stmt.setString( 8, result.splitResult.lejlighed);
                        stmt.setString( 9, result.splitResult.resten);
                        stmt.setString(10, brugerid);
                        stmt.setInt(11, getStatusInt(result.status) );
                        
                        stmt.executeUpdate();
                        
                }               
        }
        
        /*
         * Bruges til at sammenligne gammel og ny adresse service - kan fjernes engang efter at vi er skiftet til ny service 
         */
        public static List<LoggedAddress> getLoggedAdresses(int antaldage) throws SQLException {
                String sql = "select postnr,adresse,status from logs.hentruteinformation where indlast>=date_sub(curdate(), interval " + antaldage + " day) " +
                                "and status IN (10,11,12) " +
                                "group by postnr,adresse "
                                ;

                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<LoggedAddress> result = new ArrayList<LoggedAddress>();

                        while (res.next()) {
                                LoggedAddress a = new LoggedAddress();                          
                                a.postnr = res.getInt(1);                               
                                a.adresse = res.getString(2);
                                a.status = res.getInt(3);

                                result.add(a);
                        }

                        res.close();

                        return result;
                }
        }
        
        private static int getStatusInt(SearchResult.Status status) {

                switch (status) {
                case ERROR_UNKNOWN_POSTAL:
                        return 20;
                case ERROR_MISSING_HOUSENUMBER:
                        return 21;
                case ERROR_POSTBOX:
                        return 22;                      
                case ERROR_UNKNOWN_STREETNAME:
                        return 23;
                case ERROR_UNKNOWN_ADDRESSPOINT:
                        return 24;
                case STATUS_NOT_COVERED:                        
                        return 25;
                case ERROR_INTERNAL: //                 
                        return 26;                      
                        
                case STATUS_OK:
                        return 29;
                        
                default:
                        return 30;
                }
        }
        
        private static int safeInt(String str) {
                try {
                        return Integer.parseInt( str );
                } catch (NumberFormatException e) {
                        return 0;
                }
        }
        
        private static String nullify(String str) {
                if (str == null)
                        return null;
                
                if (str.equals("")) {
                        return null;
                } else {
                        return str;
                }
        }
        
        private static String coalesce(String s1, String s2) {
                if (s1 != null)
                        return s1;
                
                return s2;
        }

        
}
1	package dk.daoas.daoadresseservice.db;
2
3
4	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.HashMap;
11	import java.util.List;
12	import java.util.Map;
13
14	import dk.daoas.daoadresseservice.DaekningsType;
15	import dk.daoas.daoadresseservice.beans.Address;
16	import dk.daoas.daoadresseservice.beans.AliasBean;
17	import dk.daoas.daoadresseservice.beans.ExtendedBean;
18	import dk.daoas.daoadresseservice.beans.HundredePctBean;
19	import dk.daoas.daoadresseservice.beans.LoggedAddress;
20	import dk.daoas.daoadresseservice.beans.SearchResult;
21	import dk.daoas.daoadresseservice.util.DeduplicateHelper;
22
23	public class DatabaseLayer {
24
25	static boolean DEBUG = false;
26
27	public static List<Address> getAllAdresses() throws SQLException {
28	String debugFilter = DatabaseLayer.DEBUG ? " AND postnr = 8700 " : "";
29
30	String sql = "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,koreliste,rute "
31	+ "FROM fulddaekning.adressetabel "
32	+ "WHERE gadeid IS NOT NULL "
33	+ debugFilter
34	;
35
36	try ( Connection conn = DBConnection.getConnection();
37	Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
38	) {
39	stmt.setFetchSize(Integer.MIN_VALUE);
40	ResultSet res = stmt.executeQuery(sql);
41
42	List<Address> list = new ArrayList<Address>(2600000);//initial capacity 2.6 mio
43
44	DeduplicateHelper<String> vejnavnCache = new DeduplicateHelper<String>();
45	DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>();
46	DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
47	DeduplicateHelper<String> korelisteCache = new DeduplicateHelper<String>();
48	DeduplicateHelper<String> ruteCache = new DeduplicateHelper<String>();
49
50
51	while (res.next()) {
52
53	Address a = new Address();
54	a.id = res.getInt(1);
55	a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
56	a.husnr = res.getInt(3);
57	a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
58	a.kommunekode = res.getInt(5);
59	a.vejkode = res.getInt(6);
60	a.postnr = res.getInt(7);
61	a.gadeid = res.getLong(8);
62	a.distributor = distributorCache.getInstance(res.getString(9));
63	a.dbkBane = res.getInt(10);
64	a.koreliste = korelisteCache.getInstance( res.getString(11) );
65	a.rute = ruteCache.getInstance( res.getString(12) );
66
67	//a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn);
68
69	if (a.rute != null && a.rute.length()> 0) {
70	a.daekningsType = DaekningsType.DAEKNING_DIREKTE;
71	} else {
72	a.daekningsType = DaekningsType.DAEKNING_IKKEDAEKKET;
73	}
74
75	list.add(a);
76	}
77	res.close();
78	stmt.close();
79	conn.close();
80
81	System.out.println("Loaded " + list.size() + " adresses");
82
83	return list;
84	}
85	}
86
87	public static List<AliasBean> getAliasList() throws SQLException {
88
89
90	String sql = "SELECT postnr,vejnavn,aliasvejnavn " +
91	"FROM bogleveringer.vejtabelprod "
92	;
93
94	try ( Connection conn = DBConnection.getConnection();
95	Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
96	) {
97
98	stmt.setFetchSize(Integer.MIN_VALUE);
99
100	ResultSet res = stmt.executeQuery(sql);
101
102	DeduplicateHelper<String> vejCache = new DeduplicateHelper<String>();
103
104	List<AliasBean> list = new ArrayList<AliasBean>( 5000);
105	while (res.next()) {
106
107	AliasBean ab = new AliasBean();
108	ab.postnr = res.getInt(1);
109	ab.vejnavn = vejCache.getInstance( res.getString(2) );
110	ab.aliasVejnavn = vejCache.getInstance( res.getString(3) );
111
112	list.add(ab);
113	}
114
115	res.close();
116
117	System.out.println("Loaded " + list.size() + " aliase beans");
118
119	return list;
120	}
121
122	}
123
124	public static List<ExtendedBean> getExtendedAdresslist() throws SQLException {
125	String debugFilter1 = DatabaseLayer.DEBUG ? " WHERE orgPostnr = 8700 " : "";
126	String debugFilter2 = DatabaseLayer.DEBUG ? " AND orgPostnr = 8700 " : "";
127
128
129	String sql = "select orgid, a.id as targetid, afstand, LOWER(type) as type from fulddaekning.afstand_anden_rute a " +
130	"join odbc.transporttype t " +
131	"on t.Art = 'Transpost' " +
132	"and ( (t.Type = 'Cykel' and a.Afstand < 1.001) or (t.Type = 'Scooter' and a.Afstand < 1.201) or (t.Type = 'Bil' and a.Afstand < 2.601) ) " +
133	"and t.Rute = a.Rute " +
134	debugFilter1 +
135
136	"UNION ALL " +
137
138	"SELECT orgid, a.id as targetid, afstand,'' as type FROM fulddaekning.afstand_anden_rute_bk a " +
139	"left join bogleveringer.postnummerdistributor d on d.PostNr = a.orgPostnr " +
140	"WHERE d.Distributor <> 10057 " +
141	debugFilter2
142	;
143
144	try ( Connection conn = DBConnection.getConnection();
145	Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
146	) {
147
148
149	stmt.setFetchSize(Integer.MIN_VALUE);
150
151	ResultSet res = stmt.executeQuery(sql);
152
153	DeduplicateHelper<String> transportCache = new DeduplicateHelper<String>();
154
155	List<ExtendedBean> list = new ArrayList<ExtendedBean>( 350000); //Initial capacity 350K
156	while (res.next()) {
157
158	ExtendedBean eb = new ExtendedBean();
159	eb.orgId = res.getInt(1);
160	eb.targetId = res.getInt(2);
161	eb.afstand = res.getDouble(3);
162	eb.transport = transportCache.getInstance(res.getString(4));
163
164	list.add(eb);
165	}
166
167	res.close();
168
169	System.out.println("Loaded " + list.size() + " extendedbeans");
170
171	return list;
172	}
173	}
174
175	public static Map<Integer,HundredePctBean> get100PctList() throws SQLException {
176	String sql = "SELECT postnr,UPPER(distributor) as distributor,rute,koreliste,dbkbane " +
177	"FROM bogleveringer.adresser_udenfor_daekning";
178
179	try ( Connection conn = DBConnection.getConnection();
180	Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
181	) {
182	ResultSet res = stmt.executeQuery(sql);
183
184	Map<Integer,HundredePctBean> map = new HashMap<Integer,HundredePctBean>();
185
186	DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
187
188	while (res.next()) {
189
190
191	HundredePctBean bean = new HundredePctBean();
192	bean.postnr = res.getInt(1);
193	bean.distributor = distributorCache.getInstance(res.getString(2));
194	bean.rute = res.getString(3);
195	bean.koreliste = res.getString(4);
196	bean.dbkBane = res.getInt(5);
197
198	map.put(bean.postnr, bean);
199	}
200
201	res.close();
202
203	System.out.println("Loaded " + map.size() + " 100pct beans");
204
205	return map;
206	}
207
208	}
209
210	public static void saveRequestLog(String brugerid, String postnr, String adresse, SearchResult result) throws SQLException {
211	String sql = "INSERT INTO logs.hentruteinformation (postnr,adresse,vejnavn,googlevejnavn,husnr,husnr_bogstav,etage,lejlighed,rest,brugerid,status, indlast) " +
212	"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW() )";
213
214
215
216	try ( Connection conn = DBConnection.getConnection();
217	PreparedStatement stmt = conn.prepareStatement(sql);
218	) {
219
220	stmt.setInt( 1, safeInt(postnr) );
221	stmt.setString( 2, adresse);
222	stmt.setString( 3, result.splitResult.vej);
223	stmt.setString( 4, coalesce(result.googleVej,result.osmVej) );
224	stmt.setString( 5, nullify(result.splitResult.husnr) );
225	stmt.setString( 6, result.splitResult.litra);
226	stmt.setString( 7, result.splitResult.etage);
227	stmt.setString( 8, result.splitResult.lejlighed);
228	stmt.setString( 9, result.splitResult.resten);
229	stmt.setString(10, brugerid);
230	stmt.setInt(11, getStatusInt(result.status) );
231
232	stmt.executeUpdate();
233
234	}
235	}
236
237	/*
238	* Bruges til at sammenligne gammel og ny adresse service - kan fjernes engang efter at vi er skiftet til ny service
239	*/
240	public static List<LoggedAddress> getLoggedAdresses(int antaldage) throws SQLException {
241	String sql = "select postnr,adresse,status from logs.hentruteinformation where indlast>=date_sub(curdate(), interval " + antaldage + " day) " +
242	"and status IN (10,11,12) " +
243	"group by postnr,adresse "
244	;
245
246	try ( Connection conn = DBConnection.getConnection();
247	Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
248	) {
249
250
251	stmt.setFetchSize(Integer.MIN_VALUE);
252
253	ResultSet res = stmt.executeQuery(sql);
254
255	List<LoggedAddress> result = new ArrayList<LoggedAddress>();
256
257	while (res.next()) {
258	LoggedAddress a = new LoggedAddress();
259	a.postnr = res.getInt(1);
260	a.adresse = res.getString(2);
261	a.status = res.getInt(3);
262
263	result.add(a);
264	}
265
266	res.close();
267
268	return result;
269	}
270	}
271
272	private static int getStatusInt(SearchResult.Status status) {
273
274	switch (status) {
275	case ERROR_UNKNOWN_POSTAL:
276	return 20;
277	case ERROR_MISSING_HOUSENUMBER:
278	return 21;
279	case ERROR_POSTBOX:
280	return 22;
281	case ERROR_UNKNOWN_STREETNAME:
282	return 23;
283	case ERROR_UNKNOWN_ADDRESSPOINT:
284	return 24;
285	case STATUS_NOT_COVERED:
286	return 25;
287	case ERROR_INTERNAL: //
288	return 26;
289
290	case STATUS_OK:
291	return 29;
292
293	default:
294	return 30;
295	}
296	}
297
298	private static int safeInt(String str) {
299	try {
300	return Integer.parseInt( str );
301	} catch (NumberFormatException e) {
302	return 0;
303	}
304	}
305
306	private static String nullify(String str) {
307	if (str == null)
308	return null;
309
310	if (str.equals("")) {
311	return null;
312	} else {
313	return str;
314	}
315	}
316
317	private static String coalesce(String s1, String s2) {
318	if (s1 != null)
319	return s1;
320
321	return s2;
322	}
323
324
325	}