--- dao/DaoAdresseService/src/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/02/17 07:38:24 2316
+++ dao/DaoAdresseService/src/main/java/dk/daoas/daoadresseservice/db/DatabaseLayer.java 2015/08/25 08:46:29 2665
@@ -1,157 +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 {
+
+
+ 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;
- public static List getAllAdresses() throws SQLException {
-
- 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 "
- //+ "AND postnr = 8700" //DEBUG only
- ;
-
- 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 vejnavne = new DeduplicateHelper();
- DeduplicateHelper distributorer = new DeduplicateHelper();
-
-
- while (res.next()) {
-
- // Cache String instanserne for vejnavne for at mindske hukommelses forbruget
- String dbVejnavn = res.getString(2);
- String vejnavn = vejnavne.getInstance(dbVejnavn);
-
- // Cache String instanserne for distributor for at mindske hukommelses forbruget
- // (De-dubblicate)
- String dbDistributor = res.getString(2);
- String distributor = distributorer.getInstance(dbDistributor);
-
- Address a = new Address();
- a.id = res.getInt(1);
- a.vejnavn = vejnavn;
- a.husnr = res.getInt(3);
- a.husnrbogstav = 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 = distributor;
- 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;
- }
-
- 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 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 " +
-
- "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"
- ;
-
- 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( 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");
-
- 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);
- Map map = new HashMap();
-
- while (res.next()) {
-
- HundredePctBean bean = new HundredePctBean();
- bean.postnr = res.getInt(1);
- bean.distributor = 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;
-
- }
+
}