/[projects]/dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayer.java
ViewVC logotype

Diff of /dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayer.java

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

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