adressevedligehold/db/DatabaseCoverageUpdate.java

package dk.daoas.adressevedligehold.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import dk.daoas.adressevedligehold.beans.Address;
import dk.daoas.adressevedligehold.util.DeduplicateHelper;
import dk.daoas.adressevedligehold.util.TimingHelper;

/*
 * TODO: Batched skrivning af updates
 * TODO: Skrivning af nye adresser 
 *  
 */

public class DatabaseLayerImplementation  {
        
        static boolean DEBUG = false;
        
        
        public List<Address> getAllAdresses() throws SQLException {
                TimingHelper timing = new TimingHelper();
                
                String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr >= 6000 " : "";
                
                String sql = 
                                "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,latitude,longitude, "
                                + "rutema,korelistema,ruteti,korelisteti,ruteon,korelisteon,ruteto,korelisteto,rutefr,korelistefr,rutelo,korelistelo,ruteso,korelisteso "
                                + "FROM fulddaekning.adressetabel "
                                + debugFilter
                                ;
                
                try ( 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<Address> list = new ArrayList<Address>(2600000);//initial capacity 2.6 mio
                        
                        DeduplicateHelper<String> vejnavnCache = new DeduplicateHelper<String>(81920);
                        DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>();
                        DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
                        DeduplicateHelper<String> dirigeringsCache = new DeduplicateHelper<String>(16*1024);                    
        
                        
                        while (res.next()) {
                                
                                Address a = new Address();
                                a.id = res.getInt(1);
                                a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
                                a.husnr = (short) res.getInt(3);
                                a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
                                a.kommunekode = (short) res.getInt(5);
                                a.vejkode = (short)res.getInt(6);
                                a.postnr = (short)res.getInt(7);
                                a.gadeid = res.getInt(8);
                                a.distributor = distributorCache.getInstance(res.getString(9));
                                a.dbkBane = (short) res.getInt(10);
                                a.latitude = (float) res.getDouble(11);
                                a.longitude = (float) res.getDouble(12);
                                
                                a.ruteMandag = dirigeringsCache.getInstance( res.getString(13) );
                                a.korelisteMandag = dirigeringsCache.getInstance( res.getString(14) );
                                
                                a.ruteTirsdag = dirigeringsCache.getInstance( res.getString(15) );
                                a.korelisteTirsdag = dirigeringsCache.getInstance( res.getString(16) );                         

                                a.ruteOnsdag = dirigeringsCache.getInstance( res.getString(17) );
                                a.korelisteOnsdag = dirigeringsCache.getInstance( res.getString(18) );
                                
                                a.ruteTorsdag = dirigeringsCache.getInstance( res.getString(19) );
                                a.korelisteTorsdag = dirigeringsCache.getInstance( res.getString(20) );
                                
                                a.ruteFredag = dirigeringsCache.getInstance( res.getString(21) );
                                a.korelisteFredag = dirigeringsCache.getInstance( res.getString(22) );
                                
                                a.ruteLordag = dirigeringsCache.getInstance( res.getString(23) );
                                a.korelisteLordag = dirigeringsCache.getInstance( res.getString(24) );
                                
                                a.ruteSondag = dirigeringsCache.getInstance( res.getString(25) );
                                a.korelisteSondag = dirigeringsCache.getInstance( res.getString(26) );


                                list.add(a);
                        }
                        res.close();
                        
                        
                        System.out.println("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms");
                        
                        return list;
                }
        }
        
        private void updateAddressesBatch(List<Address> addresses)  throws SQLException {
                
        }
        
        public void updateAddresses(List<Address> addresses) {
                System.out.println("DB::UpdateAddresses size:" + addresses.size() );
                
                String sql = "UPDATE fulddaekning.adressetabel " +
                                        "SET " +
                                        "RuteMa=?, KorelisteMa=?, " +
                                        "RuteTi=?, KorelisteTi=?, " +
                                        "RuteOn=?, KorelisteOn=?, " +
                                        "RuteTo=?, KorelisteTo=?, " +
                                        "RuteFr=?, KorelisteFr=?, " +
                                        "RuteLo=?, KorelisteLo=?, " +
                                        "RuteSo=?, KorelisteSo=?, " +
                                    "Distributor=? " +
                                        "WHERE id=?";
                                        
                                        
                try (Connection con = DBConnection.getConnection()) {
                        //con.setAutoCommit(false);
                        
                        try (PreparedStatement stmt = con.prepareStatement(sql)) {
                                for (Address addr : addresses) {
                                        stmt.setString(1, addr.ruteMandag);
                                        stmt.setString(2, addr.korelisteMandag);
                                        stmt.setString(3, addr.ruteTirsdag);
                                        stmt.setString(4, addr.korelisteTirsdag);
                                        stmt.setString(5, addr.ruteOnsdag);
                                        stmt.setString(6, addr.korelisteOnsdag);
                                        stmt.setString(7, addr.ruteTorsdag);
                                        stmt.setString(8, addr.korelisteTorsdag);
                                        stmt.setString(9, addr.ruteFredag);
                                        stmt.setString(10, addr.korelisteFredag);
                                        stmt.setString(11, addr.ruteLordag);
                                        stmt.setString(12, addr.korelisteLordag);
                                        stmt.setString(13, addr.ruteSondag);
                                        stmt.setString(14, addr.korelisteSondag);

                                        stmt.setString(15, addr.distributor);
                                        stmt.setInt(16, addr.id);

                                        
                                        stmt.execute();
                                        if (stmt.getUpdateCount() != 1) {
                                                System.out.println("Error executing update");
                                        }
                                        
                                }
                        }
                        
                        //con.commit();
                } catch (SQLException e) {
                        System.out.println("Error updating addresses" + e.getMessage() );
                }
        }
        
        private static String nullify(String str) {
                if (str == null)
                        return null;
                
                if (str.equals("")) {
                        return null;
                } else {
                        return str;
                }
        }


        /*
        private static int safeInt(String str) {
                try {
                        return Integer.parseInt( str );
                } catch (NumberFormatException e) {
                        return 0;
                }
        }
        

        private static String coalesce(String s1, String s2) {
                if (s1 != null)
                        return s1;
                
                return s2;
        }

        */
}
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	2845	static boolean DEBUG = false;
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	torben	2845	System.out.println("DB::UpdateAddresses size:" + addresses.size() );
107	torben	2838
108			String sql = "UPDATE fulddaekning.adressetabel " +
109			"SET " +
110			"RuteMa=?, KorelisteMa=?, " +
111			"RuteTi=?, KorelisteTi=?, " +
112			"RuteOn=?, KorelisteOn=?, " +
113			"RuteTo=?, KorelisteTo=?, " +
114			"RuteFr=?, KorelisteFr=?, " +
115			"RuteLo=?, KorelisteLo=?, " +
116	torben	2845	"RuteSo=?, KorelisteSo=?, " +
117			"Distributor=? " +
118	torben	2838	"WHERE id=?";
119
120
121
122			try (Connection con = DBConnection.getConnection()) {
123	torben	2845	//con.setAutoCommit(false);
124	torben	2838
125			try (PreparedStatement stmt = con.prepareStatement(sql)) {
126			for (Address addr : addresses) {
127			stmt.setString(1, addr.ruteMandag);
128			stmt.setString(2, addr.korelisteMandag);
129			stmt.setString(3, addr.ruteTirsdag);
130			stmt.setString(4, addr.korelisteTirsdag);
131			stmt.setString(5, addr.ruteOnsdag);
132			stmt.setString(6, addr.korelisteOnsdag);
133			stmt.setString(7, addr.ruteTorsdag);
134			stmt.setString(8, addr.korelisteTorsdag);
135			stmt.setString(9, addr.ruteFredag);
136			stmt.setString(10, addr.korelisteFredag);
137			stmt.setString(11, addr.ruteLordag);
138			stmt.setString(12, addr.korelisteLordag);
139			stmt.setString(13, addr.ruteSondag);
140			stmt.setString(14, addr.korelisteSondag);
141
142	torben	2845	stmt.setString(15, addr.distributor);
143			stmt.setInt(16, addr.id);
144	torben	2838
145
146			stmt.execute();
147			if (stmt.getUpdateCount() != 1) {
148			System.out.println("Error executing update");
149			}
150
151			}
152			}
153
154	torben	2845	//con.commit();
155	torben	2838	} catch (SQLException e) {
156			System.out.println("Error updating addresses" + e.getMessage() );
157			}
158			}
159
160			private static String nullify(String str) {
161			if (str == null)
162			return null;
163
164			if (str.equals("")) {
165			return null;
166			} else {
167			return str;
168			}
169			}
170
171
172			/*
173			private static int safeInt(String str) {
174			try {
175			return Integer.parseInt( str );
176			} catch (NumberFormatException e) {
177			return 0;
178			}
179			}
180
181
182
183			private static String coalesce(String s1, String s2) {
184			if (s1 != null)
185			return s1;
186
187			return s2;
188			}
189
190			*/
191			}