/[projects]/dao/DaoAdresseVedligehold/src/main/java/dk/daoas/adressevedligehold/db/DatabaseLayerImplementation.java
ViewVC logotype

Annotation of /dao/DaoAdresseVedligehold/src/main/java/dk/daoas/adressevedligehold/db/DatabaseLayerImplementation.java

Parent Directory Parent Directory | Revision Log Revision Log

Revision 2844 - (hide annotations) (download)
Mon Jan 25 21:43:59 2016 UTC (8 years, 4 months ago) by torben
File size: 5886 byte(s) 
Implemented AdressManager.visitRange()

Added AddressSource.validate() to let manager do a pre-validation before loading data from DB

Added support for DAO

Make Task and webpage show error messages
1 torben 2838 package dk.daoas.adressevedligehold.db;
2    
3     import java.sql.Connection;
4     import java.sql.PreparedStatement;
5     import java.sql.ResultSet;
6     import java.sql.SQLException;
7     import java.sql.Statement;
8     import java.util.ArrayList;
9     import java.util.List;
10    
11     import dk.daoas.adressevedligehold.beans.Address;
12     import dk.daoas.adressevedligehold.util.DeduplicateHelper;
13     import dk.daoas.adressevedligehold.util.TimingHelper;
14    
15 torben 2840 /*
16     * TODO: Batched skrivning af updates
17     * TODO: Skrivning af nye adresser
18     *
19     */
20 torben 2838
21     public class DatabaseLayerImplementation {
22    
23 torben 2844 static boolean DEBUG = true;
24 torben 2838
25    
26     public List<Address> getAllAdresses() throws SQLException {
27     TimingHelper timing = new TimingHelper();
28    
29 torben 2844 String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr >= 6000 " : "";
30 torben 2838
31     String sql =
32     "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,latitude,longitude, "
33     + "rutema,korelistema,ruteti,korelisteti,ruteon,korelisteon,ruteto,korelisteto,rutefr,korelistefr,rutelo,korelistelo,ruteso,korelisteso "
34     + "FROM fulddaekning.adressetabel "
35     + debugFilter
36     ;
37    
38     try ( Connection conn = DBConnection.getConnection();
39     Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
40     ) {
41     stmt.setFetchSize(Integer.MIN_VALUE);
42     ResultSet res = stmt.executeQuery(sql);
43    
44     List<Address> list = new ArrayList<Address>(2600000);//initial capacity 2.6 mio
45    
46     DeduplicateHelper<String> vejnavnCache = new DeduplicateHelper<String>(81920);
47     DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>();
48     DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
49     DeduplicateHelper<String> dirigeringsCache = new DeduplicateHelper<String>(16*1024);
50    
51    
52     while (res.next()) {
53    
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     a.latitude = (float) res.getDouble(11);
66     a.longitude = (float) res.getDouble(12);
67    
68     a.ruteMandag = dirigeringsCache.getInstance( res.getString(13) );
69     a.korelisteMandag = dirigeringsCache.getInstance( res.getString(14) );
70    
71     a.ruteTirsdag = dirigeringsCache.getInstance( res.getString(15) );
72     a.korelisteTirsdag = dirigeringsCache.getInstance( res.getString(16) );
73    
74     a.ruteOnsdag = dirigeringsCache.getInstance( res.getString(17) );
75     a.korelisteOnsdag = dirigeringsCache.getInstance( res.getString(18) );
76    
77     a.ruteTorsdag = dirigeringsCache.getInstance( res.getString(19) );
78     a.korelisteTorsdag = dirigeringsCache.getInstance( res.getString(20) );
79    
80     a.ruteFredag = dirigeringsCache.getInstance( res.getString(21) );
81     a.korelisteFredag = dirigeringsCache.getInstance( res.getString(22) );
82    
83     a.ruteLordag = dirigeringsCache.getInstance( res.getString(23) );
84     a.korelisteLordag = dirigeringsCache.getInstance( res.getString(24) );
85    
86     a.ruteSondag = dirigeringsCache.getInstance( res.getString(25) );
87     a.korelisteSondag = dirigeringsCache.getInstance( res.getString(26) );
88    
89    
90     list.add(a);
91     }
92     res.close();
93    
94    
95     System.out.println("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms");
96    
97     return list;
98     }
99     }
100    
101 torben 2840 private void updateAddressesBatch(List<Address> addresses) throws SQLException {
102    
103     }
104    
105 torben 2838 public void updateAddresses(List<Address> addresses) {
106    
107     String sql = "UPDATE fulddaekning.adressetabel " +
108     "SET " +
109     "RuteMa=?, KorelisteMa=?, " +
110     "RuteTi=?, KorelisteTi=?, " +
111     "RuteOn=?, KorelisteOn=?, " +
112     "RuteTo=?, KorelisteTo=?, " +
113     "RuteFr=?, KorelisteFr=?, " +
114     "RuteLo=?, KorelisteLo=?, " +
115     "RuteSo=?, KorelisteSo=? " +
116     "WHERE id=?";
117    
118    
119    
120     try (Connection con = DBConnection.getConnection()) {
121     con.setAutoCommit(false);
122    
123     try (PreparedStatement stmt = con.prepareStatement(sql)) {
124     for (Address addr : addresses) {
125     stmt.setString(1, addr.ruteMandag);
126     stmt.setString(2, addr.korelisteMandag);
127     stmt.setString(3, addr.ruteTirsdag);
128     stmt.setString(4, addr.korelisteTirsdag);
129     stmt.setString(5, addr.ruteOnsdag);
130     stmt.setString(6, addr.korelisteOnsdag);
131     stmt.setString(7, addr.ruteTorsdag);
132     stmt.setString(8, addr.korelisteTorsdag);
133     stmt.setString(9, addr.ruteFredag);
134     stmt.setString(10, addr.korelisteFredag);
135     stmt.setString(11, addr.ruteLordag);
136     stmt.setString(12, addr.korelisteLordag);
137     stmt.setString(13, addr.ruteSondag);
138     stmt.setString(14, addr.korelisteSondag);
139    
140     stmt.setInt(15, addr.id);
141    
142    
143     stmt.execute();
144     if (stmt.getUpdateCount() != 1) {
145     System.out.println("Error executing update");
146     }
147    
148     }
149     }
150    
151     con.commit();
152     } catch (SQLException e) {
153     System.out.println("Error updating addresses" + e.getMessage() );
154     }
155     }
156    
157     private static String nullify(String str) {
158     if (str == null)
159     return null;
160    
161     if (str.equals("")) {
162     return null;
163     } else {
164     return str;
165     }
166     }
167    
168    
169     /*
170     private static int safeInt(String str) {
171     try {
172     return Integer.parseInt( str );
173     } catch (NumberFormatException e) {
174     return 0;
175     }
176     }
177    
178    
179    
180     private static String coalesce(String s1, String s2) {
181     if (s1 != null)
182     return s1;
183    
184     return s2;
185     }
186    
187     */
188     }
  ViewVC Help
Powered by ViewVC 1.1.20