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