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