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