--- dao/DaoAdresseService/src/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/02/23 12:30:41 2337
+++ dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/08/25 08:46:29 2665
@@ -1,165 +1,30 @@
package dk.daoas.daoadresseservice.db;
-import java.sql.Connection;
-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.util.DeduplicateHelper;
+import dk.daoas.daoadresseservice.beans.SearchResult;
-public class DatabaseLayer {
+public interface DatabaseLayer {
- static boolean DEBUG = false;
- public static List
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
- ;
-
- 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 list = new ArrayList(2600000);//initial capacity 2.6 mio
-
- DeduplicateHelper vejnavnCache = new DeduplicateHelper();
- DeduplicateHelper husnrbogstavCache = new DeduplicateHelper();
- DeduplicateHelper distributorCache = new DeduplicateHelper();
- DeduplicateHelper korelisteCache = new DeduplicateHelper();
- DeduplicateHelper ruteCache = new DeduplicateHelper();
-
-
- 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 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
- ;
-
- 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 transportCache = new DeduplicateHelper();
-
- List list = new ArrayList( 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();
- stmt.close();
- conn.close();
-
- System.out.println("Loaded " + list.size() + " extendedbeans");
-
- return list;
- }
-
- public static Map get100PctList() throws SQLException {
- String sql = "SELECT postnr,UPPER(distributor) as distributor,rute,koreliste,dbkbane " +
- "FROM bogleveringer.adresser_udenfor_daekning";
-
- 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);
+ public List getAllAdresses() throws SQLException ;
+
+ public List getAliasList() throws SQLException;
+
+ public List getExtendedAdresslist() throws SQLException;
+
+ public Map get100PctList() throws SQLException;
+
+ public void saveRequestLog(String brugerid, String postnr, String adresse, String omdelingsdag, SearchResult result) throws SQLException;
+
- Map map = new HashMap();
-
- DeduplicateHelper distributorCache = new DeduplicateHelper();
-
- 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();
- stmt.close();
- conn.close();
-
- System.out.println("Loaded " + map.size() + " 100pct beans");
-
- return map;
-
- }
+
}